EXCELでEXCELファイルおよびACCESSのデータに対してSQLを実行する方法

ADODBのSQLの特殊な構文を使ってエクセルからEXCELやACCESSのデータにアクセスします

サンプルコード

Sheet1のA1セルに以下のデータを貼り付けて下さい

名前	科目	点数
鈴木	英語	95
鈴木	数学	59
佐藤	英語	71
佐藤	数学	97

Sheet2のA1セルに以下のSQLを入力してください

Sheet2!A1

SELECT 名前
     , SUM(点数) AS 合計点
  FROM [Sheet1$A1:C5]
 GROUP BY 名前

VBAの標準モジュールに以下のコードを貼り付けて下さい

Module1

Option Explicit

Private Const C_CONNECTSTR = "Provider={Provider};Data Source={FileName};Extended Properties={Properties}"
Private Const C_PROVIDER = "Microsoft.ACE.OLEDB.12.0"
'Private Const C_PROVIDER = "Microsoft.Jet.OLEDB.4.0"
Private Const C_PROPERTIES = "Excel 12.0"
'Private Const C_PROPERTIES = "Excel 8.0"
Private Const adOpenStatic = 3
Private Const adLockReadOnly = 1

'*****************************************************************************
'サンプルコード
'*****************************************************************************
Public Sub Test()
    Call MsgBox("一度も保存されていないファイルはエラーとなるのでご注意ください")

    Dim strSQL As String
    Dim objOutRange As Range
    strSQL = Range("Sheet2!A1")          'SQLの入力されたセル
    Set objOutRange = Range("Sheet2!B1") 'SQLの実行結果を表示する左上のセル

    Dim strCon As String
    strCon = C_CONNECTSTR
    strCon = Replace(strCon, "{Provider}", C_PROVIDER)
    strCon = Replace(strCon, "{FileName}", ActiveWorkbook.Name)
    strCon = Replace(strCon, "{Properties}", C_PROPERTIES)

    'SQLの実行結果のレコードセットを取得
    Dim objRecordset As Object
    Set objRecordset = CreateObject("ADODB.Recordset")
    Call objRecordset.Open(strSQL, strCon, adOpenStatic, adLockReadOnly)

    '結果を表示
    Dim i As Long
    For i = 1 To objRecordset.Fields.Count
        '見出し設定
        objOutRange.Cells(1, i) = objRecordset.Fields(i - 1).Name
    Next
    '明細設定
    Call objOutRange.Cells(2, 1).CopyFromRecordset(objRecordset)
End Sub

ファイルを必ず保存してから、マクロのTest()を実行してください


Sheet2のB1セルに以下のデータが表示されれば成功です

名前	合計点
鈴木	168
佐藤	154

---

SQLの構文について

作業中のEXCELブックのデータの指定方法

例１

SELECT *
FROM [Sheet1$]
--セル範囲を指定しない時は、データが入力された範囲がテーブルの領域として認識されます
--[]と$は必須  

例２

SELECT *
FROM [Sheet1$A1:C5]
--セル範囲を指定した時は、そのセル範囲がテーブルの領域として認識されます

例３

UPDATE [Sheet1$A:C]  --列のみを指定することも可能です
SET 列1 = 値  
-- UPDATE文でセルの値を更新することも可能です

外部ファイルのデータの指定方法

[外部データ接続子]を記述することで外部ファイルのデータに対してSQLを実行することが可能です

SELECT *
FROM [外部データ接続子].[テーブル名]

[外部データ接続子]のバリエーション

1. EXCELファイルの指定方法

[EXCEL 12.0;DATABASE=ファイルパス\ファイル名]
[EXCEL 8.0;DATABASE=ファイルパス\ファイル名]

例１

SELECT *
FROM [EXCEL 12.0;DATABASE=c:\tmp\sample.xlsx].[Sheet1$]

例２

SELECT *
FROM [EXCEL 12.0;DATABASE=c:\tmp\sample.xlsb].[Sheet1$]
--大容量のデータを扱う場合、xlsb(バイナリ)ファイルで保存した方がxlsxファイルで
--保存するより最大で5分の1ほどファイルサイズが小さくなります

2. Accessファイルの指定方法

[MS ACCESS;DATABASE=ファイルパス\ファイル名]
[MS ACCESS;DATABASE=ファイルパス\ファイル名;PWD=パスワード] パスワードの指定も可能です

例１

