開いてないブックへの参照の変更方法

エクセル関数で、開いてないブックへの参照を変更する方法を教えてください。

Indirect関数は開いているブックへの参照にしか使えません。

現在は、マクロで参照式を書き換える方法(置き換える方法)を使っていますが、
時間が掛かるのと、できれば(後々別の人でもメンテナンスができるように)マクロは使わずに処理したいと思っています。

No.3 回答者： Qchan1962 回答日時： 2020/05/24 00:44

ご質問の意味を理解していないかも知れませんが、

リンク貼り付けなどで出来る参照式を変更すれば良いのでは？

参照先ファイルが開いてない場合、='C:\Users\hoge\Desktop\[huga.xlsm]Sheet1'!$A$1
参照先ファイルが開いている場合、=[huga.xlsm]Sheet1!$A$1

開いてないブックの内容を変えるのであれば、開く必要がありますが。。

＞後々別の人でもメンテナンスができるように
関数でもVBAでもメンテナンスには一定のスキルが必要になりますね。

VBAでもメンテナンスしやすいように工夫すれば、
作成に苦労するだけでわかり易くは出来ると思います。（処理内容によりますが）

例えば、
変更する可能性のあるものを設定シートなどに書き、参照、設定させるとか、（シート上で確認できる）
シート、表組みの配置や構造を関数やキーワードなどで都度検索し特定するとか、簡単なマニュアル、ヘルプを作成するとか
むしろ、変更されたくない様々な変更に対して警告や変更後の挙動（設定シートに書き出す、ヘルプなどを出力する）を作成すれば
かなり運用者にはわかり易くなると思います。

よくVBAで作成されたファイルは、変更があると動かないと言われますが、もちろん事実ではありますが
動かないように作成されているからで、そもそも、変更を捕まえられるなら、その行為にアドバイスするような
プログラムを書けばよい事になりますね。
規模が分からないので勝手な事を書きました。

的外れなら、忘れてください。

この回答へのお礼

ご回答、ありがとうございました。
詳細を記さない問い合わせで、混乱させて申し訳ありません。
【やりたいこと】
(1)毎日、「日付をファイル名にしたエクセルファイル」が、フォルダにたまっていきます。
(2)別のエクセルファイルで、「日付をファイル名にしたエクセルファイル」の特定のセルにリンクを張って、集計したり、グラフを表示したりします。また、別の日付の集計結果やグラフと比較したりします。
(3)集計やグラフを表示したい日付にするため、「置換」機能を使って、参照式のファイル名を別の日付に書き換えています。
上記の(3)の作業を省力化するため、日付を選択して、ボタンを押すと、参照式のファイル名を書き換えるマクロを作りました。
しかし、時間も結構掛かるのと、VBAのコマンドが変わるなど将来のメンテを懸念して、Indirectのような機能で、且つ、開いてないBookへの参照式を変更できる「関数」はないだろうか？
という悩みです。

言われるように、マクロの構造次第ではメンテしやすい構造にできるのだろうと思いますが、その点は怠けています。

混乱させてしまい、申し訳ありませんでした。
ご回答ありがとうございました。

お礼日時：2020/05/27 22:47

No.2 回答者： kamejiro 回答日時： 2020/05/23 22:33

＃１です。

答えになっていないコメントをお許しください。

＞マクロは使わずに処理したいと…
業務の全容が分からないので、マクロにすると、ワンクリックで処理する。
けれどエクセルのバージョンが変わるとコードも対応しなくなるので修正必要。

なら、

・マクロ仕様について後任の方に技術指導する。引き継ぐ期間は二人体制の担当にする。
・マクロをやめるなら、ブックを開く操作から関数を埋め込む手順のマニュアルを作る。
　操作員は一つずつ指でマニュアルを指しながら操作する

いずかの形づくりをして、後任者は、一つ一つ順を追って覚えてもらう。

自身、質問の意図と外れた勝手なことを書きましたことをご了承下さい。

この回答へのお礼

不明確な問い合わせで、申し訳ありませんでした。
＃３様のお礼欄に「やりたいこと」を少し具体的に記入しました。
＃２様の言われるように、しっかり引き継いだり、マニュアルを準備するなどで、将来の懸念は解消できると思います。
開いてないファイルへの参照式の中の「ファイル名を切り替える」事が、関数でできなければ、現在のマクロを使い続けるしか(今のところ)手が無いように思えております。
別視点からのご意見ありがとうございました。

お礼日時：2020/05/27 22:53

No.1 回答者： kamejiro 回答日時： 2020/05/23 21:06

自身の経験では、ブックを開かないと参照はできないと思います。

今回、マクロコードは提示まではしませんが、
（１）マクロで開いていないブックを開く
（２）マクロで関数を埋め込む
なら、可能です。

Indirect関数は使ったことはありませんが、データを参照する場合、参照元のブック名かシート名の指定が必要です。
指定されていても閉じたブックだったら無効かと思います。

＞マクロで参照式を書き換える方法(置き換える方法)
自身の業務で使ったエクセルも同じような使い方をしていました。
毎回、直していました。入力ミスをすると動きません。

ゆえ、
マクロで同じフォルダに入っているエクセルファイルを探し、ファイル名を取得する
そのファイルを開き、シートも指定し、データをコピペする
その後で、開いたファイルを閉じる
の機能を与えられないか試行錯誤しました。

全てネットで命令を調べたり、マクロの自動記録を使って得たコードを適当に解読して修正したりしました。
何とか、イメージに近いマクロが出来て、「マクロのコードを開いては直す」必要がなくなり、マクロのことを知らない方でも違和感なく使っています。

※形づくりには、かなり苦労しましたね。

この回答へのお礼

早速のご返信、ありがとうございます。
私も、マクロには苦労させられました。(VBに代ってからは特に)
現状のやり方は、ボタンにマクロを仕込んで、誰でも使える様にしております。
将来、エクセルのバージョンが変わったりして、プログラムの修正が必要になった場合、
よく知っている人でないとメンテできなくなることを危惧しております。
ネットでIndirect関数を知り、いろいろテストしてみましたが、「閉じたブックに対しては使えない」
事が判り、今回の質問をさせていただきました。
マクロなしでは無理なのでしょうか？　もし、可能性のある情報がありましたら、お教えください。
返信、ありがとうございました。

お礼日時：2020/05/23 22:05