計算式内の参照先ファイルの変更

質問

お世話になります。

現在、仕事で使うPCに、自動的に
[All20120105.xls]
という名前のエクセルファイルが作られるように設定されています。
（20120105←この部分はエクセルファイル作成日）
このエクセルファイルを使って在庫管理をしている者です。

在庫管理を容易にするために、
別のエクセルファイル（[在庫管理.xls]）を自分で作っておき、
[All～]内のデータを参照しているのですが、
='\\○○○\●●\[All20120105.xls]◎◎'!B2
このような計算式を[在庫管理.xls]に作ってあります。

お聞きしたいことは、
計算式内にある参照先ファイル名を当日のファイル名に置換する関数があるのかどうかです。
（2012/1/6に[在庫管理.xls]を開くと、計算式内の[All20120105.xls]◎◎の部分が
[All20120106.ｘｌｓ]◎◎に変更されるように。）

置換機能を用いて、計算式内の文字列も置換出来るのは知っており、
今回の問題も自分一人がこのエクセルを使うのであれば解決できます。
しかし、どうしても一手間かかってしまうので、
自分以外の人が、そのエクセルを使うときに戸惑ってしまいます。

SUBSTITUTE関数では計算式内の文字列は置換できないのか、出来なかったように思います。
マクロに関してはほとんど知識ありません。

ご教示のほどよろしくお願い致します。

回答No.8

keithin

アナタのパソコンのデスクトップのパスが判りませんので，試験用にＣドライブの直下にtestフォルダを作成してデータをコピーしていれておき，

Sub Auto_Open()
　Dim a As Variant
　Dim i As Long
　a = ActiveWorkbook.LinkSources(xlExcelLinks)
　For i = 1 To UBound(a)
　If a(i) Like "*All*.xls" Then
　　ActiveWorkbook.ChangeLink Name:=a(i), newname:="C:\test\ALL" & Format(Date, "yyyymmdd") & ".xls", Type:=xlLinkTypeExcelLinks
　　Exit Sub
　End If
　Next i
End Sub

のようにマクロを修正して実施してみます。
これでOKならネットワーク越しのアクセスに障害がある事が判りますし，これもダメならアナタのエクセルの設定の問題と思われます。

また，上手く行かないときは回答を貰ってアナタが「実際に動かしたそのマクロ」を，キチンと情報として返してください。
併せてエクセルを起動してから一連の手順の中で#REFの結果が現れるまでに，「どんなダイアログが実は出ていました」とか「どんな警告が現れるのでどうしています」みたいな具体的な目に見えるエクセルの様子を，漏れなくしっかり情報提供してください。

回答No.7

mar00

>オプションをクリックして有効に変えてからいじってますが、
これも関係あるのでしょうか。

2007を使った事がないので自信はないのですが、調べた限りでは
オプションをクリックして有効にすればリンクは更新されるようですし
関係ないとは思うのですが。

確率的には低いと思いますが
http://www.excel-jiten-rbn.net/formula-rbn/update_link_sink.html
の一番下の注の部分を試してみてはどうでしょうか。

お礼

ですよね。そう思い特にいじってなかったのですが…

今やってみましたが、残念ながら結果は同じでした。
ありがとうございます。

回答No.6

keithin

Ａｌｌの件はすみませんでした。
回答したマクロを動かして，仮に計算結果が#REFになっていたとしても，セルの数式自体は間違い無く正常に書き換わっている事をまず確認してください。

手元では回答したマクロを使って，All年月日.xlsを開いておかなくても，勿論ネットワーク越しでも，リンクが正常に付け変わる（#REFにはならない）事を確認しています。
また他の方から寄せられた置換を使ったマクロのアプローチでも，同様に「開いてからでないと出来ない」現象が起きているという事は，エクセルの問題と言うよりもアナタのパソコンがファイルの保存先に正しく接続できていない障害があるのかもしれません。

