【VBA】データベースのデータをExcelにインポートする方法
「データベースに貯めたデータをExcelに落として加工したい!」
そう思ったことはありませんか?
データベースは非常に便利なものですが、使い慣れたExcel上でデータをこねくり回す方が楽な時ってないですか?
そんな時に活躍するのが、今回ご紹介するデータベースのデータをExcelにインポートするマクロです。
(毎度のことながらデータベースはMicrosoft Accessです。)
早速ですが、ソースコードはこんな感じです。
Public Sub Macro1() Dim DBpath As String 'Accessファイルのフルパス Dim adoCn As Object 'Accessへの接続用オブジェクト Dim adoRs As Object 'Accessからの取得用オブジェクト Dim strSQL As String 'SQL文 Dim myArray As Variant '全レコードを格納する配列 Dim tmpFldCnt As Variant 'フィールド数 Dim tmpRcdCnt As Variant 'レコード数 Dim strTable As String 'データ取得するテーブル名 Dim DBm As Variant Dim PutCell As String 'Accessへ接続する Set adoCn = CreateObject("ADODB.Connection") DBpath = "\\・・・・・・\〇〇.accdb" '接続するファイルのフルパス adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'オブジェクトの設定(取得用) Set adoRs = CreateObject("ADODB.Recordset") adoRs.CursorLocation = 3 'SQL文の実行 strTable = "データを抜き出すテーブル名" strSQL = "SELECT * FROM " & strTable adoRs.Open strSQL, adoCn 'SQLを実行して取得したデータをadoRsへ格納する tmpFldCnt = adoRs.Fields.Count 'フィールド数を取得 tmpRcdCnt = adoRs.RecordCount 'レコード数を取得 'レコード数がゼロじゃない場合 If tmpRcdCnt > 0 Then '全レコードを配列に格納 myArray = adoRs.GetRows 'レコードの配列を転置する ReDim DBm(1 To UBound(myArray, 2) + 1, 1 To UBound(myArray, 1) + 1) As Variant For i = 0 To UBound(myArray, 2) For j = 0 To UBound(myArray, 1) DBm(i + 1, j + 1) = myArray(j, i) Next Next Worksheets("貼り付け先のシート名").Activate Range("フォーマットする範囲").ClearContents 'セルへ入力 With ActiveSheet PutCell = "貼り付け先のセル(一番左上のセルを指定する)" .Range(.Range(PutCell), .Range(PutCell).Offset(UBound(DBm, 1) - 1, UBound(DBm, 2) - 1)) = DBm End With End If adoCn.Close 'Accessへの接続解除 Set adoRs = Nothing '取得用オブジェクトの解放 Set adoCn = Nothing '接続用オブジェクトの解放 ActiveWorkbook.Save End Sub
今回は、シンプルに指定したテーブルのデータを丸ごとExcelにインポートする仕様になっています。
カラム数(列数)を減らしたいときは、
SELECT * FROM ・・・・
のところで「*」を「インポートしたいカラム名」に書き換えてください(複数指定可)。
特定の条件を満たしたレコード(行)だけをインポートしたい場合は、
SELECT * FROM & strTable
の後ろに「Where句」を付け足してください。
Where句の使い方について簡単に紹介しておきます。
まず、こんな感じで記述します。
SELECT * FROM テーブル1 Where カラム1 = Test
これを実行すると、「テーブル1」のデータの内、「カラム1」に「Test」が入っているレコードのみが抽出されます。
こんなイメージです。
まずテーブル1がこんなテーブルだったとします。
上記のSQLを実行するとこうなります。
これを応用して、ほしいレコードだけを取り出していくわけですね。
Excel上でこねくり回すのであれば、SELECT * FROM Table_Nameですべてのデータをインポートするのが単純でよいと思います。
ルーティンワークとして頻繁にインポートする機会があるようであれば、SQLの文面を工夫してみるのも良いかもしれないですね。
データベースのデータを読み出す(書き込む)用のソフトを開発するのも良いですが、ちょっとVBAを書ければExcelでも代用可能です。
※今回はAccessでやりましたが、MySQLやPostgreSQL等のデータベースでも同様のことが出来ます。
↑オブジェクト定義の部分のコードを書き換える必要があります。