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

2023年4月16日

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

●はじめに

今回説明するのは、ExcelでSQLを実行（ADO）データベース構築について説明を行いたいと思います。データベースは、情報を効率的かつ継続的に管理するために不可欠なツールです。大量のデータを網羅的に整理し、関連する情報を素早く検索・取得できるようにすることがデータベースの主要な目的です。EXCEL VBAでもデータベースを構築する事ができますので、構築方法含めて説明したいと思います。

【データベースの構築メリット】

項目	説明
一貫性と整合性	データベースは、一元化された情報の保管場所を提供することで、データの重複や矛盾を防ぎます。これにより、データの一貫性と整合性が維持され、信頼性の高い情報が利用できます。
データ分析	データベースに蓄積された情報を解析し、意思決定やビジネス戦略に役立てることができます。また、データベースを利用すれば、時系列データの分析やパターン認識も容易になります。
スケーラビリティ	データベースは、データ量やアクセス数が増加しても柔軟に対応できるよう設計されています。これにより、ビジネスの成長に伴ってデータ管理のニーズが変化しても、データベースを継続的に利用できます。

これらの利点から、データベースは情報管理やビジネス運営において重要な役割を果たします。

【社内でデータベースを構築例】

データベース	内容
顧客情報管理	データベースを利用することで、顧客情報（名前、住所、電話番号、購入履歴など）を一元的に管理できます。これにより、顧客へのサービス向上やマーケティング活動に役立てることができ、顧客満足度の向上や売上拡大につながります。
在庫管理	データベースを用いて、商品の在庫状況や入出庫情報をリアルタイムで把握することができます。これにより、適切な在庫量を維持し、品切れや過剰在庫による機会損失やコスト増を防ぐことができます。
人事管理	従業員の情報（氏名、住所、給与、勤務時間、評価など）をデータベースに格納し、管理することで、効率的な人事管理が可能になります。また、データベースを利用することで、労働時間や給与計算などの業務を効率化し、人事部門の負担を軽減できます。
請求・支払管理	データベースを使って、顧客からの請求情報や仕入先への支払情報を一元的に管理することができます。これにより、支払期限の把握や未収金の追跡が容易になり、キャッシュフローの最適化が図られます。
営業・マーケティング活動の分析	データベースに蓄積された営業成果やマーケティング活動のデータを分析することで、効果的な戦略やターゲット顧客の特定が可能になります。また、データベースを利用すれば、各活動のROI（投資対効果）を測定し、より効果的な営業・マーケティング活動に取り組むことができます

●　Microsoft Visual Basic での ADO の使用については、下記を参照して下さい（microsoft様）

https://learn.microsoft.com/ja-jp/sql/ado/microsoft-activex-data-objects-ado?view=sql-server-ver16

●【EXCEL VBAデータベース作成・データ整理術については、下記を参照して下さい】

EXCEL VBA データベース作成・データ整理術: 無秩序な情報を自動で整理・転記する方法（テクニック）

●書式の説明

ADO (ActiveX Data Objects) 概要:
ADOは、データベースやデータソースへのアクセスを提供するためのMicrosoftのプログラミングインターフェースです。ADOを使って、データベースに接続し、データの取得や操作を行うことができます。Excel VBAでは、ADOを使ってExcelファイル内や外部データベースに対してSQLクエリを実行することができます。主要なオブジェクトは次の通りです：

Connectionオブジェクト：データベースへの接続を管理するオブジェクトです。
Recordsetオブジェクト：データベースから取得したデータを格納するオブジェクトです。
Commandオブジェクト：データベースに対して実行するコマンド（SQLクエリ）を格納するオブジェクトです。

SQL（Structured Query Language）は、データベースからデータを取得、追加、更新、削除するためのプログラミング言語です。SQLクエリは、データベースに対して特定の操作を実行する命令文です。Excel VBAでは、ADOやクエリテーブルを使ってSQLクエリを実行し、Excelファイル内や外部データベースに対してデータ操作を行うことができます。主要なSQL文は次の通りです。

SELECT：データを取得するための文です。条件に一致するデータを取得できます。（今回はここの説明）
INSERT：新しいデータをデータベースに追加するための文です。
UPDATE：既存のデータを更新するための文です。
DELETE：データベースからデータを削除するための文です。

これらの概念を理解することで、Excel VBAを使ってデータベースやデータソースにアクセスし、データ操作を行うことができます。

【環境設定：準備作業】

