VBAのADOでSQLを使えるようにする方法【爆速でデータ加工】
「VBAでもSQLが使えるようになればデータ加工は爆速ですよ?」

CSVからSQLデータ加工をする方法を2つ、ExcelシートからSQLデータ加工する方法2つ。

合計4つの方法を紹介していく。

VBAでSQLを使えるようにする準備

  1. VBAエディタ内、上メニューの「ツール」から「参照設定」をひらく
  2. 「Microsoft ActiveX Data Objects 6.1 Library」にチェックを入れる

これで準備はOK。さっそくSQLを使えるようにしていこう。

CSVからSQLデータ加工し、2次元配列にする方法

Function CSVImportToArray(ByVal csv_full_path As String, _
    ByVal sql As String) As Variant

    If Dir(csv_full_path) = "" Then Exit Function

    Dim file_name As String
    Dim folder_path As String

    file_name = Dir(csv_full_path)
    folder_path = Replace(csv_full_path, file_name, "")

    Dim ado_connection As New ADODB.connection

    With ado_connection
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .Properties("Extended Properties") = "TEXT;HDR=YES;FMT=Delimited"
        .Open folder_path
    End With

    Dim ado_recordset As New ADODB.Recordset
    Set ado_recordset = ado_connection.Execute(sql)

    If ado_recordset.EOF = True Then
        CSVImportToArray = Empty
    Else
        CSVImportToArray = ado_recordset.GetRows
    End If

    ado_connection.Close

End Function

このコードはCSVをSQLでデータ加工してから2次元配列として出力するコードだ。

コピペして使ってくれ。

使うときは、つぎのコードを使って出力していこう。

Function CSVDataExtraction() As Variant

    Dim csv_full_path As String
    Dim sql As String
    Dim file_name As String

    csv_full_path = Application.GetOpenFilename("CSV(*.csv), *.csv", , "csv")
    file_name = Dir(csv_full_path)
    sql = "SELECT *" _
        & " FROM [" & file_name & "]"

    CSVDataExtraction = CSVImportToArray(csv_full_path, sql)

End Function

使いかたとしてはsql変数の内容を変えればOK。

コードの流れはつぎのような感じ。

  1. データ加工したいCSVファイルを選択する
  2. SQLを実行する
  3. 2次元配列として出力される

注意点として、ADOで出力した2次元配列はExcelでいう(行,列)の並びが(列,行)に変わるから2次元配列を使うときは注意しよう。

つぎはCSVをデータ加工して、Excelシートに出力する方法を紹介していく。

CSVからSQLデータ加工し、Excelシートに出力する方法

Sub CSVImportToSheet(ByVal csv_full_path As String, _
    ByVal sql As String, ByVal paste_start_range As Range)

    If Dir(csv_full_path) = "" Then Exit Sub

    Dim file_name As String
    Dim folder_path As String

    file_name = Dir(csv_full_path)
    folder_path = Replace(csv_full_path, file_name, "")

    Dim ado_connection As New ADODB.connection

    With ado_connection
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .Properties("Extended Properties") = "TEXT;HDR=YES;FMT=Delimited"
        .Open folder_path
    End With

    Dim ado_recordset As New ADODB.Recordset
    Set ado_recordset = ado_connection.Execute(sql)

    paste_start_range.CopyFromRecordset ado_recordset

    ado_connection.Close

End Sub

このコードは、CSVからExcelシートに出力するベースコードだ。

使うには、つぎのコードをコピペして使ってくれ。

Sub insertCSVData()

    Dim csv_full_path As String
    Dim sql As String
    Dim file_name As String

    csv_full_path = Application.GetOpenFilename("CSV(*.csv), *.csv", , "csv")
    file_name = Dir(csv_full_path)
    sql = "SELECT *" _
        & " FROM [" & file_name & "]"

    Call CSVImportToSheet(csv_full_path, sql, Sheet1.Range("A2"))

End Sub

これもsql変数の内容を変えるだけでいい。

コードの流れはつぎのような感じ。

  1. データ加工したいCSVを選択する
  2. SQLが実行される
  3. 指定したシートのRangeを基準にデータがまとめて出力される

ちなみにシート名は、オブジェクト名 + .Nameで設定しておくといい。オブジェクト名にしておくことで、シート名が変わってもコード修正しなくていいからだ。

つぎはExcelシートデータをデータ加工して、2次元配列にしていく方法を紹介していこう。

ExcelシートからSQLデータ加工し、2次元配列にする方法

