サクッとコピペ　ExcelVBA

サクッとコピペして、使えるVBAコード集

他のExcelブックの表データ（データベース）をSQLを使って取得する

はじめに

システムからデータをCSVなどで落として、それをExcel形式で保存しておくことって多いですよね。銀行の口座振替や収納代行会社を利用した収納において、多くの企業は、金融機関が提供しているデータベースから、入金情報をCSVなどの形式でダウンロードし、2次利用のためにそれをExcelデータとして保存しておくことが多いのではないでしょうか。また、逆に、社内基幹システムやSaaSなどのデータベースにインポートするため、インポートしやすい形式でExcelデータを作成していることもあります。

いずれにせよ、データベースとして扱いやすい表データが、日々大量に生産され、企業のファイルサーバーにExcel形式で大量に眠っているのではないでしょうか。そして、その表データを事務員たちは、受け渡し、参照し、コピペして加工するという絶望的な作業に日々追われています。

そんなExcelファイルを一つのデータベースとしてみなし、SQLを使って、特定ファイルの特定のデータをある程度まで加工して、データを取得するようなことをExcelVBAは可能にします。この場合、Excelファイルは、「ブック」がデータベースで、「シート」がテーブルとみなされます。処理のイメージは次の通りです。

SQL

もちろん、元となる表をそのまま加工し、レタスの行以外の行を削除していく方法も可能です。しかし、元なる表はそのまま残したいという需要もあるでしょうし、元の表から、複数のシートを別々に作成しなければならない場合もあります。その場合には、私はSQLとADO（ActiveX Data Objects）を使用する手法を用いて、コードを書いています。この方法だとSQLで取得したデータを加工しやすいです。

ADO（ActiveX Data Objects）　の事前バインディング

ADO（ActiveX Data Objects）は、名前の通り、オブジェクトなのですが、このオブジェクトを使用する際には、VBE上で、このオブジェクトの使用を登録しておく必要があります。登録の仕方は、次の通りです。

①[Alt＋F11]などで、VBE（Visual Basic Editor）を開く　⇒　②メニューの[ツール]から[参照設定(R)]を選ぶ　⇒　③Microsoft ActiveX Data Objects 2.X Library（Xは任意）または、Microsoft ActiveX Data Objects 6.1 Library　にチェックを入れ、OKで閉じる。

ADO事前バインディング

事前バインディング

実際のコーディング

それでは、実際のコードを見てみましょう。ディレクトリの指定については、自身で作成したものを入力してください。

