Excelマクロで指定したファイル名を関数に引用したい

マクロで別ファイルのファイル名をアクティブシートの指定セルから
指定し、そのファイル内にある数値をVLOOKUPで拾うマクロを構築したいと思っています。

マクロについてはネットで調べたりと色々しているのですが
理解力が足りずコードが読めないため
ピッタリと当てはまるマクロのコードを探すことが出来ません。

やりたいことは、下記のようなことです。

①アクティブファイルAのﾘｽﾄシートのセルA1にデータが載っているファイル名を入力
　※データ参照のファイル名が毎回変わるため、ファイル名を指定するマクロを選択しました。
②①で指定したファイル(仮にファイルBとしましょう)のシート1から該当データを探すため
　VLOOKUP関数をセルに書き込むマクロを使用
　※①と②のコードを作成する作業を別々に行ったため、どのように関数内に①のファイル名を
　　当て込めればいいのかが判りません。

Sheets("シート1").Select
Range("B15").Select
ActiveCell.Formula = _
"=VLOOKUP(検索値,'①で指定したファイルBのシート1'!$A:$F,6,0)"
　　↑のように関数を使おうとしたのですが、検索範囲のファイル名を指定する方法が解らず
　　行き詰っております。


何故VLOOKUPを使用しようとしているのかというと、拾うデータの行がﾃﾞｰﾀ量に応じて
変動するからです。
②を構築した時は、固定ファイル名でやって後からファイル名を置換すれば良いと
思っていたのですが、いちいちリンク先を探しに行く動作が挟まってしまい、返って時間が掛かるため直接ファイルを指定するしか方法が無いのではないかと思い、①の方法をネットで探した次第です。
もっと効率の良い方法もあるのかもしれませんが、解る範囲で勉強しながら作業しております
ので、何でそんなことしようとしてるかワカラナイと仰らずご教授頂けましたら幸いです。
①を②の中に引用する以外にも何か方法が在ればお教え頂きたいです。

欲をかいて申し訳ありませんが、出来ればファイルBは閉じたまま作業したく思っております。

No.1 ベストアンサー 回答者： ママチャリ 回答日時： 2016/08/13 22:10

Workbook.ChangeLink メソッドを使えば、リンク先のファイルを変更することができます。

こんな感じです。

Sub sample()
With ThisWorkbook
.ChangeLink Name:=.LinkSources(xlExcelLinks)(1), _
NewName:=Range("A1"), Type:=xlExcelLinks
End With
End Sub

上記のマクロは、「現在リンクが張られているファイル」を「A1セルで指定されているファイル」に変更するものです。
ただし、注意点が2つあります。
1つ目は、A1セルに入力するファイル名はフルパスで指定すること。
2つ目は、現在張られているリンク先が2つ以上ある場合、上記マクロは1番目のリンクを変更します。もし変更すべきリンクが1番目でない場合は、インデックス番号を修正する必要があります。

この回答へのお礼

ありがとうございます。

解釈自体を間違っていたら失敗してしまうので確認したいのですが
教えて頂いたメソッドは
固定ファイル名を使った数式でデータの引用をした後、
リンク名をA1セルで指定されているファイルに変更する
…という趣旨の内容で良いでしょうか？

但し、ファイル名はフルパスを指定｡
そして…ゴメンナサイ。１番目のリンクを変更するという部分が
よく解らないです。
１つのシートに、同じファイルの色々なシートから様々な値を拾い
リンクを張っている状態になるのですが…
この場合は修正が必要と言うことでしょうか？
勉強不足で申し訳ありません！
教えて頂いたコードを使って実験してみます。

お礼日時：2016/08/15 16:57

No.2 回答者： ママチャリ 回答日時： 2016/08/15 21:23

ママチャリです。

お礼に書かれている確認の件ですが、前半部分はsraphさんの理解で正しいです。
後半部分については、ひとつのファイルに対して複数の参照をしているのであれば、修正する必要はないです。修正が必要なのは、複数のファイルに対してリンクを張っている場合だけです。「データ」タグの「リンクの編集」で複数のリンク先が表示される場合は、修正が必要な可能性があります。