Excelマクロ ファイル名が変わるとエラーになる

Excelマクロの初心者です。
マクロで
「Application.Run "専任タイムカード集計ファイル.xlsm!標準時"」と書いた場合
ファイル名が変わると常にエラーが出ます。ファイル名が変わっても大丈夫に書き換えるには
どのようにしたらよろしいでしょうか。

質問者からの補足コメント

• マクロにパスワードがかかっているとか関係しますか。

  No.9の回答に寄せられた補足コメントです。 補足日時：2020/06/22 01:24

No.8 ベストアンサー 回答者： Uh-huh 回答日時： 2020/06/22 00:21

Sub aaa()

Const path As String = "C:\testes\" '各事務所のファイルを置くフォルダのパス
pasu = Dir(path & "*.xls") '上で指定したフォルダ内に入っているxls(xlsm、xlsx全て含む)ファイルを
Do While pasu <> "" '探して
cnt = cnt + 1
Cells(cnt, 1) = path & pasu 'A1から純につっこむ
pasu = Dir() 'ファイルが無くなるまで繰り返す
Loop
For Each Rng In Range("A1", Cells(Rows.Count, 1).End(xlUp)) '行がいくつあるかわからないのでA1から下にかけて何かしら値が入っているセルまで範囲指定
Application.Run ("'" & Rng & "'!Sheet1.m1") 'Sheet1.m1＝実行したいマクロ名
On Error Resume Next 'エラーを弾く
Next
End Sub

各行の意味は適当すぎる説明ですがこんな感じです。
内容としては、
各事業所のマクロを実行するだけのAというエクセルファイル(以下A)を作成し上記のマクロを組んでおき、
各事業所に配るエクセルファイルabcd…(以下abcd)の中にあるマクロを呼び出すのですが、
abcdは毎回ファイル名が変わるとのことなので、そのファイル名をまず取得してA列上に入れます(ただしabcdを置くフォルダはバラバラではそもそも探せないので、場所を決めなくてはいけません、上記のマクロではC:\testes\配下にあることになっています)
その後に、取得したファイル名からマクロを実行します。原本があるとのことなのでマクロ名は同じだろうということでそのまま構文に入れています(上記ではマクロ名Sheet1.m1)。
これで元の質問の答えになるはずです！
が、ただちょっと気になるのは、マクロってブックを指定しない限りアクティブブックに対して有効になるので、既に組まれているマクロがAに対して実行されないように組まれているかな？ということですが…でもまあ元々フォルダ名さえ指定できれば上手く動いていたマクロなので大丈夫なんだろうと思っています…！

ちなみに補足読んでですが、abcdのファイルは毎日みんな出勤するたびに開くんですよね？
自動実行にしたらまずそうですかね？なんなら変なところいじられたくないとかならユーザーフォームとかでブック自体は見えなくしてユーザーフォームだけ見せてクリックだけさせるとかもできるんですが(クリックしたら打刻～マクロ実行し保存して閉じてエクセルアプリケーションをプロセス終了するまでを一連のマクロで組むとか)
そしたら毎週日曜に更新しなくてもいいはずなんですけどね！環境が見えないので提案が難しいですね…

この回答へのお礼

回答ありがとうございます。う〜ん、私の知識のなさが問題です。書いていただいたマクロあと一歩のところでつまずいてしまいました。
「Application.Run ("'" & Rng & "'!Sheet1.m1") 'Sheet1.m1＝実行したいマクロ名」の部分に何を入れていいのかがわかりません。Ａ列上にファイル名を取得することはできています。このマクロを実行するために作ったファイル名も取得されています。

お礼日時：2020/06/22 01:00

No.11 回答者： Uh-huh 回答日時： 2020/06/22 01:58

エラーが出ていないならSheetモジュールの件も忘れて大丈夫ですよ。

エラーになっていないなら原因が他にあるとしか考えられないので、パスワード外して実行してみてください。

この回答へのお礼

