外部ソースからデータを _tblExcel シートに取得するには、さまざまな方法があります。この記事では、VBA で SQL を使用して、MS Access データベース ファイルと Excel ファイルのデータをクエリする方法を説明します。はい、SQL と VBA を混ぜてみましょう!そしてそれはあなたが思っているよりも良いです！
SQL は Structured Query Language の略で、データベース (MS Access、MS SQL Server、Oracle、Sybase、SAP、MySQL など) から情報を取得するために使用される言語です。
インターネット、テキスト ファイル、または他の Excel または CSV ファイルからデータを取得することもできます。
したがって、レポート用のデータベースからのデータの取得を自動化するには、データベース接続 (以下のマクロの変数 varConn) と SQL クエリ (変数 varSQL) が必要です。以下の例は、MS Access の小規模データベースからデータを取得する SQL クエリです。
[MS Access データベースをダウンロード] をクリックします。 MS Access ファイルは次のようになります。
SQL クエリを実行するマクロを作成しましょう。
メニュー「サービス - マクロ - エディター」Visual Basic, insert 新しいモジュール (「挿入 - モジュール」メニュー) を開き、そこにマクロ テキストをコピーします。
Sub SQLQuery_1()
Dim varConn As String
Dim varSQL As String
Range("A1").CurrentRegion.ClearContents
varConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)}"
varSQL = "SELECT Month, Product, City FROM Sumproduct"
With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
.CommandText = varSQL
.Refresh BackgroundQuery:=False
End With
End Sub
[保存] をクリックして、Excel に戻ります。 [表示] - [マクロ] (Alt+F8) メニューでマクロの名前「SQLQuery_1」を選択します。ダイアログ ボックスが表示されるので、[データベース...] をクリックし、Access ファイルを探して、[OK] をクリックします。
これで、マクロ クエリが、test.mdb データベースの Sumproduct テーブルから Month、Product、および City 列を Excel ファイルに抽出したことがわかります。
同様に、データベースからのように通常の Excel ファイルからデータを取得できます。このためには、マクロ コードを少し書き直し、接続ドライバーを Access から Excel に変更する必要があります。
Sumproduct テーブルを Access から Excel にエクスポートし、ファイルを保存します。次に、変更したコードを新しいモジュールに挿入し、マクロを実行します。
Sub SQLQuery_2()
Dim varConn As String
Dim varSQL As String
Range("A1").CurrentRegion.ClearContents
varConn = "ODBC;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}"
varSQL = "SELECT Month, Product, City FROM Sumproduct"
With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
.CommandText = varSQL
.Refresh BackgroundQuery:=False
End With
End Sub
ウィンドウが表示され、Excel ファイル (Sumproduct.xlsx または別の拡張子を持つ名前にする必要があります) を選択できます。私たちはそれを見つけて選択します。 [OK] をクリックすると、Access ファイルからと同じデータが取得されます。