【ExcelVBA】ExcelファイルをADOでデータベースのように扱う方法

2023/11/08

ADOコネクションでExcelファイルをあたかもデータベースのように扱うことができます。AccessがインストールされていないPCではこの方法がおすすめです。（ただし多数の人が使う大規模データベースには向いていません。1人or数人で使う＆トータル1万行以下が目安です。）

データ用Excelファイルは別に保存されていて特に開くことなく読み書きできます。

今回はSELECT文を使った例を紹介します。動きについては以下の動画で参照ください。

データファイルはこのようになっています。これを「data.xlsx」というファイル名で保存して閉じておきます。シート名は「ITEMS」とします。

ソースコードはこちらです。

Sub readRecords()
   Set myCon = New ADODB.Connection
   With myCon
      .Provider = "Microsoft.ACE.OLEDB.12.0;"
      .Properties("Extended Properties") = "Excel 12.0"
      .Open "C:\Users\...\data.xlsx"
   End With
 
   sql_query = "SELECT * FROM [ITEMS$]"

   Set myRS = New ADODB.Recordset
   myRS.CursorLocation = adUseClient
   myRS.Open sql_query, myCon, adOpenForwardOnly, adLockOptimistic, adCmdText

   Range("A:E").ClearContents
   r = 1
   Do While Not myRS.EOF
      Cells(r, 1).Value = myRS.Fields("ID").Value
      Cells(r, 2).Value = myRS.Fields("商品名").Value
      Cells(r, 3).Value = myRS.Fields("単価").Value
      Cells(r, 4).Value = myRS.Fields("最小個数").Value
      myRS.MoveNext
      r = r + 1
   Loop

   Set myRS = Nothing
End Sub

上記を実行すると以下のように表示されます。

今度はWHERE分を入れて単価を500円超えのデータのみを抽出してくれます。SQL構文がそのまま使えます。

sql_query = "SELECT * FROM [ITEMS$] WHERE 単価>500"

上記の構文で実行するとご覧の通り500円を超える商品だけが列挙されます。

あくまで個人が使うレベル、複数人でも限られた少人数で使うことに限られます。これはAccessも同じですが。また1万行を超えるようなデータ数になる場合は避けた方がよいと思います。そのようなケースはやはりちゃんとしたデータベースサーバーを立ち上げるべきでしょう。

しかし、小規模のデータを扱う分には非常に優れていると思います。

- Excel, VBA