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にインポートする方法
上図のように動作します。
- Excelに追加した「Queryを実行」ボタンをクリック
- VBAコードからのDB接続
- 接続したDBでQueryを実行する
- DBで実行されたQuery結果を返す
- 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
次の手順で実行します。
- デスクトップ上で右クリック>新規>テキスト文書
- ファイル名をtest.udlに変更してEnter
- test.udlファイルをダブルクリックして開き、「プロバイダ」タブでプロバイダを選択した後、「接続」タブでサーバー、ユーザー名、パスワード、データベースなどを入力し、「接続テスト」してから「確認」して保存
- 保存したtest.udlファイルをエディタ(メモ帳など)で開いてみるとConnectionStringが得られる
![](https://prodskill.com/wp-content/uploads/2022/09/image-191.png)
![](https://prodskill.com/wp-content/uploads/2022/09/image-192.png)
この方法は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
投稿ありがとうございます〜そのままフォローするので、ディビではクエリがうまく実行され、値も他のシートに保存することもできます。ひとつ気になるのが、SQLクエリーがB2セルに入れられないほどクエリが非常に長文である場合には、どのようにクエリを呼び出すことができるでしょうか?返信をしてもらえるかもしれませんが、答えてくれて本当にありがとう!
訪問とコメントありがとうございます。
SQLクエリが非常に長い場合は、クエリの内容を別のsqlファイルとして保存し、VBAコードからそのファイルの内容を取得する方が良いでしょう。
近いうちにこれに関連する内容を別々の投稿で作成します。