例えば
・SQL Server上の
・テーブル「m_product」のデータを取得
して、
シート上へ設定できます!
※列名も含む。
ここではVBAから
・「QueryTableオブジェクト(=クエリと接続)」
を使用して取得する方法を記載します!
VBAコード
ここでは例として、
・SQL Server上のDB「sampleDB」の
・テーブル「m_product」のデータを取得して
・新規作成したシート「data」へ設定
します。
Option Explicit
Sub sample()
'プロバイダ
Const PROVIDER As String = "MSOLEDBSQL"
'サーバー名
Const SERVER_NAME As String = "localhost\SQLEXPRESS"
'DB名
Const DB_NAME As String = "sampleDB"
'ユーザー名
Const USER_NAME As String = "XXXXX"
'パスワード
Const PASSWORD As String = "XXXXX"
Dim dataSheeName As String
Dim sql As String
Dim sheet As Worksheet
Dim dataSheet As Worksheet
Dim conStr As String
Dim n As Name
'SELECT文の結果を設定するシート名
dataSheeName = "data"
'実行するSELECT文
sql = "SELECT * FROM m_product"
'既にシート「date」が存在する場合は削除
For Each sheet In ThisWorkbook.Worksheets
If sheet.Name = dataSheeName Then
'確認メッセージを非表示
Application.DisplayAlerts = False
'シート削除
Worksheets(dataSheeName).Delete
'確認メッセージを表示
Application.DisplayAlerts = True
End If
Next
'シート「date」を新規作成
Set dataSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
dataSheet.Name = dataSheeName
'接続文字列の組み立て(Windows認証)
' conStr = "Provider=" & PROVIDER & ";" & _
' "Data Source=" & SERVER_NAME & ";" & _
' "Initial Catalog=" & DB_NAME & ";" & _
' "Integrated Security=SSPI;" & _
' "DataTypeCompatibility=80;"
'接続文字列の組み立て(SQL Server認証)
conStr = "Provider=" & PROVIDER & ";" & _
"Data Source=" & SERVER_NAME & ";" & _
"Initial Catalog=" & DB_NAME & ";" & _
"User ID=" & USER_NAME & ";" & _
"Password=" & PASSWORD & ";" & _
"DataTypeCompatibility=80;"
'「QueryTableオブジェクト(=クエリと接続)」を作成
With dataSheet.QueryTables.Add(Connection:="OLEDB;" & conStr, _
Destination:=Range("B2"), _
sql:=sql)
'SELECT文を実行
.Refresh BackgroundQuery:=False
'名前(後続で削除できるように名前を設定)
.Name = "仮テーブル"
'作成された「QueryTableオブジェクト(=クエリと接続)」を削除
.Delete
End With
'上記で作成されてしまう名前定義(仮テーブル)を削除
For Each n In ActiveWorkbook.Names
If n.Name = dataSheeName & "!" & "仮テーブル" Then
n.Delete '
End If
Next
End Sub
上記のVBAコードに記載の「接続文字列」の詳細は、以下のWebサイトの記事をご確認ください。
実行結果
SQL Serverのデータを取得してシートへ設定できました。
※列名も取得できました。
参考①
シート上の表(一連のセル範囲)をテーブル化することで、
・スタイルの設定
・テーブル化した範囲の名前定義
ができます。
詳細は以下の記事をご確認ください。
参考②
VBAからではなく、手動でも上記と同等のことを実施できます。
詳細は以下のWebサイトの記事をご確認ください。
参考③
列名を取得しない方法もあります。
詳細は以下の記事をご確認ください。
参考④
テーブルのレコードをCSVファイルへ出力することもできます。
詳細は以下の記事をご確認ください。
参考⑤
上記で使用した以下の詳細は、公式サイトをご確認ください。
●「QueryTables.Add」
●「QueryTableオブジェクト(=クエリと接続)」