まず、EXCEL VBAでデータベース構築やデータベース操作を可能にするために【Microsoft ActiveX Data Objects Libraryライブラリ】が利用できるように設定します。

①　Excelを開き、VBAエディタを開く。シート上で「Alt」+「F11」キーを押すか、Excelの「開発」タブからVBAエディタを開きます。
「ツール」メニューから「参照設定」を選択。

②　VBAエディタのメニューバーにある「ツール」をクリックし、ドロップダウンリストから「参照設定」を選択します。
「Microsoft ActiveX Data Objects」ライブラリを選択。

③　参照設定ダイアログボックスに表示されるライブラリのリストから、「Microsoft ActiveX Data Objects」ライブラリを探します。バージョンは複数ある可能性がありますが、通常は最新のバージョンを選択します（例：「Microsoft ActiveX Data Objects 6.1 Library」）。
チェックボックスをオンにして「OK」ボタンをクリック。

「Microsoft ActiveX Data Objects」ライブラリの横にあるチェックボックスをオンにし、下部の「OK」ボタンをクリックして設定を適用します。
これで、Excel VBAプロジェクトでADO（ActiveX Data Objects）ライブラリが利用できるようになります。これにより、データベースへの接続やデータ操作が可能になります。

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

●プログラム説明　(詳細説明)

下記のプログラムはExcel VBAを使って、Excel内でSQLを実行し、データを取得するものです。ADO (ActiveX Data Objects) を利用してデータベース操作を行い、指定されたソースデータからクエリ結果を取得し、別のワークシートに出力します。【SQLクエリの設定】部分を変更する事で、様々なデータの出力を行う事ができます。

【プログラムの流れ】
１．必要なオブジェクトを宣言し、接続情報を設定する。
２．データベースに接続する。
３．SQLクエリを設定し、実行する。
４．取得したフィールド名（表題）を1行目に表示する。
５．クエリ結果をresultDataワークシートに出力する。
６．レコードセットと接続を閉じる。
７．オブジェクトを解放する。

【プログラム実行条件・注意事項】

参照設定にMicrosoft ActiveX Data Objects Libraryを追加する必要があります。
ソースデータと結果データを格納するワークシート名を正確に指定してください。
接続文字列を適切に設定し、Excelファイルのパスが正確であることを確認してください。
SQLクエリの書式が正しいことを確認してください。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　EXCEL_SQL01（サンプルプログラム）

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
'
'

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）データベース（クエリを実行・学習用）

●プログラム説明　(サンプル②)

下記のサンプルプルログラムは、サンプル①応用になります。EXCEL VBA（ADO）を使って簡単にEXCEL上でクエリを実行する事ができますが、どのようにSQL文を入力すると処理結果が出力されるのかをこのサンプルプログラムを使って試すことができます。

【プログラムの流れ】

①　ワークシート「SouceData」、「resultData」、「SQL」をそれぞれの変数にセットします。
②　ワークシート「SouceData」のセルJ2に入力されたSQL文を取得します。
③　ワークシート「SQL」のA列からSQL文を検索します。
④　該当するSQL文が見つかった場合、対応するB列のセルからSQL文を取得します。
⑤　ワークシート「resultData」のA～F列をクリアし、実行する処理の内容およびSQL文を表示します。
⑥　Excelファイルをデータソースとして指定する接続文字列を作成し、データベースに接続します。
⑦　SQL文を実行し、結果をレコードセットに格納します。
⑧　フィールド名をシート「resultData」の1行目に表示し、結果を2行目から出力します。
⑨　レコードセットと接続を閉じ、オブジェクトを解放します。
⑩　ワークシート「resultData」をアクティブにし、列の幅を自動調整します。

【プログラム実行条件・注意事項】
①　プログラムを実行する前に、プロジェクトへの参照設定（Microsoft ActiveX Data Objects Library）が必要です。
②　ワークシート「SQL」に入力されたSQL文が正しい形式であることを確認してください。
③　データベースの接続文字列は、使用するExcelファイルやバージョンによって変更が必要な場合があります。
④　ソースデータやSQL文が大量の場合、処理に時間がかかることがあります。

★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
●　EXCEL_SQL02（サンプルプログラム）

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
'
'

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

●実行前～実行後　※実行後、処理結果については、選択されたSQL文によって異なります。ワークシート「resultData」に、SQL文を実行した結果が表示されます。フィールド名が1行目に表示され、2行目からは検索結果が出力されます。また、ワークシート「resultData」のI1セルに実行する処理の内容が、I2セルに実行するSQL文の内容が表示されます。
（画面クリックして拡大）