Ｅｘｃｅｌでセルの値を引用したいのですが・・・

質問

セルの値を他のbookから引用したい場合
='ドライブ名:\フォルダ名\[book名.xls]シート名'!セル位置
になると思うのですが
この際、「シート名」を任意のセルの文字列で指定出来ないのでしょうか？
また、この任意の文字列をプルダウン方式で選択するようにするにはどのようにすれば良いのでしょうか？
ご教授の程、宜しくお願い致します。

回答No.5

WWolf · ベストアンサー

大体で申し訳ないですがこんな感じですか？

Sub Test()
Dim DrPh, Da, FileName, FilePath As Variant
Dim Pos As Integer
FileName = Application.GetOpenFilename("xlsファイル (*.xls), *.xls", , "ファイルの選択", , False)
s = Dir(FileName)
Pos = InStrRev(FileName, "\")
DrPh = Left(FileName, Pos)
Co = 4　’E列
i = 1: j = 1
For ro = 3 To 123　’roは3行目から参照が始まり123行まで参照する
q = " '" & DrPh & "[" & s & "]sheet1'!" & "R" & ro & "C" & Co
Da = Application.ExecuteExcel4Macro(q)
Cells(i, j).Value = Da　’ｉは吐き出したい行、ｊは吐き出したい列
i = i + 1
Next
End Sub

質問があればどうぞ

お礼

返信が遅くなり申し訳ありません。
せっかく御考案いただいたプログラムですが意味が全く理解できません。
よろしければ上記プログラムの各々の行の下に意味を記載していただけないでしょうか？
宜しくお願い致します。

回答No.7

ki-aaa

補足を読みました。

>E3には
>=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F"&ROW())
>と入力することによって、下方向へのコピーができます。
・・・ROW()は、E3に書き込めば、３に成り、E4に書き込め>ば、４に成ります。

これを
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F"&ROW()+xx)
のように、最後に行数を調整するために、加減算をしてください。　

C7には
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!J3")
は
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!J"&ROW()-4)
と成ります。

ただし、ROW()+XX,ROW()-XXの値が、ゼロ以下になると、エラーになります。

お礼

度重なるアドバイスありがとうございました。

回答No.6

ki-aaa

>コピーする際はセル位置が自動で変わっていかないのですね

行方向(下方向)へのコピーのみは、そのように対応できます。　　

>E3には
>=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F3")
>と入力し、E4には
>=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F4")
>・・・・、としたいのです。

E3には
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F"&ROW())
と入力することによって、下方向へのコピーができます。
・・・ROW()は、E3に書き込めば、３に成り、E4に書き込めば、４に成ります。

列方向のコピーにも、OFFSETを使うことによりできるようになりますが、式があまりにも複雑になり、お勧めできません。

お礼

返信が遅くなり申し訳ありません。

>E3には
>=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F3")
>と入力し、E4には
>=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!F4")
>・・・・、としたいのです。

と記載しましたが
C7には
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!J3")
と入力し、C8には
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "!J4")
・・・・、としたいのです。

の間違いでした。
ですので「ROW()」では対応できません。
申し訳ありません。

回答No.4

WWolf

補足など読ませていただきました。
ところでエクセルのＶＢＥの起動方法（ツール-＞マクロVisialBasicEditor)はおわかりですよね？
先に御礼の内容として
＞ちなみに他のご指導者の答えを元にすると私のファイルでD3に=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!J3")と入力します。（$E$4が参照セルです。）D4にはJ3がJ4となり、順次J5、J6・・・また、E3にはF3となり順次F4、F5・・・となります。

とのことですが、
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!$J$3")
でいけば”D4にはJ3がJ4となり、順次J5、J6・・・”にはならないことはないですか？
”また、E3にはF3となり順次F4、F5・・・となります。”は理解できないですね。

マクロについてはもう少し補足お願い致します。
D3、D4・・・とに参照BookのE4の値が必要なのですか？そうであればD3からDの何行目まで必要ですか？とりあえず補足を考慮した（固定ファイルみたいなので）マクロを下記に書きます。またわからなければ質問をどうぞ。

Sub Test()
Dim DrPh, ITI, Da
DrPh = "f:\" 'ここにフルパスを
s="Book名.xls”
ITI = "R4C5"
q = " '" & DrPh & "\[" & s & "]sheet1'!" & ITI
Da = Application.ExecuteExcel4Macro(q)
cells(4,4).value=Da
End If
End Sub

お礼

お手数をおかけしております。

＞ところでエクセルのＶＢＥの起動方法（ツール-＞マクロVisialBasicEditor)はおわかりですよね？

