VBAコーディングパターン：DB Query結果Excelにインポートする

DB Query結果Excelへのインポートを実装するVBAコーディングパターンを紹介します。

関連記事： VBAコーディングパターン：ADO DB Connection Stringの生成

1. エクセル画面と動作方式

1.1。エクセル画面

「B1」セルにDBに接続するための接続文字列（Connection String）を入力し、「B2」セルにデータを抽出するためのSQL Queryを入力するように構成します。

「開発ツール > 挿入 > ActiveXコントロール > コマンドボタン」をクリックしてボタンを追加し、名前を「Query実行」に変更します。

例示画面は以下の通りである。

1.2. DB Query結果Excelにインポートする方法

上図のように動作します。

1. Excelに追加した「Queryを実行」ボタンをクリック
2. VBAコードからのDB接続
3. 接続したDBでQueryを実行する
4. DBで実行されたQuery結果を返す
5. Excelシートにクエリ結果を表示する

2. DB接続用のProvider（プロバイダ）の選択

上記の動作方法の「2。 VBAコードで「DB接続」で使用するプロバイダです。 ADO.ConnectionオブジェクトのProviderプロパティで指定するか、ConnectionStringで指定できます。

通常はConnectionStringで指定し、その例は次のとおりです。

• MS OLE DB Provider for ODBC Drivers
  • Provider = MSDASQL.1; Password = [パスワード]; Persist Security Info = True; User ID = [UserID]; Data Source = [ODBCコントロールパネルで設定したDSN]; Initial Catalog=[DB名]
• MS OLE DB for SQL Server (SQL Server 認証)
  • Provider = SQLOLEDB.1; Password = [パスワード]; Persist Security Info = True; User ID = [UserID]; Initial Catalog=[DB名];Data Source=[サーバーアドレス(IPまたはhost名)]
• MS OLE DB for SQL Server (Windows 認証)
  • Provider = SQLOLEDB.1; Password = [パスワード]; Integrated Security = SSPI; Persist Security Info = False; User ID = [UserID]; Initial Catalog=[DB名];Data Source=[サーバーアドレス(IPまたはhost名)]
• Oracle Provider for OLE DB
  • Provider=OraOLEDB.Oracle.1;Password=[パスワード]; Persist Security Info = True; User ID = [UserID]; Data Source = [サーバーアドレス（IPまたはホスト名）]

PCに適したProvider（プロバイダ）がインストールされていれば、Oracle、MS SQL Server、MySQL、MariaDB、PostgreSQLなどのRDBMSサーバーに接続でき、MS Access、SQLiteなどのlocal RDBMSにも接続できる。

ConnectionStringは、次のようにして生成できます。

ConnectionStringの生成方法 1. UDLファイルの利用

UDL（Universal Data Link）ファイルは、Microsoft Data Access Components（MDAC）に含まれる概念です。

注： ユニバーサルデータリンク（UDL）の構成 – OLE DB Driver for SQL Server | Microsoft Docs

次の手順で実行します。

1. デスクトップ上で右クリック>新規>テキスト文書
2. ファイル名をtest.udlに変更してEnter
3. test.udlファイルをダブルクリックして開き、「プロバイダ」タブでプロバイダを選択した後、「接続」タブでサーバー、ユーザー名、パスワード、データベースなどを入力し、「接続テスト」してから「確認」して保存
4. 保存したtest.udlファイルをエディタ（メモ帳など）で開いてみるとConnectionStringが得られる

この方法はWindows OSの32bit、64bitプラットフォームによってProviderが表示される。つまり、64bit OSでは64bit Providerのみが表示される。 64bit OSで32bit Providerを表示できる方法は別の投稿で投稿する。

ConnectionStringの生成方法2. VBAコード

前の記事 VBAコーディングパターン：ADO DB Connection Stringの生成 で詳細に説明しておいた。要約すると、次のようになります。

• Microsoft ActiveX Data Objects 2.8 Library リファレンスの追加
• Microsoft OLE DB Service Component 1.0 Type Libraryリファレンスの追加
• 次のコードでConnectionStringを生成する

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

Providerに関する注意事項

使用したいProviderとExcelの32bit、64bitプラットフォームが同じでなければなりません。 32bit Excelを使用している場合は32bit Providerを使用し、63bit Excelを使用している場合は64bit Providerを使用する必要があります。

64bit Windows OSと32bit Excelを使用している場合、最初の方法で作成したConnectionStringでDatabaseに接続しようとすると、「プロバイダが見つかりません」というエラーが発生することがあります。

この場合は、ExcelとProviderのプラットフォームが同じであることを確認し、適切なProviderを使用すると解決されます。

3. DB Query 結果 Excel へのインポートの実装

3.1。参照を追加

VBAコードエディタメニューで「ツール>参照を追加」を実行して、「Microsoft ActiveX Data Objects 2.8 Library」を参照して追加します。

3.2。 DB Query結果ExcelにインポートするVBAコード

エクセルシートに追加したボタンのクリックイベントプロシージャに次のようなコードを作成する。

Private Sub cmd_RunQuery_Click()
    Dim sSQL As String
    Dim oCon As ADODB.Connection, oRS As ADODB.Recordset
    Dim sConnectionString As String

    sConnectionString = Range("B1").Value2
    sSQL = Range("B2").Value2

    Set oCon = New ADODB.Connection
    Set oRS = New ADODB.Recordset
    oCon.ConnectionTimeout = 10
    oCon.Open sConnectionString
    oCon.CommandTimeout = 60

    oRS.Open sSQL, oCon, adOpenForwardOnly, adLockReadOnly
    
    Dim oSht As Worksheet, lColIdx As Long
    Set oSht = Worksheets.Add(After:=Me)
    For lColIdx = 0 To oRS.Fields.Count - 1
        oSht.Range("A1").Offset(0, lColIdx).Value2 = oRS.Fields(lColIdx).Name
    Next lColIdx
    oSht.Range("A2").CopyFromRecordset oRS

    oRS.Close: Set oRS = Nothing
    oCon.Close: Set oCon = Nothing
End Sub

上記のコードの説明は次のとおりです。

• 行6：Excelシートの「B1」セル値をsConnectionString変数にインポートします。
• 行7：Excelシートの「B2」セル値をsSQL変数にインポートします。
• 行12：ADODB.ConnectionオブジェクトoConにsConnectionString値を渡してデータベースに接続します。
• 行15：データベースでSQL Queryを実行し、その結果をADODB.RecordsetオブジェクトoRSにインポートします。
• 行18：SQL Queryの実行結果を表示するシートoShtを作成します。
• 行19-21：オブジェクトoRSのフィールド（列）リストをシートoShtの最初の行に出力します。
• 22行目：oShtシートの「A2」セルにoRSの内容（SQL Query実行結果）を出力する。

3.3。サンプルファイルのダウンロード

以下のgithub Linkで上記のコードを含むExcelファイルの例を見ることができます。
ファイルリンク： github DAToolset/VBACode/VBA コーディングパターン-DB Query1.xlsm