こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2017-09-18 11:06:47

QNo.9375869

すぐに回答ほしいです

質問者が選んだベストアンサー

どうぞ
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

投稿日時 - 2017-09-18 14:43:00

お礼

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

投稿日時 - 2017-09-18 22:54:08

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

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

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

' ' //
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ブックのレイアウトや項目名が変わったら、
SQLを書き換えることになります。
 F2 F1 [F4]
の意味は、順に、
フィールド2(=B列)、フィールド1(=A列)、フィールド4(=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'"
のように、より簡単(基本的)なものに書換えるだけで
対応できます。
この場合のSQLの意味としては、
 テーブル[Sheet1$A:D]から、 ←FROM
 B列が'c'であるレコード、 ←WHERE
 すべて* を選択     ←SELECT
みたいな感じ。
 v = oRSet![F4]
で、抽出後のレコードセットから(1件めの)、
D列のデータを取得しています。
検査値にマッチするレコード(データ)が複数ある場合には、
2、3行、ループ処理を書き加えることで、
マッチしたデータを全件取得するようにも出来ます。

以上、ご参考まで。

投稿日時 - 2017-09-18 17:15:22

お礼

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

投稿日時 - 2017-09-18 22:50:38

あなたにオススメの質問