エクセルでフォルダ内のBookのセルの参照
- エクセルでフォルダ内のBookのセルの参照について質問があります。
- 特定のフォルダ内にある複数のBookの個別情報(製品名)のセルを参照し、別のBookのリストに格納したいです。
- 参照するシートは5-10枚で、参照するセル番地は15個程度です。また、参照セルはリストのシートの最下行のセルに入れたいです。VBA初心者のため、ご支援いただけると助かります。
- ベストアンサー
エクセルでフォルダ内のBookのセルの参照
いつもお世話になっております。 決まったフォルダ内の複数のBook「個別情報(製品名)」のフルパスを別のBook「リスト」のA列に入れると、「個別情報(製品名)」のシート(規格)の指定のセルを「リスト」のシート(規格一覧)に行方向に引っ張ってくるようにしたいのですが。 1度に参照するシートは5-10枚。 参照するセル番地は決まっていて、多いと15個程度。 参照セルは「リスト」のシート(規格一覧)の最下行のセルに入るようにしたい。 閉じたままのBookのシートを指定して、セル参照が出来ることを少し前に知ったのですが、当方には難易度が高そうで躊躇していましたが最近コロナで時間が出来たのでチャレンジしてみようかと。 当方永遠のVBA初心者ですのでその辺もご配慮の上よろしくお願い致します。
- akira0723
- お礼率68% (647/940)
- Excel(エクセル)
- 回答数4
- ありがとう数3
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
ちょっとハードルが高く、 今後、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は、参照するセル番地の埋まったセルです。
その他の回答 (3)
- masnoske
- ベストアンサー率35% (67/190)
いきなり閉じたままのBookのシートを指定してセル参照をするマクロを組むのではなく,まずはBookを開いてセル参照するマクロを組むべきです. 理由は簡単で,Bookを開かずに操作した場合,目で確認できないのでデバッグに時間を取られてしまいます. なので,Bookを開いた状態で動作することを確認した上で,閉じた状態で動作させるようにするのが結局は近道だったりします. なお,Bookを開いた状態だとセルやレンジの指定が A1形式でも動作するので,R1C1形式でマクロを組みましょう(閉じた状態では A1形式は使えなかったはずです). なので,コーディングする時も Excelの表示を R1C1形式にしておいたほうが良いでしょう. がんばってください.
お礼
アドバイスありがとうございます。 目指すのは仰るレベルなのですが。。。 NETの3行マクロは動くのですが、応用ができない悲しいレベルです。
- kkkkkm
- ベストアンサー率65% (1618/2457)
> by 「永遠の初心者」 なのでしたら、最初から複雑な事を理解するのは無理ですから、一定のファイルの一定のシートの一定のセル範囲を対象ファイルを開かずに転記するという事から始めたほうがいいと思いますよ。それがうまくいかなければ、複数のファイルや可変なシート及び可変なセル範囲で実行してもうまくいきませんし、複数の処理ではどこが悪いのか分からないと思います。 > ご回答のコードの意味が分からないので 頭で理解しようとしても「永遠の初心者」でしたら無理ですから、実際に実行してみてどのような動きなのかを体験して、エラーになったらステップ実行などで何が違うのかを探して解決していくのがチャレンジです。
お礼
全くご指摘の通りで、自分でも情けないとは思うのですが・・・ NETの”C:・・・の例を自分のフォルダ、ファイルに置き換えるのにも何度も試行錯誤が必要なレベルです。 よってお手数をおかけすることが躊躇われるのですがつい質問してしまいます。 御免なさい。
補足
御解答のTANAKAさんのコードも最初に試してはいるのですが、お手上げ状態(レベル)です。
- kkkkkm
- ベストアンサー率65% (1618/2457)
ブックを開かないでセルのデータを読む 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」・・・ のファイルも同じフォルダ内に存在し続けます。 ご回答のコードの意味が分からないので、上記ができるのに出来ないと思っていたら御容赦!! by 「永遠の初心者」
関連するQ&A
- エクセルで別のブックのセル参照
Excel2000を使っています。 あるセルに、別のブックのセルの値をそのまま参照したいのですができません。解説書には [ブック名]シート名!セル番地 と書いてあってその通りにしたのですが、入力した式がそのまま表示されるだけで値が参照されません。どうすればいいのでしょうか。 あまり覗けないので回答が遅れるかもしれませんが、よろしくお願いします
- ベストアンサー
- オフィス系ソフト
- Excel セル参照で参照ブック指定
指定したセルに拡張子を抜いたブック名を入力し、そのセルを参照してブック参照することは可能でしょか? 「INDIRECT」では参照ブックを開かないと表示されないため目的が達成されません。 (例) Book1.xlsx A1=test Book2.xlsx A1=Book1 A2=["&(A1)&".xlsx]Sheet1'!A1 何か良い解決策をご存知の方、ご教授願います。
- 締切済み
- Excel(エクセル)
- エクセルでBookを開かずにセルを参照する(再)
昨日同じ内容で質問させていただいたのですが、添付図を付けることに思い至らずお手数をおかけしたので出直し質問です。 添付の図で製品毎の送付先連絡票という定型の帳票があり、毎日フォルダに追加されます。 この複数の帳票のフルパスを下の図の「送付先リスト」のB列にC&Pしたら、その製品のC列から行方向に帳票のシートのセル内容を参照するようにしたいのです。 このリストは新しいファイル(フルパス)が下にどんどん追加されます。 帳票(参照元)は同じ名前のシート1枚で、セルはA3、B5・・・と決まったセルです。 これが出来れば種々の定型帳票のリスト化に非常に有用なので宜しくお願いします。 開いたファイルのセル参照はINDIRECT関数で何とか出来ていますが10枚のBookを開いて、リストにフォルダのフルパスをC&Pして¥で区切り位置を使用して・・・でソロソロ限界ですので何卒HELPお願いします。
- ベストアンサー
- Excel(エクセル)
- エクセルのブック参照について教えてください
エクセルの機能に、あるセルに=[Book1.xls]Sheet6!B5のように指定すると、他のブックを参照することができますが、 これって、ネットワークで繋がった異なるPC同士のブックでも出来るんでしょうか? 例 PC1に置いてある、Book1.xlsのSheet6のB5を PC2で制作中のブックのセルに指定する。みたいな。
- ベストアンサー
- オフィス系ソフト
- エクセルで誤ってほかのブックを参照しているセルを見つけ出すには
エクセルの2つのブックを結合してひとつのブックとして使っているのですが、開く都度「このブックにはほかのデーターソースへのリンクが含まれています。....」というメッセージが出ます。 メッセージが出るのは、計算式のセル参照が旧のブック名を参照してしまってるのが原因だとは、知っているのですが、膨大な行数、シート数のブック(3メガくらいの容量)ですので手作業で見つけて修正するのは大変困難で困っています。 何か、ブック全体から簡単におかしな参照をしている箇所を見つける方法はないのでしょうか。 一方のブックの改訂版がしばしば出されるためその都度、リンクした旧のブックから残りのシート部分をコピーして貼り付けるのですが、その都度 旧のブックを参照する計算式が残ってしまい、見つけて修正する作業に困っています。
- ベストアンサー
- オフィス系ソフト
- エクセルで別ブックのセルを参照する式
エクセルで別ファイルのセルを参照する方法をお教えください。 一日1シートに在庫を記録しているブックがあります。 ブックは一か月ごとの、新しいブックになります。 シートの名前は11月1日なら、「1101」というふうにしています。 各シートのセル、C17~E17、C18~E18 、C19~E19の部分だけを別ブックに参照して表を作りたいのです。 別ブックのセルを参照する時は、='パス名[ブック名]シート名'!セル名 みたいなので ='D:desktop[2012.11.xls]1101'!C17 という式を作りました。(パス名は適当です。) シート名の「1101」を「1102」…と簡単にしていく方法をお教えいただけないでしょうか。 QNo.5943822のinu-nekoさんの質問など参考にさせていただきましたが、回答にたどり着くことができませんでした。 ご存知の方がいらっしゃいましたら、どうかよろしくお願いいたします。
- 締切済み
- オフィス系ソフト
- Excel 同ブックの他シートを参照するセルを、他ブックのセルにコピー
こんにちは。Excelの使い方について質問させていただきます。 よろしくお願いします。 Excel2003を使っています。 シート数、シート名がまったく同じブックが2つあります。 AブックのAシートのA1セルを参照しているセルである AブックのBシートのA1セルをコピーします。 BブックのBシートのA1セルにペーストします。 BブックのAシートのA1セルを参照して欲しいのですが、 別ブックであるAブックのAシートのA1セルを参照するように 自動的に変換されてしまいます。 シンプルにそのままコピーすることは不可能でしょうか。 セルのコピーでは無く、中の文字列を選択してコピーしても、 何が起こっているのかよくわかりませんが上手くいきません。 Excel初心者です。マクロはまったく使えません。 親切な方、どうぞよろしくお願いします。
- ベストアンサー
- その他(業務ソフトウェア)
- エクセル 指定したブックを参照したい
指定したブックを参照したいと思っています。 具体的には、 A1に参照するデータが入っています。 例えば、A1に田中が入っていればブック名が田中のデータを佐藤が入っていればブック名が佐藤のデータを参照するようにしたい。(元データはあるものとします。) このとき、式は ='C:\...\[ブック名]Sheet1'!A1 のようになると思います。 参照ブック名をA1にあるデータに指定するにはどうすればよいのでしょうか?? よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- 複数ブックから指定のデータを抽出してリストにしたい
VBAで複数のブックから指定のデータを抽出してリストを作りたいです。 ・地域ごとに分かれたブックが複数あります。(全て同一フォルダ内、リストも同フォルダに作ります) ・シートの構成は「原本」「一覧」「商品名A」「商品名B」(※5~20シート位) ・商品は月替わりで増減がある。 「一覧」…商品名のシートから月の販売個数を集計した物を一覧表にしてあります。 必ず各ブックの2枚目のシートになります。 ・各地域のブックから「一覧」のシートを参照して「商品名」「メーカー」「合計」を抽出する。 ・リストには地域別でメーカー毎に一覧を表示したい。 別ブックの特定のセルを参照して一覧にするのは簡単なのですが、商品が毎月増減します。 なので各地域別のブックを更新したら、リストの方も自動的に反映されるようにしたいのです。 色々検索してみて各ブックの指定のシート、指定のセルからマクロで自動的に読み込むというのは見つかりましたが、セルの範囲が一定ではない場合はどうすれば良いのか解りません。 出来ればリスト以外のブックは開かずにデータを抽出したいです。 (※実際に入力作業を行うスタッフは本当に入力しか出来ないので、このブックを先に開いて~とかは混乱して出来ないようです。マクロで全てのファイルを開く等も勝手に閉じてしまったりするので避けたいです。)
- ベストアンサー
- Excel(エクセル)
- 参照先をセルに入力して、他のブックのセルを参照する方法
Excelで、他のブックのセルを参照したいです。 たとえば、あるExcel(test.xls)上から、別のフォルダにある複数のブック C:\AAA.xls D:\BBB.xls の値を参照する場合ですが(AAA.xlsとBBB.xlsは、値の内容は違うが、同じシート名の同じセルに値が入っている)、 test.xlsのSheet1のA1に「C:\」、A2に「AAA.xls」を入力すると、test.xlsのSheet2にはAAA.xlsの値を表示し、 test.xlsのSheet1のA1に「D:\」、A2に「BBB.xls」を入力すると、test.xlsのSheet2にはBBB.xlsの値を表示するような方法です。 参照先が固定であれば、='C:\[AAA.xls]Sheet名'!D5・・・のような指定ができるのですが、参照するファイル名とパス名が都度変わるので、入力したセルの値で参照先を変えたいのです。 ちなみに、INDIRECTを使うやり方も考えましたが、参照先のブックを開いていないと値が表示されないようなので、却下されました。 (参照先のブックは開かなくても値を参照させたいです) どなたかご存知の方がいらっしゃったら、教えてください。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
お礼
お手数をお掛けしました。 当方にはハードルが高い要求に対し、質問の仕方も悪いようなので図入りの質問で出直します。
補足
いつもお世話になっております。 早々に試してみました。 新しいBookのSheet1に添付の図と全く同じ位置にフルパス、シート名、セルを入れてみて実行してみました。 マクロ名の選択で「Sheet1 Myrefresh」というマクロ名が1つだけ表示され、それを実行しましたが何度やっても何も起こらず。(エラーにもならず) 目的ファイルを開いたときと閉じたままでも試行しましたが何も起こらずでした。 実は、質問の仕方が悪く、ご回答の内容は当方の期待する動きではありません。 被害拡大防止のために一旦締めさせていただいてご回答のような図入りで出直します。 お手数をお掛けし、本当にごめんなさい。