すいません、全くの素人でわからないです。

＞先に御礼の内容として
＞＞ちなみに他のご指導者の答えを元にすると私のファイルでD3に=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!J3")と入力します。（$E$4が参照セルです。）D4にはJ3がJ4となり、順次J5、J6・・・また、E3にはF3となり順次F4、F5・・・となります。

＞とのことですが、

=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!$J$3")
でいけば”D4にはJ3がJ4となり、順次J5、J6・・・”にはならないことはないですか？
「＄」を入れても入れなくてもならないです。

＞”また、E3にはF3となり順次F4、F5・・・となります。”は理解できないですね。

説明不足ですいません、E3には
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!F3")
と入力し、E4には
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!F4")
・・・・、としたいのです。

＞マクロについてはもう少し補足お願い致します。
D3、D4・・・とに参照BookのE4の値が必要なのですか？そうであればD3からDの何行目まで必要ですか？

D3からD15、D25からD33、D43からD51・・・、と規則的に跳び跳びにD123まで必要です。

＞とりあえず補足を考慮した（固定ファイルみたいなので）マクロを下記に書きます。またわからなければ質問をどうぞ。

お手数ですがまたお願い致します。
ちなみにドライブが使用するPCによって変わるのですが（メモリースティックの為）ＯＫでしょうか？

回答No.3

WWolf

関数でいくなら＃1、＃2さんの回答で良いと思います。
下記ではＢｏｏｋを開かず参照ＢｏｏｋのＡ1セルを取り出します。

Ｄｒｐｈ＝ブックの入っているフルパス
ＩＴＩ＝セルの番地

Sub Test()
Dim fs, f, f1, fc, fn, s, sy, DrPh, ITI, Da
DrPh = "c:\"
ITI = "R1C1"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(DrPh)
Set fc = f.Files
For Each f1 In fc
s = f1.Name
sy = Right(s, 3)
If sy = "xls" Then
q = " '" & DrPh & "\[" & s & "]sheet1'!" & ITI
Da = Application.ExecuteExcel4Macro(q)
MsgBox Da
End If
Next
End Sub

参考にして下さい。分らないことがあれば聞いてください。

お礼

ちなみに他のご指導者の答えを元にすると私のファイルでD3に
=INDIRECT("'F:\フォルダ名\[ファイル名.xls]" & $E$4 & "'!J3")
と入力します。（$E$4が参照セルです。）
D4にはJ3がJ4となり、順次J5、J6・・・
また、E3にはF3となり順次F4、F5・・・
となります。

補足

ご教授有難うございます。
せっかくではありますがＶＢＡは全くの素人でこのプログラムを何処にどうやって入力するのかもわからない状態です。
しかしながらWWolf様のプログラムが私の目指すところであります。
よろしければ詳しくご教授いただければと存じます。

回答No.2

n_and_n

INDRECT関数を使います。　但し、そのブックを開いておかないと #REF! のエラーとなります。

　　　Ｂ列
４行　ドライブ名:\フォルダ名...\フォルダ名
５　　book名.xls
６　　シート名
７　　セル位置
１０　="'" & B4& "\[" & B5 & "]" & B6 & "'!" & B7
：
１２　= INDIRECT(B10)

Ｂ４セル～Ｂ７セルに具体的なデータを入力します。　Ｂ１２セルに値が表示されます。（冒頭の但し書きのようにそのbookが開いていないといけません。）

「シート名」をプルダウン方式で選択するのは、シート名のリストを予め用意します。例を示します。

　　　Ｋ列
４行　Sheet1
５　　Sheet2
６　　Sheet3

メニューバー[データ]-[入力規則]で「入力値の種類」を『リスト』とし、「データ」欄に上の例では K4:K6 とし、「プルダウンボックスから選択する」のチェックボックスをＯＮとします。

お礼

ご教授有難うございました。

回答No.1

ki-aaa

=INDIRECT("　")を使うとできます。

='ドライブ名:\フォルダ名\[book名.xls]シート名!セル位置
=INDIRECT("'ドライブ名:\フォルダ名\[book名.xls]シート名!セル位置")
=INDIRECT("'ドライブ名:\フォルダ名\[book名.xls]"&A1&"!セル位置")
・・・A1:シート名を入れておく
この三つの式は、同じ結果になります。

>この任意の文字列をプルダウン方式で選択するようにするにはどのようにすれば
A1セルに、データ→入力規則→リストを設定すればできるると思います。

お礼

ご教授有難うございました。
この関数で連続してコピーする際はセル位置が自動で変わっていかないのですね・・・。
地道に入力していきます。