【ExcelVBA】ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力するには？

5分

SHARE

本記事では、ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する方法についてご説明します。

【動画】ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する実際の動き

本題に入る前に、まずはExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する実際の動きについて、次のツイートをご覧ください。

ツイート内の動画では、ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力しています。

目次へ戻る

ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する方法

ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力するには、次の流れの通りにコードを書いていきます。

Excelのマクロ

STEP.1
出力するCSVファイルのデリミタ指定
出力するCSVファイルのデリミタを指定します。
デリミタには、カンマやスペース、タブ文字などを指定します。
STEP.2
出力するCSVファイルの指定
出力するCSVファイルを指定します。
STEP.3
SQL Serverへの接続情報取得
SQL Serverに接続するための接続情報を取得します。
STEP.4
Connectionオブジェクトのインスタンスの生成
Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
STEP.5
SQL Serverに接続
接続情報が取得出来たら、その接続情報をもとにSQL Serverに接続します。
STEP.6
Recordsetオブジェクトのインスタンスの生成
Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverのデータの参照・取得に必要です。
STEP.7
Recordsetオブジェクトに接続先情報を設定
RecordsetオブジェクトのActiveConnectionプロパティに、STEP.1で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
STEP.8
データを抽出するSQL文の設定
データを抽出するSQL文をRecordsetオブジェクトのSourceプロパティに設定します。
STEP.9
指定したテーブルのデータを抽出
STEP.6のSQL文で指定したテーブルのデータを抽出します。
テーブルのデータ参照(アクセス)は、RecordsetオブジェクトのOpenメソッドで行います。
STEP.10
抽出したデータのヘッダの生成
データが存在する場合は、抽出したデータのヘッダ部を生成する。(ヘッダが必要な場合)
※データが存在しない場合は処理を終了します。
STEP.11
ヘッダと抽出したデータをCSVファイルに書き込む
STEP.10で生成したヘッダと、STEP.9で抽出したデータをCSVファイルに書き込みます。
目次へ戻る

ソースコード

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」ボタンをクリックします。

  1. 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にインポートしたい時は、こちらの記事を参考になるかと思います。

【ExcelVBA】ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートするには？

今回紹介したExcelのマクロのソースコードは長くなってしまいましたが、参考にしていただけたら幸いです。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】ExcelのマクロからSQL Server…

次の記事

【ExcelVBA】CSVファイルのデータの中から条件に合致す…