本記事では、ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する方法についてご説明します。
【動画】ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する実際の動き
本題に入る前に、まずはExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する実際の動きについて、次のツイートをご覧ください。
Excelのマクロで、SQL ServerのテーブルデータをCSVファイルに出力する処理を動画に撮ってみました😊
CSVに出力するデータはselect文で指定しています。抽出したいデータは条件句で指定したり、様々な組み合わせでお好みのデータを抽出することが可能です😃#VBA pic.twitter.com/fA3YXXnRtS
— まさ@Excel、VBA、マクロ(経験年数16年) (@masamasa9785) July 16, 2021
ツイート内の動画では、ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力しています。
ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する方法
ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力するには、次の流れの通りにコードを書いていきます。
Excelのマクロ
デリミタには、カンマやスペース、タブ文字などを指定します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのデータの参照・取得に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
テーブルのデータ参照(アクセス)は、RecordsetオブジェクトのOpenメソッドで行います。
※データが存在しない場合は処理を終了します。
ソースコード
Excelのマクロのコード(例)
Excelのマクロのコード(例)は次の通りです。
'変数定義 Dim DBName As String, connDB As String, CSVFilename As String, hdr As String, cnt As Integer Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset '定数定義 Const dlm As String = "," 'デリミタ(カンマ) 'CSVファイル名 CSVFilename = "data1.csv" 'データベース接続情報を取得 DBName = "testDataDB" 'DBの名前 connDB = "Provider=SQLNCLI11.1;" connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;" connDB = connDB & "Initial Catalog=" & DBName & ";" connDB = connDB & "Trusted_Connection=yes;" 'Connectionオブジェクトのインスタンスを生成する Set oCon = New ADODB.Connection 'SQL Serverに接続する oCon.Open connDB 'Recordsetオブジェクトのインスタンスを生成する Set oRS = New ADODB.Recordset '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、 'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定 oRS.ActiveConnection = oCon 'CSVに出力したいテーブルデータを抽出するSQL文(Sourceプロパティに設定) oRS.Source = "select * from dbo.tbl_score_list" '指定したテーブルのデータを参照する(アクセスする) oRS.Open 'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり) If oRS.EOF = True Then 'データが存在ない場合 Else 'データが存在する場合 For cnt = 0 To oRS.Fields.Count - 1 'CSVに出力するヘッダ行の取得(ここではテーブルのカラム名を使用。デリミタ区切りで編集) hdr = hdr & oRS.Fields.Item(cnt).Name & dlm Next '末尾のカンマを削除する hdr = Left(hdr, Len(hdr) - 1) Open ThisWorkbook.Path & "\" & CSVFilename For Output As #1 'CSVファイルに出力 Print #1, hdr & Chr(10) & oRS.GetString(adClipString, , dlm, vbNewLine) Close #1 End If '各終了処理 oRS.Close oCon.Close If Not oRS Is Nothing Then Set oRS = Nothing If Not oCon Is Nothing Then Set oCon = Nothing
コードの解説
注目すべきコード①
最初に見て頂きたいのは62行目です。
この62行目でSQL Serverから抽出したデータ(とヘッダ)をCSVファイルに出力しています。
CSVファイルに出力するのにPrint # ステートメントを使います。
また、SQL Serverのテーブルから抽出したデータを取得するには、レコードセットを文字列として返すGetStringメソッドを使います。
レコードセットを文字列として返すGetStringメソッドを使ってSQL Serverのテーブルから抽出したデータを、Print # ステートメントを使ってCSVファイルに書き出しています。
ちなみに、7行目で指定したデリミタは、GetStringメソッドの3番目の引数に指定しています。
注目すべきコード②
次に見て頂きたいのは59行目です。
この59行目でCSVファイルの出力先を指定しています。
注目すべきコード③
次に見て頂きたいのは35行目です。
SQL Serverのテーブルから抽出したデータは、この35行目で指定しているSQL文で決まります。
抽出したいデータに条件つける場合はこのSQL文に条件を付けて記述します。
次の38行目のOpenメソッドが実行されると、SQL文の条件を満たした抽出データがレコードセットに格納されます。
注目すべきコード④
次に見て頂きたいのは24行目です。
ConnectionオブジェクトのOpenメソッドを使ってSQL Serverに接続しています。
なお、SQL Serverへの接続情報の取得は、13行目から18行目で行っています。(変数connDBに格納)
取得したSQL Serverへの接続情報は、21行目で生成したConnectionオブジェクトのインスタンスのメンバであるOpenメソッドの引数に指定し、SQL Serverに接続しています。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの3行目の「ADODB.Connection」と4行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する方法についてご説明しました。
ザックリとした処理の流れとしては、
- ①CSVファイルに書き出したいテーブルデータを抽出するSQL文を記述
- ②SQL Serverに接続
- ③ ①のSQL文の条件を満たしたテーブルデータを抽出し、その抽出データをレコードセットから取得
- ④ ③のデータをCSVファイルに書き込んで保存
以上になります。
SQL Serverのテーブルデータを確認したい時や、テーブルデータのバックアップを取っておきたい時に便利ですね。
ちなみに、CSVファイルをSQL Serverにインポートしたい時は、こちらの記事を参考になるかと思います。

今回紹介したExcelのマクロのソースコードは長くなってしまいましたが、参考にしていただけたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。