ExcelのVlookupでアクセスを参照したい

質問

質問があります。

掲題の通りですが、ExcelのVlookupでACCESSのクエリを参照してレコードを取得したいです。

今まで、ACCESSのクエリをExcelにエクスポートして、それをExcelでVlookupで持ってきていました。それを、Accessから直接Vlookupでもってきたいです。

ADO+VBAでやるだろうとは理解していますが、どうも難しくわからないです・・・。コードの記述を含めてやり方を教授して頂けると幸いです。
画像をアップロードしたのでよろしくお願いいたします。
画像のA1－D5がExcelで、下のテーブルはACCESSのものです。

ACCESSのテーブルをExcelのシートにコピペしてVlookupすれば良いのですが、後学のため、それと応用を利かすためにAccessのクエリを直接参照してデータを取得する方法を知りたいです。

よろしくお願いいたします。

それと、もし、その他オフィスの項目で聞くべきでしたら教えて頂ければ幸いです。

よろしくお願いいたします。

回答No.1

feaoid48938dd · ベストアンサー

ADO＋VBAで正解です

エクセルのファイルとアクセスのファイルが同じ場所にあるという前提で

'先ずは宣言を行います
Dim ws As Workspace
Dim db As DAO.Database
Dim rs, As DAO.Recordset
Dim strSQL As String

'保存場所の設定
dbNAME = "アクセスファイル名.mdb"
mydb = ActiveWorkbook.Path & "\" & dbNAME

'ワークスペースとデータベースの設定
Set ws = DBEngine.Workspaces(0)
Set db = OpenDatabase(mydb)

'エクセル上で実行するクエリ文の作成と実行
strSQL = "select * from テーブル名 Where 商品名 = " & "りんご" & ";"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'セルに出力
range("D2") = rs!出荷数

で実現出来るかと思います。

データベースはアクセス、作業はエクセルでと割り切ると良いかと思います。
実際のテーブルはもっと縦も横も多いと思いますので、SQL文を

strSQL = "select * from テーブル名 Where 商品名 = " & "りんご" & "and 品種 = " & "陸奥" & ";"

と変えれば追加できます。

上記 "りんご"　部分を　cells(2, 1) と参照する形でも対応できますし、
for next Do loop 文で cells(i, 1) と変数を入れれば動的な処理もできます。

それでは～

お礼

ありがとうございます。
繰り返し文がどうしても出来ずに詰まってしまいました・・・。

また質問させて頂きます。

ありがとうございました。

補足

ありがとうございます！

下記の文に改造して使いました。

Option Explicit
Sub test()
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set db = New ADODB.Connection
db.Provider = "Microsoft.ace.oledb.12.0"
db.Open "C:\Users\Kei\Desktop\Test\Test.accdb"

Set rs = New ADODB.Recordset
rs.Open "Ship", db, adOpenStatic

strSQL = "select * from Ship where 商品名 = " & "りんご" & ";"

Range("D2") = rs!出荷数

rs.Close
db.Close
Set db = Nothing
Set rs = Nothing

End Sub

ここに変数を使って繰り返し文を作れば全レコードを入力できますね。

ありがとうございました。