Sub SQLデータ取得加工()

    '画面遷移をなくす
    Application.ScreenUpdating = False
    
    Dim cn As New ADODB.Connection 'データベース接続するためのオブジェクト
    Dim rs As New ADODB.Recordset  'レコードセットのオブジェクト
    Dim sql As String 'SQLコードの格納
    Dim dir As String 'ディレクトリの格納
    Dim i As Long
    
    'ディレクトリの取得（任意で）
    dir = "C:\Users\yamadataro\Desktop\八百屋データ.xlsx"
    
    'データベースファイルへの接続（ADO接続）
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0"
        .Open dir
    End With
    
    '「マスタ」用に「売上」シートからのSQL文作成
        sql = "SELECT"
        sql = sql & " *"
        sql = sql & " from " & "[売上$]"
    
    'SQLを用いて、データをレコードセットで取得する
    rs.Open sql, ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, LockType:=adLockReadOnly
    
    '「マスタ」シートにレコードを張り付ける処理
    Sheets("マスタ").Select
    '「マスタ」にあるデータを消す
    ActiveSheet.Cells.ClearContents
    '一行目の処理
    Cells(1, 1).Value = "商品コード"
    Cells(1, 2).Value = "商品名"
    Cells(1, 3).Value = "単価"
    Cells(1, 4).Value = "数量"
    Cells(1, 5).Value = "日付"
    '全部レコードを貼り付ける処理
    Range("A2").CopyFromRecordset Data:=rs
    'レコードセットのクリア
    rs.Close
    Set rs = Nothing
    
    
    '「レタス」用に「売上」シートからのSQL文作成
        sql = "SELECT"
        sql = sql & " *"
        sql = sql & " from " & "[売上$]"
        sql = sql & " where 商品名 = 'レタス' "
        
    'SQLを用いて、データをレコードセットで取得する
    rs.Open sql, ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, LockType:=adLockReadOnly
    
    '「マスタ」シートにレコードを張り付ける処理
    Sheets("レタス").Select
    '「マスタ」にあるデータを消す
    ActiveSheet.Cells.ClearContents
    '一行目の処理
    Cells(1, 1).Value = "商品コード"
    Cells(1, 2).Value = "商品名"
    Cells(1, 3).Value = "単価"
    Cells(1, 4).Value = "数量"
    Cells(1, 5).Value = "日付"
    Cells(1, 6).Value = "売上合計"
    
    i = 2  '2行目から処理
    
    Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
    
        Cells(i, 1).Value = rs!商品コード.Value
        Cells(i, 2).Value = rs!商品名.Value
        Cells(i, 3).Value = rs!単価.Value
        Cells(i, 4).Value = rs!数量.Value
        Cells(i, 5).Value = rs!日付.Value
        Cells(i, 6).Value = rs!単価.Value * rs!数量.Value
            
        rs.MoveNext '次のレコードに移動する
        i = i + 1
    Loop
    'レコードセットのクリア
    rs.Close
    Set rs = Nothing
    
    
    '「かぼちゃ」用に「売上」シートからのSQL文作成
        sql = "SELECT"
        sql = sql & " *"
        sql = sql & " from " & "[売上$]"
        sql = sql & " where 商品名 = 'かぼちゃ' "
        
    'SQLを用いて、データをレコードセットで取得する
    rs.Open sql, ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, LockType:=adLockReadOnly
    
    '「マスタ」シートにレコードを張り付ける処理
    Sheets("かぼちゃ").Select
    '「マスタ」にあるデータを消す
    ActiveSheet.Cells.ClearContents
    '一行目の処理
    Cells(1, 1).Value = "商品コード"
    Cells(1, 2).Value = "商品名"
    Cells(1, 3).Value = "単価"
    Cells(1, 4).Value = "数量"
    Cells(1, 5).Value = "日付"
    Cells(1, 6).Value = "売上合計"
    
    i = 2  '2行目から処理
    
    Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
    
        Cells(i, 1).Value = rs!商品コード.Value
        Cells(i, 2).Value = rs!商品名.Value
        Cells(i, 3).Value = rs!単価.Value
        Cells(i, 4).Value = rs!数量.Value
        Cells(i, 5).Value = rs!日付.Value
        Cells(i, 6).Value = rs!単価.Value * rs!数量.Value
            
            rs.MoveNext '次のレコードに移動する
            i = i + 1
    Loop
    
    'レコードセットのクリア
    rs.Close
    Set rs = Nothing
    'コネクションのクリア
    cn.Close
    Set cn = Nothing
    'マスタに画面を戻す。
    Sheets("マスタ").Select
    '画面遷移を戻す
    Application.ScreenUpdating = True

End Sub

データベースファイルへの接続（ADO接続）

■cn.Prvider　の部分（プロバイダの指定）について

適用 指定文字列
Office2003以前の場合 Microsoft.Jet.OLEDB.4.0
Office2007以降の場合 Microsoft.ACE.OLEDB.12.0
CSVの場合 同上

■cn.Properties　の部分（拡張プロパティの指定）について

適用 指定文字列
Office2003以前の場合 Excel 8.0
Office2007以降の場合 Excel 12.0
CSVの場合 Text

SQLの書き方の注意について

少しとっつきにくいところがありますが、躓きやすいSQLの書き方をまとめておきます。

■テーブル（シート）の書き方（from句 の後）

範囲 指定文字列
シート全体（A1からセルが埋まっている表） [シート名＄]
シートの範囲指定 [シート名$セル範囲（例:B2:F120）]
CSVの場合 [ファイル名.csv]

■文字列の書き方

類型 シングルクォーテーション(')囲みの要不要
列名 不要
where句の文字リテラル　 要
where句内の数字 不要

 

プライバシーポリシーはこちら