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

 

 

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 データベース作成・データ整理術: 無秩序な情報を自動で整理・転記する方法(テクニック)

 

 

 ●書式の説明
  1. ADO (ActiveX Data Objects) 概要:
    ADOは、データベースやデータソースへのアクセスを提供するためのMicrosoftのプログラミングインターフェースです。ADOを使って、データベースに接続し、データの取得や操作を行うことができます。Excel VBAでは、ADOを使ってExcelファイル内や外部データベースに対してSQLクエリを実行することができます。主要なオブジェクトは次の通りです:
  • Connectionオブジェクト:データベースへの接続を管理するオブジェクトです。
  • Recordsetオブジェクト:データベースから取得したデータを格納するオブジェクトです。
  • Commandオブジェクト:データベースに対して実行するコマンド(SQLクエリ)を格納するオブジェクトです。
  1. 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クエリの設定】部分を変更する事で、様々なデータの出力を行う事ができます。

【プログラムの流れ】
1.必要なオブジェクトを宣言し、接続情報を設定する。
2.データベースに接続する。
3.SQLクエリを設定し、実行する。
4.取得したフィールド名(表題)を1行目に表示する。
5.クエリ結果をresultDataワークシートに出力する。
6.レコードセットと接続を閉じる。
7.オブジェクトを解放する。

 

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

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

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

 

 

 

 

●実行前~実行後 ※プログラム実行後、クエリ内容に応じて別シートに出力されました。
(画面クリックして拡大)

 

 

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(サンプルプログラム)

 

 

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

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。