■SQL.REQUEST関数

VBAではなく、外部アドインでのワークシート関数の話です。
こちらには『Excel 2002 でのみサポート』とありますが、2000にも元からあったような気がします（？）。
まぁ、別途ダウンロードもできるようになっているのであまり細かい事は言わないようにしよう。

たまに、『他のファイルを参照する時に、セルにファイル名、シート名を入力して、その値を使ったリンク式で参照できないか、ただし、そのファイルは閉じたまま』...という質問を見かけます。
セルの値を変更する事で、簡単に参照先を変更したいという事でしょう。
セル値を参照してというと、INDIRECT関数などですが、これは閉じているファイルは参照できません。
通常はマクロを組み合わせたり、数式自体を置換する事で対応したり、というアドバイスになってしまいますね。
ですが、実用的かどうかはさておき、XLODBC.XLAのSQL.REQUEST関数を使えばできなくもない...というところでしょうか。

一例として、下記のようなコードで使用例を作ってみました。

Sub try()
　　Const wkPath As String = "D:¥"
　　Const wkBook As String = "test.xls"

　　'新規Book作成しダミーデータセット、D:¥test.xlsとして保存後閉じる。
　　With Workbooks.Add(xlWBATWorksheet)
　　　　With .Sheets(1)
　　　　　　.Range("A1:B1").Value = Array("field1", "field2")
　　　　　　.Range("A2:B10").Formula = Array("=ADDRESS(ROW(),COLUMN(),4)", "=ROW()")
　　　　End With
　　　　.SaveAs wkPath & wkBook
　　　　.Close
　　End With
　　'アドインできない場合、手動で。
　　AddIns("ODBC アドイン").Installed = True
　　'ThisWorkbook.Sheets.AddにSQL.REQUEST関数をセット。
　　With ThisWorkbook
　　　　With .Sheets.Add
　　　　　　.Range("A1:F1").Value = Array("path", "book", "sheet", "chk", "sql", "ans")
　　　　　　.Range("A2:D2").Value = Array(wkPath, wkBook, "sheet1", "A2")
　　　　　　.Range("E2").Formula _
　　　　　　　　= "=""SELECT field2 FROM [""&C2&""$] WHERE field1='""&D2&""'"""
　　　　　　.Range("F2").Formula _
　　　　　　　　= "=SQL.REQUEST(""DSN=Excel Files;DBQ=""&A2&B2,,4,E2,FALSE)"
　　　　　　.Columns("A:F").AutoFit
　　　　　　Application.Goto .Range("F2")
　　　　End With
　　End With
End Sub

（Write側ThisWorkbookのシート）


A2:E2までを条件入力エリアとしてます。

書式は SQL.REQUEST(接続コード, 出力セル, ダイアログ表示, ステートメント, 列名表示) で、
[接続コード]……… "DSN=Excel Files;DBQ=" の後にファイルのフルパス名。
[出力セル]…………結果が返されるセルですが、ワークシート関数として使う場合は設定しても無視され、式の入力セルに結果が返るので省略します。
[ダイアログ表示]…例のような関数として使う場合は 4 の『表示しない』にして、設定がおかしかったらエラー値が返るようにしておけばいいでしょう。
[ステートメント]…SQLの構文を記述します。SQLについて学習すると、応用の幅が広がるかもしれません。
[列名表示]…………結果に列名（見出し）を表示させるかどうかです。結果を配列（というかQUERY結果のようなデータ群）として表示させたい場合はTRUEにしたほうが良いかもしれません。

参照先のサンプルは下図のようなデータで、D2セルに入力した値を照合して、そのB列の値を得る、というようなVLOOKUP的な例示にしてみました。

（Read側D:test.xlsのSheet1）