エクセルでフォルダ内のBookのセルの参照

質問

いつもお世話になっております。

決まったフォルダ内の複数のBook「個別情報（製品名）」のフルパスを別のBook「リスト」のA列に入れると、「個別情報（製品名）」のシート（規格）の指定のセルを「リスト」のシート（規格一覧）に行方向に引っ張ってくるようにしたいのですが。

１度に参照するシートは5-10枚。
参照するセル番地は決まっていて、多いと15個程度。
参照セルは「リスト」のシート（規格一覧）の最下行のセルに入るようにしたい。

閉じたままのBookのシートを指定して、セル参照が出来ることを少し前に知ったのですが、当方には難易度が高そうで躊躇していましたが最近コロナで時間が出来たのでチャレンジしてみようかと。

当方永遠のVBA初心者ですのでその辺もご配慮の上よろしくお願い致します。

回答No.4

HohoPapa · ベストアンサー

ちょっとハードルが高く、
今後、VBAのメンテナンスが必要になったときや
Officeのバージョンが変わり、改修する必要が出てきたときにどうするのか？
といったリスクはありますが、
添付画像を例に、以下のコードを仕込めば、
期待のことはできます。
よかったら試してみてください。

なお、
このマクロ関数を組み込んだブックを開いただけでは
多くの場合、
最新の状態（参照先ブックの現在の値）にはなりませんので、
必要な時に、Sub Myrefresh()を実行する必要があります。

あるいは、開いた時のイベントで実行するようにしてください。
（このイベントで実行する場合は、現コードに手直しが必要です。）

'//以下、標準モジュール

Function GetWsDate(MyPath As String, ShName As String, MyAddress As String) As Variant

　Dim SQL As String
　Dim cn As Object
　Dim rs As Object
　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
　cn.Open MyPath

　SQL = ""
　SQL = SQL & "select F1" & vbCrLf
　SQL = SQL & "FROM [" & ShName & "$" & MyAddress & ":" & MyAddress & "]" & vbCrLf
　rs.Open SQL, cn
　GetWsDate = rs("F1")

　rs.Close
　cn.Close
　Set rs = Nothing
　Set cn = Nothing

End Function

'//最新のデータに更新
Sub Myrefresh()
'https://docs.microsoft.com/ja-jp/office/client-developer/excel/excel-recalculation
With ActiveSheet
　.EnableCalculation = False
　.EnableCalculation = True
　.Calculate
End With

End Sub

=GetWsDate(C3,D3,E3)
を前提にパラメータ（引数）を説明します。
C3は、対象ブックのフルパスの埋まったセル
D3は、そのブックのシート名の埋まったセル
E3は、参照するセル番地の埋まったセルです。

お礼

お手数をお掛けしました。
当方にはハードルが高い要求に対し、質問の仕方も悪いようなので図入りの質問で出直します。

補足

いつもお世話になっております。

早々に試してみました。
新しいBookのSheet1に添付の図と全く同じ位置にフルパス、シート名、セルを入れてみて実行してみました。

マクロ名の選択で「Sheet1 Myrefresh」というマクロ名が1つだけ表示され、それを実行しましたが何度やっても何も起こらず。（エラーにもならず）

目的ファイルを開いたときと閉じたままでも試行しましたが何も起こらずでした。

実は、質問の仕方が悪く、ご回答の内容は当方の期待する動きではありません。
被害拡大防止のために一旦締めさせていただいてご回答のような図入りで出直します。

お手数をお掛けし、本当にごめんなさい。

回答No.3

masnoske

いきなり閉じたままのBookのシートを指定してセル参照をするマクロを組むのではなく，まずはBookを開いてセル参照するマクロを組むべきです．
理由は簡単で，Bookを開かずに操作した場合，目で確認できないのでデバッグに時間を取られてしまいます．

なので，Bookを開いた状態で動作することを確認した上で，閉じた状態で動作させるようにするのが結局は近道だったりします．
なお，Bookを開いた状態だとセルやレンジの指定が A1形式でも動作するので，R1C1形式でマクロを組みましょう（閉じた状態では
A1形式は使えなかったはずです）．

なので，コーディングする時も Excelの表示を R1C1形式にしておいたほうが良いでしょう．

がんばってください．

お礼

アドバイスありがとうございます。
目指すのは仰るレベルなのですが。。。

NETの3行マクロは動くのですが、応用ができない悲しいレベルです。

回答No.2

kkkkkm

> ｂｙ 「永遠の初心者」

なのでしたら、最初から複雑な事を理解するのは無理ですから、一定のファイルの一定のシートの一定のセル範囲を対象ファイルを開かずに転記するという事から始めたほうがいいと思いますよ。それがうまくいかなければ、複数のファイルや可変なシート及び可変なセル範囲で実行してもうまくいきませんし、複数の処理ではどこが悪いのか分からないと思います。

> ご回答のコードの意味が分からないので

頭で理解しようとしても「永遠の初心者」でしたら無理ですから、実際に実行してみてどのような動きなのかを体験して、エラーになったらステップ実行などで何が違うのかを探して解決していくのがチャレンジです。

お礼

全くご指摘の通りで、自分でも情けないとは思うのですが・・・
NETの”C:・・・の例を自分のフォルダ、ファイルに置き換えるのにも何度も試行錯誤が必要なレベルです。

よってお手数をおかけすることが躊躇われるのですがつい質問してしまいます。

御免なさい。

補足

御解答のTANAKAさんのコードも最初に試してはいるのですが、お手上げ状態（レベル）です。

回答No.1

kkkkkm

ブックを開かないでセルのデータを読む
http://officetanaka.net/excel/vba/tips/tips28.htm

こちらを参考にして
OpenFileNameやSheetName
などの変数にご自身の環境に合わせたブック名やシート名などを入れて、必要なだけループして試してみてください。

補足

いつもお世話になっております。
早々のご回答ありがとうございます。

実は事前にご回答のURLの内容を確認して「出来そうかな」と思ったのですが、永遠の初心者には無理でした。

＞フォルダ内の複数のBook「個別情報（製品名）」の
＞フルパスを別のBook「リスト」のA列に入れ
て、マクロ実行ボタンで動く（参照）ようにしたいのです。
理由は毎月新製品の「個別情報（製品名）」のファイルが所定のフォルダに投げ込まれるので、これをコード中で指定するのではなく、
（もし、ご回答のコードの意味が違っていれば御免なさい）
ファイルオープン時の自動実行はシートの指定等いくつか使っているのですが非常に不安多し。
開いてこなくて焦った経験あり、で

＞「個別情報（製品名）」のフルパスを別のBook「リスト」のA列に入れ・・・
てマクロボタンで動く、ようにしたいのです。

複数のBookの複数のセルをリスト一覧のシートに行方向に引っ張りたいのです。

例えばファイル名「製品B」のB5,C4,H8・・・・と10-15個のセル内容と同じ月に増えたファイル名「製品C」の同じ名前のシートの同じセル内容を下の行に転記したいのです。
来月になると「製品D」、「製品E」、「製品F」・・・のファイルが増えています。
又、過去のファイル名「製品A」、「製品B」・・・ のファイルも同じフォルダ内に存在し続けます。

ご回答のコードの意味が分からないので、上記ができるのに出来ないと思っていたら御容赦!!

ｂｙ 「永遠の初心者」