Function sheetImportToArray(ByVal sql As String) As Variant

    Dim db_path As String
    db_path = ThisWorkbook.Path & "\" & ThisWorkbook.Name

    Dim ado_connection As New ADODB.connection

    With ado_connection
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .Properties("Extended Properties") = "Excel 12.0"
        .Open db_path
    End With

    Dim ado_recordset As New ADODB.Recordset
    Set ado_recordset = ado_connection.Execute(sql)

    If ado_recordset.EOF = True Then
        SheetImportToArray = Empty
    Else
        SheetImportToArray = ado_recordset.GetRows
    End If

    ado_connection.Close

End Function

上記コードがExcelシートをデータ加工して、2次元配列で出力するコードだ。

今回はコードが入ってるExcelを対象にしている。
他のExcelブックを参照したい?それならdb_path変数にExcelブックのフルパスを入れればOKだ。

使うときは、つぎのコードを使って2次元配列を出力してこう。

Function sheetDataExtraction() As Variant

    Dim sheet_name As String
    Dim sql As String

    sheet_name = Sheet1.Name
    sql = "SELECT *" _
        & " FROM [" & sheet_name & "$]"

    sheetDataExtraction = sheetImportToArray(sql)

End Function

コードの流れはつぎのような感じ。

  1. SQLを実行する
  2. 2次元配列として出力する

最後は、Excelシートをデータ加工。結果を別シートに出力する方法を紹介していこうか。

ExcelシートからSQLデータ加工し、別シートに出力する方法

Sub sheetImportToSheet(ByVal sql As String, _
    ByVal paste_start_range As Range) As Variant

    Dim db_path As String
    db_path = ThisWorkbook.Path & "\" & ThisWorkbook.Name

    Dim ado_connection As New ADODB.connection

    With ado_connection
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .Properties("Extended Properties") = "Excel 12.0"
        .Open db_path
    End With

    Dim ado_recordset As New ADODB.Recordset
    Set ado_recordset = ado_connection.Execute(sql)

    paste_start_range.CopyFromRecordset ado_recordset

    ado_connection.Close

End Sub

上記コードがExcelシートからデータ加工して、別シートに出力するコードだ。
ちなみに、このコードもdb_path変数を変えれば他のExcelブックでもSQL加工できるから試してみよう。

使うには、つぎのコードをコピペして使ってくれ。

Sub insertSheetData()

    Dim sheet_name As String
    Dim sql As String

    sheet_name = Sheet1.Name
    sql = "SELECT *" _
        & " FROM [" & sheet_name & "$]"

    Call sheetImportToSheet(sql, Sheet1.Range("A2"))

End Sub

コードの流れはつぎのような感じ。

  1. SQLを実行する
  2. 指定したシートのRangeを基準にデータをまとめて出力する

ちなみに、LEFT JOIN とかで別シート参照する場合は[]に$をつければOK。


Dim sql As String
sql = "SELECT *" _
    & " FROM [" & Sheet1.Name & "$] LEFT JOIN [People_Data$]" _
    & " ON [" & Sheet1.Name & "$].username = [People_Data$].name"

まとめ: VBAでも爆速でデータ加工してみよう

  • CSVからSQLデータ加工し、2次元配列にする方法
  • CSVからSQLデータ加工し、Excelシートに出力する方法
  • ExcelシートからSQLデータ加工し、2次元配列にする方法
  • ExcelシートからSQLデータ加工し、別シートに出力する方法

正直、VBAだけでデータ加工するのは限界がある。
だから、この4つを効率的に使ってSQLで爆速にデータ加工をしていこう。

事務作業を自動化してもっと"ラク"しませんか?

あなたの会社の事務作業...効率化してラクに。もっと重要な業務に集中できるようになります。

日々増えるルーティンの事務作業...
「この作業めんどくさい...」
「事務作業に時間をとられて他に集中できない...」

あなたの代わりにVBAを使ってパソコン業務を自動化。あなたはもっと価値のあることに時間を使うことができるため売上UP、ムダなコストの削減が期待できます。

また、作って終わりではなくつぎの3つのことを約束します。

  • しっかりドキュメント作成
  • きれいな見やすいコードでムダな工数カット
  • できるだけボタン1つポチッと押すだけで業務が完了

どんなにささいなめんどくさい事務作業でも相談してください。相談は無料です。
3年の業務効率化実績があるため、力になれるかもしれません。

※件名には「業務効率化の相談」と記載ください。できるだけ24時間以内に返信します。