「￥￥コンピュータ名」の部分が問題かも知れません。
ネットワークドライブで指定していた場合は，いちどネットワークドライブを解除し，マイネットワークから目的のコンピュータにつないでリンク元を参照する式に書き直し，それに合わせてマクロを修正してみてください。

そもそも「手動操作で置換すれば出来る事は知っています」という元のご相談のお話でしたが，「目的のブックを閉じたまま実際に手動操作で置換操作を行い，それで正常にリンクが付け変わった」かどうかは確認をされたのでしょうか？

手動操作で正常動作を確認できたのでしたら，それを新しいマクロの記録にとって工夫して作成してみた方が簡単に結果を出せるかも？しれません。

お礼

詳しく有難うございます。

補足

＞回答したマクロを動かして，仮に計算結果が#REFになっていたとしても，セルの数式自体は間違い無く正常に書き換わっている事をまず確認してください。

正常に書き換わってます。

＞ネットワークドライブで指定していた場合は，いちどネットワークドライブを解除し，マイネットワークから目的のコンピュータにつないでリンク元を参照する式に書き直し，それに合わせてマクロを修正してみてください。

この部分が私の知識では理解できませんでしたが、
現在使用しているＰＣは個人のＰＣではなく会社のＰＣですので、
ネットワークドライブ（？）で指定しているのかもしれません。

マイネットワークを開くとローカルネットワークのところに、
店舗内の別パソコンとの共有フォルダ（？）と
他店舗のパソコンとの共有フォルダ（？）があり、
店舗内共有部分に在庫管理エクセルなどのフォルダを作ってました。
一応デスクトップにフォルダを移動させて、修正しましたが、結果は同じです。

＞ネットワークドライブを解除
これができるのかどうか…。会社からいろいろ制限かかってるかもしれません。

