VBAのADOでSQLを使えるようにする方法【爆速でデータ加工】

2021-06-06 2022-03-09

「VBAでもSQLが使えるようになればデータ加工は爆速ですよ？」

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

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

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

VBAエディタ内、上メニューの「ツール」から「参照設定」をひらく
「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。

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

データ加工したいCSVファイルを選択する
SQLを実行する
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変数の内容を変えるだけでいい。

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

データ加工したいCSVを選択する
SQLが実行される
指定したシートの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

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

SQLを実行する
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

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

SQLを実行する
指定したシートの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で爆速にデータ加工をしていこう。