SELECT *
FROM [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[テーブル名]

例２

SELECT *
FROM [MS ACCESS;DATABASE=c:\tmp\sample.accdb].クエリ名  --[]は省略可
--クエリも可

INSERT UPDATE DELETE 等で値を更新することも可能です

例３

--以下のようにEXCELのデータを既存のテーブルに追加することも可能です
INSERT INTO [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[テーブル名]
SELECT *
FROM [Sheet1$]

CREATE TABLE DROP TABLE 等のDDL文の実行も可能です

例４

--以下のようにEXCELのデータを新たなテーブルにインポートすることも可能です
SELECT *
INTO [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[新テーブル名]
FROM [Sheet1$]

例５

--以下の様にクエリを作成することも可能です
CREATE VIEW [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[新クエリ名] AS
select_statement

3. CSV(TXT)ファイルの指定方法

[TEXT;DATABASE=ファイルパス]

例

SELECT *
FROM [TEXT;DATABASE=c:\tmp].[sample.txt]
--[テーブル名]にはファイル名を指定します

4. ODBC接続

筆者の環境では、試せていないので接続できるかどうかは不明です。

以下のサイトを参照して紹介しています。
外部参照するデータベースの識別
実際に試された方がおられましたら、ぜひコメントお願いします。
またSQLの方言は該当のデータベースのそれではなくAccessとなります。

オラクル
[ODBC;Driver={Microsoft ODBC for Oracle};SERVER=サーバ名;UID=ユーザID;PWD=パスワード]
SQLServer
[ODBC;Driver={SQL Server};SERVER=サーバ名;Database=ＤＢ名;UID=ユーザID;PWD=パスワード]
など

2020/05/23追記
MySQLにて以下の識別子で接続できることを確認しました。
[ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=ＤＢ名;UID=ユーザID;PWD=パスワード]

5. 参考

当記事は下記サイトのIN句の別の記述方法です。
IN 句 (Microsoft Access SQL)
ADOでの異なる種類のファイルへのSQL
外部参照するデータベースの識別

---

SQLの方言

SQLの書式は、AccessのSQLに準拠します。
以下に主な特徴を紹介します。

1.文字列連結

文字列の連結には & を使用します。
リテラルの文字列は　「"」ダブルコーテーション　または　「'」シングルコーテーション　で囲います。

例

SELECT 列1  &  列2  &  "123"  &  'ABC' AS 連結例
FROM [Sheet1$]

2.条件判定

条件の判定には IIF()関数 または SWITCH()関数 あるいは CHOOSE()関数を使用します。

IIF(条件, THENの値, ELSEの値)
あるいは
IIF(条件, THENの値)

SWITCH(条件1, 値1, 条件2, 値2, ... , TRUE, その他の値)
--最後の条件をTRUEとすることで、条件を満たさなかった時の値を指定することができます
--EXCEL2016以降のIFS()関数と同様

CHOOSE(項目, 値1, 値2, ... )
--EXCELのCHOOSE()関数と同様
--項目=1の時は値1、項目=2の時は値2 ...

IIF()関数でELSE部分を省略した場合はNULLとなります。
よって以下の2行は同じ結果になります。
IIF(条件, THENの値)
IIF(条件, THENの値, NULL)
SWITCH()関数・CHOOSE()関数についても、いずれの条件も満たさない場合はNULLとなります。

余談
集計関数にIIF()関数を使用するテクニックをご紹介します
例1：点数が0点を除いた最小点を取得する場合
MIN(IIF(点数>0,点数))　0点の場合はNULLとなるため集計関数からは無視される
例2：点数が100点のレコードをカウントする
COUNT(IIF(点数=100,点数))　100点以外の場合はNULLとなるため集計関数からは無視される
または　SUM(IIF(点数=100,1,0))
さらにどうでもいい余談、上記のCOUNTを使用する場合とSUMを使用する場合で一見結果は同じ様ですが微妙な違いがあります。
データベースから返ってくるRecordsetのデータ型がCOUNTは正数型 SUMは実数型となります。
例えばSELECT INTOでAccessに新規テーブルを登録した場合、COUNTを使用したカラムは整数型、SUMを使用したカラムは実数型となります。

3.NULLの判定

項目 IS NULL　または　ISNULL(項目)
NULL以外の判定
項目 IS NOT NULL　または　NOT ISNULL(項目)

例

SELECT IIF(ISNULL(列1),     '-', 列1) AS 列1
     , IIF(NOT ISNULL(列2), 列2, '-') AS 列2
     , IIF(列3 IS NULL,     "-", 列3) AS 列3
     , IIF(列4 IS NOT NULL, 列4, "-") AS 列4
FROM [Sheet1$]
WHERE ISNULL(列5)
   OR NOT ISNULL(列6)
   OR 列7 IS NULL
   OR 列8 IS NOT NULL

4.数値項目へのキャスト(型変換)

以下の例の様に1を乗じることで容易に数値として認識させることができます。

例1

SELECT *
FROM [Sheet1$]
WHERE 項目*1 IN(1,2,3)
--EXCELのデータに対してSQLを実行した場合、数値と思っていても文字列と判定されてしまう場合が良くあります
--そのような場合にこの手法が有効です

例2

SELECT *
FROM [Sheet1$]
WHERE RIGHT(年月日, 2)*1 IN(5,15,25)
--YYYYMMDDの書式で設定されている日付項目の日の部分を数値で評価しています

5.予約語や数字から始まる名称など、カラム名として不適切な項目の対処法

項目名を[]で囲みます。

例

SELECT [KEY], [1番目], [氏　名]
FROM [Sheet1$]

6.外部結合

例

SELECT Z.*, A.*
FROM [Sheet1$] AS Z
LEFT JOIN
     [Sheet2$] AS A
 ON Z.コード1 = A.コード1
AND Z.コード2 = A.コード2 
-- ZとAの部分はテーブルに別名を設定したい場合に記述します。名称は自由に選択できます
-- ZのテーブルのレコードはAのテーブルの内容にかかわらず全レコード出力されます

AccessのSQLにFULL JOINはありません。

7.内部結合

例1

SELECT Z.*, A.*
 FROM [Sheet1$] AS Z
INNER JOIN
      [Sheet2$] AS A
 ON Z.コード1 = A.コード1
AND Z.コード2 = A.コード2 
-- ZとAの部分はテーブルに別名を設定したい場合に記述します。名称は自由に選択できます
-- ZのテーブルとAのテーブルの両方に存在するレコードのみ出力されます

以下のSQLも同様の結果になります。

例2

SELECT Z.*, A.*
FROM [Sheet1$] AS Z
   , [Sheet2$] AS A
WHERE Z.コード1 = A.コード1
  AND Z.コード2 = A.コード2 

8.複数カラムのIN演算子

複数カラムに対するIN演算子の判定はできません

だめな例

SELECT *
FROM [Sheet1$]
WHERE (列1, 列2) IN(SELECT 列1, 列2
                     FROM [Sheet2$])
--上記はエラーとなります
--ORACLEなどでは有効な記述方法です

代わりにEXISTSを使います

例1

SELECT *
FROM [Sheet1$] AS Z
WHERE EXISTS(SELECT *
               FROM [Sheet2$] AS A
              WHERE Z.列1 = A.列1
                AND Z.列2 = A.列2)

または複数カラムを連結して１つのカラムにする

例2

SELECT *
FROM [Sheet1$]
WHERE 列1 & 区切り文字 & 列2 IN(SELECT 列1 & 区切り文字 & 列2
                               FROM [Sheet2$])
--区切り文字はなくても良いと思いますが
--列1と列2の桁数が可変であれば、なんらかの区切り文字があった方が確実です

あるいはJOINを使用する

例3

SELECT Z.*
FROM [Sheet1$] AS Z
LEFT JOIN
  (SELECT DISTINCT  --注1
          列1,列2 
     FROM [Sheet2$]) AS A
  ON Z.列1 = A.列1
 AND Z.列2 = A.列2
WHERE NOT ISNULL(A.列1)
または
WHERE ISNULL(A.列1)  -- NOT INのケース
--注1 DISTINCTはサブクエリのレコードに重複がある場合は必須です
--なければ元のZテーブルのレコード数より多くのレコードが結果に出力されます

筆者の体験によれば、JOINを使用する方法が、他の方法に比べてダントツに高速です。
もし、INまたはEXISTSを使用したSQLの実行に非常に時間がかかるようであれば、JOINの使用をおすすめします。

9.コメント

コメントは記述できません。
一般的なSQLでは、-- は行末までコメント　/* */ に囲まれている文字はコメントとなりますが、AccessのSQLでは使用できません。
ただし、以下で紹介しているツールではコメントの記述ができるように対応しています。

10.サブクエリの記述

AccessのSQLではWITH句は使えません。
ただし、以下で紹介しているツールでは、セル参照という方法を使って、サブクエリ部分を切り出して記述することが可能です。

---

ツールの紹介

この記事で紹介した手法を用いて、EXCELでかんたんにSQLを実行できるツールを作成しました。
以下の記事で紹介していますので、ぜひ一読ください。
EXCELでEXCELファイルおよびACCESSのデータに対してかんたんにSQLを実行するツール