パスワード外しても変わらないです。Uh-huhさんのおかげでほとんど完成に近いところまで作成することができました。本当にありがとうございます。今後もエクセルマクロについていろいろ教えていただきたいです。会社にあなたの様な方がいれば業務の効率化がもっと進むと思いますが、現状は尋ねると余計な仕事を増やすなと言わんばかりです。またこのような機会がありましたら是非教えてください。

お礼日時：2020/06/22 02:06

No.10 回答者： Uh-huh 回答日時： 2020/06/22 01:40

多分エラーが出ていますね…

一度on error resume next を省いて見てもらえますか？即エラーが出ますかね？
一応作成後試用したので(単純なmsgBox出すだけのマクロを2つとマクロなしファイル1つの合計3ファイル保存したフォルダを対象に)、使えたんですが…。
それと、質問者さんが実行したいマクロをSheetモジュールに入れることはできますか？
ちなみにパスがかかっていることが原因かは不明です、私はパスをかけずに試したので。一回外して上手くいくなら原因ですね。

この回答へのお礼

on error resume next を省いてもエラーは出ません。普通に実行されたような感じになります。対象のファイルが開いた状態で終了になりますが、マクロが書き換えられた形跡はありません。
Sheetモジュールとは何でしょうか。

お礼日時：2020/06/22 01:54

No.9 回答者： Uh-huh 回答日時： 2020/06/22 01:04

Application.Run ("'" & Rng & "'!Sheet1.m1") 'Sheet1.m1＝実行したいマクロ名

の'Sheet1.m1＝実行したいマクロ名はただのコメントですからね！？一応コピペしたら緑色の文字になるので気づくとは思いますが…

Application.Run ("'" & Rng & "'!Sheet1.m1")
これはあくまで例なので、Sheet1.m1がマクロ名になっていますが、
もし標準時というマクロ名なら、
Application.Run ("'" & Rng & "'!標準時")
になります

この回答へのお礼

エラーが出なくなり一瞬喜んだのですが、指定したファイルがすべて開いた状態で終わり、その他の変化がありません。このマクロは各ファイルの「Application.Run "専任タイムカード集計ファイル.xlsm!標準時"」の部分を各ファイルのファイル名に変換してくれるマクロなんですよね。新しいファイル名は上記の専任の前に事務所所在地、例えば「大阪専任タイムカード集計ファイル」のように変わっているだけです。

お礼日時：2020/06/22 01:23

No.7 回答者： Uh-huh 回答日時： 2020/06/21 22:16

ある程度どうしたらいいか想像できたので具体的な構文後ほどきちんと回答いたします

イメージとしてはNo3の回答に近くなるかと思います。
ちなみに職員にお願いするとどうせ漏れるからNGなのか、不正に関わるからNGなのかどちらでしょう？
また、どのくらいの頻度で実行しますか？

この回答へのお礼

ありがとうございます。どのように返事すればうまく伝わるか難しいですが、タイムカードとしてExcelファイルを使うので職員は出勤する度にこのファイルを使います(打刻します)。毎月5日〆ですので、〆が来る度に次の月の原本を作成し職員に回す予定です。作成とは言っても月日曜日を更新するだけですので簡単です。

お礼日時：2020/06/21 23:01

No.6 回答者： Uh-huh 回答日時： 2020/06/21 06:06

No.3の回答は詳細読む前に打ったものなので相当荒い内容です、申し訳ないです…

No.5 回答者： Uh-huh 回答日時： 2020/06/21 06:04

＞ファイル名の変更と同時にマクロの行のファイル名も変更になると1番良いです。

分かりました！詳細ありがとうございます！
原本ファイルがあるとのことなので、その原本ファイルにマクロ構文が入っているということで間違いないですか？
その原本をコピーして、毎回ファイル名は変わるということですかね？
ちなみに現在開いているブックのマクロを呼び出す際ファイル名取得は必須じゃないので、もし仮に原本ファイルにマクロ構文が入っているならわざわざ他ブックから呼び出すやり方でマクロ実行しなくても良くて、ファイル名取得する必要もないのですが、なにかやむを得ない理由があると思って良いでしょうか？
一応私ならこうするというやり方ですが、原本ファイルにランダムで変わるファイル名を取得するために任意のセル、例えばA1にCELL関数を入れ自ブックのファイル名含むフルパスを取得できるようにします。そしていじられないように適当に非表示にしておきます。
その上でApplication.run を実行する際構文ではファイル名を指定せずに A1セル値を引っ張るように書きます。そうすることで毎回変わるファイル名はA1に反映するので変わっても大丈夫です。

