【ExcelVBA】ExcelファイルをADOでデータベースのように扱う方法
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万行を超えるようなデータ数になる場合は避けた方がよいと思います。そのようなケースはやはりちゃんとしたデータベースサーバーを立ち上げるべきでしょう。
しかし、小規模のデータを扱う分には非常に優れていると思います。
スポンサーリンク