同じフォルダー内の別ブックで計算結果を入力

質問

Aブックsheet1をActiveしており同じフォルダー内にあるBブックのsheet1の検索値"A2"のVLooKUPで出した計算結果13の値のみをAブックsheet1　セル"A2"に入力したいのですが。Excel関数は解るのですがVBA関数となると難しくどなたか解るかた宜しくお願いします。因みにBブックは閉じています。

回答No.1

watabe007 · ベストアンサー

どうぞ
Sub Test()
　　Dim myPath As String, a As Long
　　Application.ScreenUpdating = False
　　myPath = ThisWorkbook.Path
　　With Workbooks.Open(myPath & "\B.xls")
　　　　With Worksheets("Sheet1")
　　　　　　a = Application.VLookup(.Range("A2").Value, .Range("B2:D6"), 3)
　　　　End With
　　　　.Close False
　　End With
　　ThisWorkbook.ActiveSheet.Range("A2").Value = a
　　Application.ScreenUpdating = True
End Sub

お礼

有難うございます助かりました。構文がある程度理解できました。今後応用していきたいと思います。

回答No.2

real beatin（@realbeatin）

こんにちは。
以前、類似のご質問に回答した者です。
　『VBAで別ブックからVLOOKUPで抽出』
　https://okwave.jp/qa/q8984933.html

> Excel関数は解るのですが
ということでしたら、以前のご質問同様、そのままの応用で
VBAで、ブックを開いて、数式をセルに書き込んで計算させる方法が簡単
な気がしますが、さておき。
敢えてVBAで実現させたいという理由には、色々あるのでしょうけれど、
> 因みにBブックは閉じています。
Bブックを閉じたまま処理したい、という意図も含まれているように
私には見えましたので、VBAからADODB経由でクエリを実行する例を。

外部ブックからの抽出作業で何度も悩むようでしたら、
いっそ、ＳＱＬを覚えた方が楽、なのでは、と思っています。
ともあれ、試しに動かしてみてやってくださいませ。

' ' //
Sub Re9375869W()
Const Bブック = "Bブック.xlsx"　'　■ブック名を拡張子込みで正しく指定！！
Dim oConn As Object　'　As ADODB.Connection
Dim oRSet As Object　'　As ADODB.Recordset
Dim v, sPath As String, sSQL As String
　　sPath = ActiveWorkbook.Path & "\" & Bブック　'　同じフォルダー内にあるBブックのフルパス
　　Set oConn = CreateObject("ADODB.Connection")
　　oConn.Open "Provider=" & "Microsoft.ACE.OLEDB.12.0" & _
　　　　　　";Data Source=" & sPath & _
　　　　　　";Extended Properties=""Excel 12.0;HDR=No;ReadOnly=True"""
　　sSQL = "SELECT * FROM [Sheet1$A:D] " & _
　　　　　　"WHERE F2 = (SELECT TOP 1 * FROM [Sheet1$A:A] WHERE F1 <> '検査値')"
　　Set oRSet = oConn.Execute(sSQL, , 1)
　　If Not oRSet.BOF Then v = oRSet![F4]
　　ActiveWorkbook.Sheets("Sheet1").Range("A2").Value = v

　　oRSet.Close:　　Set oRSet = Nothing
　　oConn.Close:　　Set oConn = Nothing
End Sub
' ' //

BブックSheet1A2（=検査値）の値が、
空の場合、または、BブックSheet1B列に該当がない場合は、
AブックSheet1A2の値を空にするという仮の仕様です。

　oConn.Open ... で、
Bブックをデータソースとして接続します。
引数がやや長ったらしいですが、
変数sPathで指定したBブックのフルパスの部分以外は
ほぼ固定なので、定型のものとして考えてください。

Bブックのレイアウトや項目名が変わったら、
ＳＱＬを書き換えることになります。
　F2　F1　[F4]
の意味は、順に、
フィールド２(=B列)、フィールド１(=A列)、フィールド４(=D列)、です。
　[Sheet1$A:D]　[Sheet1$A:A]
は、Sheet1のA:D列、Sheet1のA:A列、です。
　'検査値'
は、BブックSheet1A1の項目名を'で括って文字列値として指定したものです。

因みに、BブックSheet1A2（=検査値）の値に依らないで、
マクロから直接、検査値を指定する場合は、sSQL = ... の部分を
　　sSQL = "SELECT * FROM [Sheet1$A:D] WHERE F2 = 'c'"
のように、より簡単（基本的）なものに書換えるだけで
対応できます。
この場合のＳＱＬの意味としては、
　テーブル[Sheet1$A:D]から、　←FROM
　B列が'c'であるレコード、　←WHERE
　すべて* を選択　　　　　←SELECT
みたいな感じ。
　v = oRSet![F4]
で、抽出後のレコードセットから（１件めの）、
D列のデータを取得しています。
検査値にマッチするレコード（データ）が複数ある場合には、
２、３行、ループ処理を書き加えることで、
マッチしたデータを全件取得するようにも出来ます。

以上、ご参考まで。

お礼

有難うございます。理解が難しいですが勉強になります。