この回答へのお礼

本当にありがとうございます。
原本ファイルにマクロが入っています。
その原本をコピーして事務所ごとにファイル名を変えようと思っています。

原本ファイルが開いていたら、ファイル名を変えたファイルのマクロも使えるんですよね。これを知らなくて自分のPCで作成したファイルを名前を変更して別のPCで開いた時にマクロ実行するとエラーが出て驚きました。でもこれは名前を変えたファイルを使う時に常に原本ファイルを開いておかなければいけないということですよね。そうだとしたらそれをタイムカードに打刻をする度に職員にお願いするのは良くないと・・・。こんな説明で理解していただけているのでしょうか。
ランダムで変わるファイル名を取得するというのは何となく理解出来ますがこのマクロを書くとなると私にはハードルが高いです。

お礼日時：2020/06/21 06:22

No.4 回答者： Uh-huh 回答日時： 2020/06/21 05:45

読み返したら文章クソだったので意味わからない箇所は言ってください…

No.3 回答者： Uh-huh 回答日時： 2020/06/21 05:42

補足ですが、

もし仮に、全然関係ないファイル名に誰かに変えられる場合(そんなことあるか不明ですが)で、ただほかブックのマクロが必要で実行したいだけであれば、
その実行したいマクロのあるブックすべて特定フォルダに移し、
Application.runで各ブックのマクロを実行する側のブックではその特定フォルダのファイル名を全取得するようにし、取得したファイル名全てに実行予定のマクロ呼び出し、エラーは全てOn Error Resume Nextで弾くということもできます。
例えばtest2.xlsmに実際はマクロaがあるがtest1,2,3いずれに入っているか不明な場合、なので取り敢えず全部に実行命令する感じです。
荒っぽいですが。

この回答へのお礼

本当に早くて丁寧な返信ありがとうございます。何となくイメージ出来るのですが初心者の私には複雑になりすぎている気がします。小さい会社なのですがシステム担当の方に相談しても良い反応をしてもらえず困っていましたがUh-huhさんのお陰でほぼ完成に近づいて来ています。直接依頼したいぐらいです。本当にありがとうございます。

お礼日時：2020/06/21 05:55

No.2 回答者： Uh-huh 回答日時： 2020/06/21 05:12

No1の者です。

どういう作業内容かが読め無いので回答に困っています。
作業内容によってもやり方が変わりますし条件によっても何ができて何ができるのか、提案ができるのですが、今提案のしようが無いので、作業内容の詳細を記載されたほうがいいですね。
なんのためにファイル名を変えているか、ファイル名はパターン化されているのか(〇〇年〇月.xlsmというふうに)、それとも勝手に誰かに全然関係ないファイル名に変えるのか、一体なんの為のApplication.Runなのか、同時に複数ファイルを開いているだけなのか、別の作業なのか…全然分からないので勝手に必要ないと決めつけずにある程度質問するときは詳細を伝えてください。

この回答へのお礼

お返事ありがとうございます。初心者でマクロの何を伝えて良いのか分かっておらず申し訳ありません。
Excelで勤怠管理を行おうと考えています。打刻をして保護をかけてある程度不正などは起こらないように作成し、完成に近づいて来ました。
事務所ごとに1ファイル作成し、原本のタイムカードにあたるシートをコピーして職員の方には使っていただこうと考えています。
ですので、事務所ごとにファイルを作成する時に当然ファイルの名前が変わってしまい、マクロの行にファイル名が入っているとその度にエラーがでるので訂正する作業が出て来る事に気づきました。この作業を出来るだけ簡単に行えれば良いと考えています。
ファイル名は規則性は出てくると思います。ファイル名の変更と同時にマクロの行のファイル名も変更になると1番良いです。
こんな説明で足りているでしょうか。よろしくお願いします。

お礼日時：2020/06/21 05:34