＞「目的のブックを閉じたまま実際に手動操作で置換操作を行い，それで正常にリンクが付け変わった」かどうか
ブックを閉じたままではできませんでした。
無意識に[All~］のbookをひらいた状態でCtrl+hで置換作業をしていたみたいです。

回答No.5

mar00

ANo.4です。
>ただやはりリンク先bookは開いておく必要があるみたいですね。
当方Excel2003を使用していますが、all○○.xlsと同じフォルダから
在庫管理.xlsを開くだけでできる事を確認しています。
オプションの設定によるものだと思うのですが。
違っていたら、すいません。

お礼

ありがとうございました。

補足

当方Excel2007です。
お二方の意見を聞いているとどうもそのような感じみたいですが、
オプションのどの設定をいじったらいいのかわかりません。
在庫管理.xlsmを開くことで、
セキュリティの警告が出て「リンクの自動更新が無効にされました」
と表示されます。

これはマクロを組む前から毎回表示されており、（VLOOKUPで他のbookとリンクしています）
オプションをクリックして有効に変えてからいじってますが、
これも関係あるのでしょうか。

回答No.4

mar00

'リンクしているブックが1つという前提ですが
Sub AUTO_OPEN()

Dim LinkFLName As String

LinkFLName = "all" & Format(Date, "yyyymmdd") & ".xls"

Cells.Replace What:="all*.xls", Replacement:=LinkFLName, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End Sub
'リンクしているall○○.xlsという所をすべて置換えします。

お礼

ありがとうございます。
リンクしている[All~]がついてるbookはひとつだけです。
コンピュータ名が不要な分、こちらの方が汎用性高いみたいですね。

ただやはりリンク先bookは開いておく必要があるみたいですね。
開く順番だけ指示すればよくなったので、この方法でいこうかと思います。

回答No.3

keithin

閉じたブックに対する参照を数式で可変にする方法はありません。（実際にやってみると判りますが，INDIRECT関数を使ってもできません）

手を使わない前提で考えるなら，マクロに頼るしかありません。

手順：
ブックを開く
ALT+F11を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

Sub Auto_Open()
　Dim a As Variant
　Dim i As Long
　a = ActiveWorkbook.LinkSources(xlExcelLinks)
　For i = 1 To UBound(a)
　If a(i) Like "*ALL*.xls" Then
　　ActiveWorkbook.ChangeLink Name:=a(i), newname:="\\コンピュータ名\フォルダ名\ALL" & Format(Date, "yyyymmdd") & ".xls", Type:=xlLinkTypeExcelLinks
　　Exit Sub
　End If
　Next i
End Sub

「\\コンピュータ名\フォルダ名\」の部分を具体的な正しい内容に書き換え，ファイルメニューで終了してエクセルに戻る
ブックを保存し，閉じて開き直す。
マクロが自動でリンク元を書き換えてくれる。

＃具体的なマクロの書きぶり，新しいマクロの記録で「編集メニューからリンクの編集で新しい参照元にリンクを付け替える」をマクロに録って調べてみると，参考にできます

＃別のアプローチ
参照元のブック名が毎回「違う」から問題になります。

＞仕事で使うPCに、自動的に
＞[All20120105.xls]
＞という名前のエクセルファイルが作られるように設定されています

こちらに仕込みを入れて，自動的にAll20120105.xlsというブックと「同時に同じ内容で」例えばALL.xlsというブックを（上書きで）保存するように仕込んでおけば，作業中のブックは常に「ALL.xls」を参照していればよいだけのお話になります。

お礼

詳しくありがとうございます。
指示通りコピーペーストしてマクロを組んでみたのですが、
うまいこと今日の日付に変わってくれません。
在庫管理のエクセルを作成した日付のまま表示されてしまいます。
もちろんマクロ有効エクセルブックで登録しています。

＞「\\コンピュータ名\フォルダ名\」の部分を具体的な正しい内容に書き換え

フォルダ名に日本語が入っていたのでそれがいけないのかと思い、別フォルダを作成。
アルファベットのみのフォルダに変更しましたが、ダメでした。

＞ActiveWorkbook.ChangeLink Name:=a(i), newname:="\\コンピュータ名\フォルダ名\ALL" & ＞Format(Date, "yyyymmdd") & ".xls", Type:=xlLinkTypeExcelLinks

コピーペーストするとバックスラッシュが￥（半角）になりますが、
それは問題ないですよね？

＞＃別のアプローチ
こちらにすごく惹かれましたが、
（たぶん）保護されているアプリケーションを起動することで
自動生成されるようになってるので、仕込みをいれることは難しいですよね…？？

補足

1日経って、気付いたことがあり、入力を変えたところいけました♪
excellのファイル名がAll~だったのに対し、
ご教示頂いたのがALL~（すべて大文字）だったためうまくいってなかったようです。

ただ、やはりAll~ファイルを開かないと連動してくれないようで#REF!が表示されてしまいます。
やはりbookを開かずに…というのは難しいですかねぇ。

回答No.2

KURUMITO

本日よりも1日前のファイルでしたら次のような式になりますね。

=INDIRECT("\\○○○\●●\[All"&TEXT(TODAY()-1,"yyyymmdd")&".xls]◎◎!B"&ROW(B2))

お礼

ありがとうございます。
基本的には当日の作業なので大丈夫です。

回答No.1

KURUMITO

例えば

='\\○○○\●●\[All20120105.xls]◎◎'!B2

上の式を次のように変更します。

=INDIRECT("\\○○○\●●\[All"&TEXT(TODAY(),"yyyymmdd")&".xls]◎◎!B"&ROW(B2))

これで本日の日付のファイルのデータが表示されます。

お礼

No.3様が仰っているように、bookを開いている状態であれば、この式で大丈夫なようでしたが、
bookが閉じている状態では出来ないみたいでした。。
出来れば参照先bookは開かずに作業がしたいので…。

回答ありがとうございました。