VBAで情報取得

質問

Book1のA列の文字をもとに、
Book2（開いていない）からデータを取得する方法は
どのような構文になりますか？

<Book1>
あああ
<Book2>
あああ　　11111　　東京
【やりたい事】
Book1に　11111、東京の情報を取得したい。
関数でいえばVLOOKUPです。
VBAでの方法をご存知の方ご教授お願い致します。

回答No.2

zap35 · ベストアンサー

VBAであっても閉じているブックを開いて検索し、検索が終わったらブックを閉じるのが原則です。決して「超簡単」ではないですよ。

DataBook.xlsのSheet1に以下のデータがあるとします
名称　コード　場所
a1　　　11　　　東京
a2　　　22　　　大阪
a3　　　33　　　京都
a4　　　44　　　名古屋

また今開いているブックのA1セルに検索したい値（仮にa4）がある場合、B1セルに「44」、C1セルに「名古屋」を表示させるマクロサンプルは以下です。

実際にはブックを開いていますが、画面描画を行わないことによって「見かけ上開かない」ように見えるはずです。

Sub Macro2()
Dim wb, aBk As Workbook
Dim aSh As Worksheet
Dim idx As Integer
Dim psw As Boolean
Dim r As Range
Const tBk = "DataBook.xls"
Const tSh = "Sheet1"
　　Application.ScreenUpdating = False
　　Set aSh = ActiveSheet
　　For idx = 1 To Workbooks.Count
　　　　If Workbooks(idx).Name = tBk Then
　　　　　　psw = True
　　　　　　Exit For
　　　　End If
　　Next idx
　　If psw = False Then
　　　　Set aBk = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & tBk)
　　　　Set r = Worksheets(tSh).Columns(1).Find(what:=aSh.Range("a1"), _
　　　　　　　　LookIn:=xlValues, Lookat:=xlWhole)
　　　　If Not r Is Nothing Then
　　　　　　aSh.Range("B1:C1").ClearContents
　　　　　　aSh.Range("B1").Value = r.Offset(0, 1).Value
　　　　　　aSh.Range("C1").Value = r.Offset(0, 2).Value
　　　　End If
　　End If
　　aBk.Close False
　　Application.ScreenUpdating = True
End Sub

もし「ブックを開かない」にこだわるならADOを用いて取得する方法もあります。そのサンプルが以下です。Microsoft ActiveX Data Objects 2.X Libraryを参照設定する必要があります

Sub Macro1()
Dim myCom As New ADODB.Connection
Dim myRst As New ADODB.Recordset
Dim myCyc As String
Dim mySql As String
Dim myFil As String
Dim idx As Long
　　myFil = "z:\DataBook.xls"
　　myCyc = "Driver={Microsoft Excel Driver (*.xls)};" & _
　　　　　　"DBQ=" & myFil & ";"
　　mySql = "SELECT * FROM [Sheet1$] WHERE 名称 = '" & Range("A1") & "'"
　　myCom.Open "Provider:=MSDASQL;" & myCyc
　　myRst.Open Source:=mySql, ActiveConnection:=myCom
　　Range("B1:C1").ClearContents
　　Range("z1").CopyFromRecordset myRst
　　Range("AA1:AB1").Copy Range("A2")
　　Range("z1").Resize(1, 100).ClearContents
　　myRst.Close
　　Set myRst = Nothing
　　Set myCom = Nothing
End Sub

どちらのサンプルも動作確認していますが、いずれにしても質問内容だけで書けるのはこの程度です。実際のシートにあわせて修正するのにもそれなりにマクロ知識が必要です。
そこまではおつきあいできませんが、がんばってください。

お礼

ありがとうございます。
やはり、難しいのですね。
あまりにも難しいようでしたら、アクセスでの方法もかんがえてみます。

回答No.1

uturogi_k

こんにちわ
他のEXCELより情報を取得する方法はさまざまありますが
DAOを使用して接続すれば比較的簡単に他EXCELファイルからの読み込みが可能かと思われます。
以下に参考になるURLを送付します。

http://homepage.mac.com/sunagoh/excelvba.html
該当部はこちら
http://homepage.mac.com/sunagoh/excel/db000201.html

この接続方式を使用するには参照設定で
Microsoft DAO 3.6 Object Libraryにチェックを入れる必要がありますのでご注意ください

ご参考になれば幸いです。

お礼

ありがとうございます。
参考にしてみます。