EXCEL VBA エクセルでSQLを実行（ADO）データベース構築・クエリー実行・大量のデータを処理・入門者

EXCEL VBA エクセルでSQLを実行（ADO）データベース構築・クエリー実行・大量のデータを処理

EXCEL VBA エクセルでSQLを実行（ADO）データベース構築 ・環境設定・詳細説明

Option Explicit
'
'
Sub SampleADO() 'EXCELデータベース
    ' 参照設定が必要です (Microsoft ActiveX Data Objects Library)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim sourceWs As Worksheet, resultWs As Worksheet
    Dim field As ADODB.field
    Dim i As Integer

    ' ワークシートの指定
    Set sourceWs = ThisWorkbook.Worksheets("SouceData")  'ソースデータが登録
    Set resultWs = ThisWorkbook.Worksheets("resultData")  'クエリー結果が登録

    ' オブジェクトの宣言
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' データソースとしてExcelファイルを指定する接続文字列
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    ' データベースに接続
    cn.Open

    ' SQLクエリの設定
    strSQL = "SELECT * FROM [" & sourceWs.Name & "$];"

    ' SQLクエリを実行し、結果をレコードセットに格納
    rs.Open strSQL, cn

    ' 表題（フィールド名）を1行目に表示
    i = 1
    For Each field In rs.Fields
        resultWs.Cells(1, i).Value = field.Name
        i = i + 1
    Next field

    ' 結果を「resultData」ワークシートに出力（2行目から）
    resultWs.Range("A2").CopyFromRecordset rs

    ' レコードセットと接続を閉じる
    rs.Close
    cn.Close

    ' オブジェクトの解放
    Set rs = Nothing
    Set cn = Nothing
End Sub
'
'

EXCEL VBA エクセルでSQLを実行（ADO）データベース（クエリを実行・学習用）

Option Explicit
'
'
Sub SampleADO01()   'EXCELデータベース
    ' 参照設定が必要です (Microsoft ActiveX Data Objects Library)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL, searchString As String
    Dim sourceWs, sqlWs, resultWs As Worksheet
    Dim field As ADODB.field
    Dim i As Integer
    Dim searchRange, foundCell As Range

    ' ワークシートの指定
    Set sourceWs = ThisWorkbook.Worksheets("SouceData")  'ソースデータが登録
    Set resultWs = ThisWorkbook.Worksheets("resultData")  'クエリー結果が登録
    Set sqlWs = ThisWorkbook.Worksheets("SQL")  'SQL文が登録されている。
    
    '選択してSQL文（文字列）を取得
    searchString = sourceWs.Cells(2, "J").Value


    ' SQL文の検索範囲を設定
    Set searchRange = sqlWs.Columns("A")
    
    ' SQL文の文字列を検索
    Set foundCell = searchRange.Find(What:=searchString, LookIn:=xlValues, LookAt:=xlWhole)
    
    ' 検索して一致するSQL文（クエリ）の設定
    If Not foundCell Is Nothing Then
            strSQL = sqlWs.Cells(foundCell.Row, "B")
        Else
            MsgBox searchString & " はワークシート「SQL文」に存在しません。"
            Exit Sub
    End If

    'SQLの実行結果を表示するワークシートのA列～F列をクリアーします。
    With resultWs
        .Range("A:F").ClearContents
    '実行する処理の内容を結果シートに表示
        .Range("I1") = searchString
    '実行するSQL文の内容を結果シートに表示
        .Range("I2") = strSQL
    End With
    
    ' オブジェクトの宣言
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' データソースとしてExcelファイルを指定する接続文字列
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    ' データベースに接続
    cn.Open

    ' SQLクエリを実行し、結果をレコードセットに格納
    rs.Open strSQL, cn

    ' 表題（フィールド名）を1行目に表示
    i = 1
    For Each field In rs.Fields
        resultWs.Cells(1, i).Value = field.Name
        i = i + 1
    Next field

    ' 結果を「resultData」ワークシートに出力（2行目から）
    resultWs.Range("A2").CopyFromRecordset rs

    ' レコードセットと接続を閉じる
    rs.Close
    cn.Close

    ' オブジェクトの解放
    Set rs = Nothing
    Set cn = Nothing
    
    'SQL結果のワークシートを表示
    With resultWs
        .Activate
        .Cells.Columns.AutoFit
    End With
    
End Sub
'
'
Sub SampleADO02() 'EXCELデータベース'

    Dim sourceWs As Worksheet
    Set sourceWs = ThisWorkbook.Worksheets("SouceData")  'ソースデータが登録
    sourceWs.Activate 'ワークシート「SouceData」をアクティブにする（戻る）

End Sub
'
'