エクセルのマクロで、別ファイルの数値を参照する方法

質問

現在aaa.xlsというファイルを開いていて、
そのセルA1には20070621という数値が入っています。

別にbbb_20070621.xlsというファイルがあって、
そのファイルのsheet1内の、B1の値を参照したいと思います。

bbb_20070621.xlsのようなファイルが無数にあるので、
ファイルを開かなければいけないINDIRECT関数は使いたくありません。
=INDIRECT("[bbb_"&A1&".xls]Sheet1!B1")

ファイルを開かず参照できるように、
できればマクロ（VBA）で組みたいと思っています。

RangeやCellでいろいろ試してみたのですが、
どうもうまくいかず、
分厚いマニュアルにも書いてありませんでした。。

初歩的な質問ですいませんが、
どなたかよろしくお願いします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

>変数を使わず、

初歩的な内容ですから、少しじっくりと考えれば答は出てくるはずです。
ただ、変数を使わず、マクロに代入というのは、リテラル値（文字列）といいますが、ミスが多くなります。なるべく、そのような方法は変数が見極めるようになってからしたほうがよいです。VBAの基本は、変数の型をきちんと入れます。

セルから取る場合は、一旦、変数で受けて、それを代入させます。

("'c:\data\file\" & "[bbb_" & A1 & ".xls" & "]" & Sheet1!B1)
これは、VBAの書き方ではありませんから、それ自体の書き方でエラーになってしまいます。

シート2 のA1 に、ファイル名を、アドレスを、シート1 のB1 に入れるには、以下のような書き方になります。しかし、VBAは、ワークシート関数とは、まったく違う種類のものですから、その考え方を変えないと、VBAを使いこなすのは大変です。

'標準モジュールがベターです。

Sub TestR()
Dim myPath As String
Dim xlFile As String
Dim myAddress As String

　myPath = "c:\data\file\"
　xlFile = "bbb_" & Worksheets("Sheet2").Range("A1").Value
　myAddress =Worksheets("Sheet1").Range("B1").Value
　MsgBox getDirectData(myPath, xlFile, myAddress)
End Sub

Function getDirectData(myPath As String, Fname As String, oAddress As String)
'ダイレクトにxlファイルから値を取り出すサブルーチン関数
Dim r1Address As String
Dim myData As Variant
Dim k As Integer
On Error GoTo ErrHandler
r1Address = Mid$(oAddress, 1, InStr(oAddress, "!") - 1) & "'!" & _
　　　　　　Range(oAddress).Address(1, 1, xlR1C1)

　 If Right(myPath, 1) <> "\" Then myPath = myPath & "\"
　 k = InStr(Fname, ".xls")
　 If k > 0 Then
　　　Fname = Mid$(Fname, 1, k - 1)
　 End If

　 myData = ExecuteExcel4Macro _
　　　　("'" & myPath & "[" & Fname & ".xls]" & r1Address)
　 If Not IsError(myData) Then
　　　　getDirectData = myData
　 End If
ErrHandler:
End Function

お礼

御礼が遅くなってすいません。
ご丁寧にありがとうございます！

>しかし、VBAは、ワークシート関数とは、まったく違う種類のものですから、
>その考え方を変えないと、VBAを使いこなすのは大変です。

そうですよねー。。
でも教えていただいたものを応用して、何とかいけそうです。
心より感謝いたします！<(_ _)>

回答No.2

K-19

ほかのエクセルのシートのある値を参照するには、
bbb_20070621.xlsのSheet１のB1からB9までにデータが入ってます。
aaa.xlsのSheet１のA1に
bbb_20070621.xlsのSheet１のB1の値を参照するには、
A1のセルに
='C:\Documents and Settings\user1
\My Documents\[bbb_20070621.xls]Sheet1'!$B$1
と記入すればよいのでは？？
'C:\Documents and Settings\user1
\My Documentsはbbb_20070621.xlsが格納されている場所を
指示します。
bbb_20070621.xlsを開かなくてもできますよ。
試してみてください。

補足

こんにちは、早速ありがとうございます！
説明の言葉足らずで申し訳ありません。。

ファイルは日々追加されていきますし、
今後条件式などを入れて処理が複雑になっていきますので、
変数を使ったマクロを組みたいと思っています。

回答No.1

Wendy02

こんにちは。

二種類の方法があります。
ひとつは、データベース・オブジェクトを使って取り出す方法です。

もうひとつは、古い方法ですが、

Sub getClosedData()
Dim myPath As String
Dim FName As String
Dim ShName As String
Dim MyData As Variant

Dim myRow As Long
Dim myCol As Integer

myPath = Application.DefaultFilePath & "\"　'デフォルトパス
FName = "Test01.xls"
ShName = "Sheet1"
myRow = 1
myCol = 2　 'B1の値を取り出す

　 MyData = ExecuteExcel4Macro _
　　　　("'" & myPath & "[" & FName & "]" & ShName & "'!R" & myRow & "C" & myCol)
　 If Not IsError(MyData) Then
　　　　MsgBox MyData
　 End If

End Sub

>ファイルを開かず参照できるように、できればマクロ（VBA）で組みたいと思っています。
可能なのですが、今まで、私がせっかくコードを書いても評価は受けたことがありません。より複雑になるからだとは思います。実際に、どういう手順になるのか、良く考えてからやってみると良いです。そういう私も、この方法を掘り下げて考えたことがありません。

補足

こんにちは、早速ありがとうございます！
少し応用しまして、変数を使わず、
下記のように書いてみましたが、エラーになってしまいます・・。

MyData = ExecuteExcel4Macro _
("'c:\data\file\" & "[bbb_" & A1 & ".xls" & "]" & Sheet1!B1)

こういう使い方は許されないのでしょうか・・。