[[20220617221909]]　『エクセルが更新されたら通知するVBA』（SUNEO）　ページの最後に飛ぶ

『エクセルが更新されたら通知するVBA』（SUNEO）

こんばんは。

件名にあるようにエクセルが更新されたら通知するVBAはどのように書けばよいでしょうか？

狙いとしてはPCの電源を入れたらマクロが起動して対象のファイルの監視をはじめるのが狙いです。

ネットなどを参考に書いたコードは以下のようになります。
Sub IntervalAction()

    Dim myInterval As Long
    myInterval = 2
    Call SearchFolder
    Application.OnTime DateAdd("s", myInterval, Time), _
      "IntervalAction"
      End Sub
Sub SearchFolder()
    Dim myFname As String

    myFname = Dir("I:\小集団資料\*.xlsx")
    Do While myFname <> ""
        If FileDateTime(myFname) > DateAdd("s", -10, Now) Then
            MsgBox myFname & "は最新ファイルです！"
            Exit Sub
        End If
        myFname = Dir()
    Loop
End Sub
単独で実行すると
If FileDateTime(myFname) > DateAdd("s", -10, Now) Then
でエラーが出て、ファイルが見つかりませんと出ます。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

更新イベント？
再計算か書き込みぐらいしかないと思います？
（簡単が1番） 2022/06/17(金) 22:48

消去も書き込みと同じ扱いとなります。
（簡単が1番） 2022/06/17(金) 22:53

myFname = Dir("I:\小集団資料\*.xlsx")
で返るmyFnameが何かを確認されましたか？（デバッグの基本!)
ファイル名だけじゃないですか？
フォルダを指定しないと、FileDateTime(myFname)は、
カレントフォルダ内にあるそのファイルを見に行きますよ。

（γ） 2022/06/17(金) 23:04

（γ）さん。
ご指摘ありがとうございます。
今確認した所、仰った通りファイル名だけでした。
（SUNEO） 2022/06/17(金) 23:11

VBAではなくVBSのコードもネットを参照にしました。
'---　ファイル更新監視　---
'[File Data]

 FullPath_01 = "I:\小集団資料\小集団.xlsx"
 Date_01 = "2022/03/01 15:28:06"
'--------------------------
On Error Resume Next
Set fso = CreateObject("scripting.filesystemobject")
Set objFile = fso.GetFile(FullPath_01)
 Date_02 = objFile.DateLastModified
On Error GoTo 0
 If Date_02 = "" Then WScript.Quit
 ForReading = 1
 ForWriteing = 2
 Set f = fso.OpenTextFile(WScript.ScriptFullName, ForReading)
 ReadAllTextFile = f.ReadAll
 f.Close
 f3 = Split(ReadAllTextFile, Chr(13) & Chr(10))
 For i = 0 To UBound(f3) - 2
   If f3(i) = "'[File Data]" Then
     If Mid(Trim(f3(i + 2)), 1, 9) = "Date_01 =" Then
       f3(i + 2) = " Date_01 = """ & Date_02 & """"
     End If
   End If
 Next
 Set f1 = fso.OpenTextFile(WScript.ScriptFullName, ForWriteing, True)
 MyString = Join(f3, Chr(13) & Chr(10))
 f1.write MyString
 f1.Close

 If Trim(Date_01) <> Trim(Date_02) Then
  WScript.echo "ファイルが更新されました" & vbCrLf & vbCrLf & _
  FullPath_01 & vbCrLf & Date_01 & " → " & Date_02
 End If
これを実行すると何も反応しませんでした。
試しに対象ファイルを更新してもだめでした。
（SUNEO） 2022/06/18(土) 00:25

最初の話は、
If FileDateTime("I:\小集団資料\" & myFname) > DateAdd("s", -10, Now) Then
などとフォルダ名を補えばよいだけではないんですか？
二つ目の話は読んでいません。何か手違いがあるんじゃないですか？

それほど頻繁にファイル更新を監視するといったことの必要性が理解できません。
私見では、表計算ソフトにはもっと他にやるべきことがあるのでは、
というのが率直な感想です。
上記のコメントも技術的な面でのコメントであり、
必ずしもそれを推奨するわけでもありません。
# 私のコメントはここまでです。

（γ） 2022/06/18(土) 07:47

更新前後はどうやって判断しているの。
上記のコードは常に最新フアァイルと表示されるように思うけど。
監視するのだったらそのファイルのログを取った方がいいのでは。

（*） 2022/06/18(土) 19:35

スケジューラでVBSを定期的に繰り返し実行するということですね？
・対象ブックの更新時刻を、自身のVBSファイルに書きつけておき、
・毎回毎回、前回に書きつけておいたものと比較するわけですね。

実行対象のVBSファイルそのものに、対象ファイルの更新時刻を書きこむ、
というのが少しトリッキーな感じはします。

# 今のままでも動作すると思いますが、
# 別にもうひとつtxtファイルを用意しておいて、
# そこに対象ブックの更新時刻を書きつけておいてもいいわけですね。
# そのほうが簡単じゃないかと思います。

（γ） 2022/06/18(土) 22:50

（*）さん
更新前後は
ファイルの更新日時を比較して
違っていたらメッセージを表示する内容です。

（γ）さん
ちなみに、上記のvbsはテキストファイルしかできなくて困っています。
xlsmに対応できたらいいなと思っています。

（SUNEO） 2022/06/18(土) 22:53

| ちなみに、上記のvbsはテキストファイルしかできなくて困っています。
| xlsmに対応できたらいいなと思っています。
理解できません。できるはずですよ。

DateLastModifiedプロパティは別にテキストファイルに限定されません。
ファイルの更新時の日付＋時刻を持つだけですから。
下記を参照ください。
http://officetanaka.net/excel/vba/filesystemobject/file04.htm

ネットにあるものをそのまま使うのではなく、
コードの意味を理解したほうがよいと思います。
（γ） 2022/06/19(日) 06:20

＞ファイルの更新日時を比較して
その更新日時はどうやって比較しているのですか。
（*） 2022/06/19(日) 07:52

＞その更新日時はどうやって比較しているのですか。

私がコメントする筋合いでもないですが、
＞・対象ブックの更新時刻を、自身のVBSファイルに書きつけておき、
＞・毎回毎回、前回に書きつけておいたものと比較するわけですね。
が回答になりますか？

処理ごとに、対象ファイルの更新日時刻を

 Date_01 = "2022/03/01 15:28:06"
の形で自分自身(VBSファイル）に書きつけておくのです。

そうすれば、VBSを読み込めば、
前回観測時に記録したその時点での更新年月日が入手できるわけで、
それと現時点のDate_02と比較するだけです。

いや、そんなことはわかっています、ということでしたら失礼。
質問の意味が別にあるのでしたら、スキップ下さい。
（γ） 2022/06/19(日) 18:11

vbsでワイルドカードは使えますでしょうか？

 FullPath_01 = "I:\小集団資料\小集団.xlsx"のファイル名の箇所に
ワイルドカードを追加するにはどうしたらよいでしょうか。
（SUNEO） 2022/06/20(月) 12:07

ワイルドカードを使うってどういうことでしょうか。
どれか一つでも更新されたら警告を出すということですか？
そのフォルダの配下の全ファイルをfilesで取得し、
各ファイルについて更新時刻の新旧比較を
することになるでしょうね。

もしくはファイルの個数だけVBSファイルを実行させるかです。

Excelでそうしたことを実行する必要性を説明してもらうと
別法の提案があるかも知れません。
（γ） 2022/06/20(月) 12:54

例えばxlsxではなくxlsmに変更しておき、
保存したときにマクロでテキストファイルを書き出すような
作りにしておく方法があるでしょう。
すでに指摘があったログファイルの活用ということです。

（γ） 2022/06/20(月) 13:01

フォルダの中にはファイルは一つしかないです。
しかし先頭に日付が加わります。
その日付は変わったりします。
だからワイルドカードを使いたいと思いました。

（SUNEO） 2022/06/20(月) 13:07

 やりたいことはなんとなく分かるんですが（なんとなくしか分かりませんが）
 提示されているコードがちょっとちぐはくな感じで、混乱するというか。

 まず、やりたい事、やり方の方針をある程度固めた方がよいのではないかと思います。

 (1) 処理系は？
　　　 Excel VBAですか？　VBScriptですか？
 (2) 一定間隔で実行する方法はどうしますか？
　　　 Windowsのタスクスケジューラですか？ ←VBSならこれ一択
　　　 VBAのOnTimeステートメント ← VBAならOnTimeとタスクスケジューラの2択？
 (3) 監視対象のファイルのファイル名と更新時刻の一覧をどうやって保持するか？
       データ保持用のエクセルファイルを用意して書き込む
　　　 データ保持用のテキストファイルを用意して書き込む
       保持しない（前回実行時の時刻より更新時刻が新しいファイルをピックアップする）

 まずはこのあたりを
（´・ω・`） 2022/06/20(月) 13:42

ああ、そうか。VBScriptにはDir関数はなかったのでしたね。
ならFSOのFilesプロパティですね。
http://officetanaka.net/excel/vba/filesystemobject/folder06.htm
を参照ください。

（γ） 2022/06/20(月) 14:15

（´・ω・`）さん
遅れてすみません。
質問に答えます。

 (1) 処理系は？
　　　 Excel VBAですか？　VBScriptですか？
→VBScriptです
 (2) 一定間隔で実行する方法はどうしますか？
　　　 Windowsのタスクスケジューラですか？ ←VBSならこれ一択
　　　 VBAのOnTimeステートメント ← VBAならOnTimeとタスクスケジューラの2択？
→Windowsのタスクスケジューラです
 (3) 監視対象のファイルのファイル名と更新時刻の一覧をどうやって保持するか？
       データ保持用のエクセルファイルを用意して書き込む
　　　 データ保持用のテキストファイルを用意して書き込む
       保持しない（前回実行時の時刻より更新時刻が新しいファイルをピックアップする）
→更新時間だけ、現状のvbsでは保持しています。

フォルダの中にはファイルは１つしかなく、ファイル名の先頭に日付(例0620)などが中身を更新したら入ります(手動です)

それを踏まえ、現状のコードは

　ファイル更新監視　--

 Date_01 = "2022/06/19 12:46:52"
'--------------------------
On Error Resume Next
Set objFile = fso.GetFile(FullPath_01)
 Date_02 = objFile.DateLastModified
On Error GoTo 0
 If Date_02 = "" Then WScript.Quit
 ForReading = 1
 ForWriteing = 2
 Set f = fso.OpenTextFile(WScript.ScriptFullName, ForReading)
 ReadAllTextFile = f.ReadAll
 f.Close
 f3 = Split(ReadAllTextFile, Chr(13) & Chr(10))
 For i = 0 To UBound(f3) - 2
   If f3(i) = "'[File Data]" Then
     If Mid(Trim(f3(i + 2)), 1, 9) = "Date_01 =" Then
       f3(i + 2) = " Date_01 = """ & Date_02 & """"
     End If
   End If
 Next
 Set f1 = fso.OpenTextFile(WScript.ScriptFullName, ForWriteing, True)
 MyString = Join(f3, Chr(13) & Chr(10))
 f1.write MyString
 f1.Close

 If Trim(Date_01) <> Trim(Date_02) Then
  WScript.echo "ファイルが更新されました" & vbCrLf & vbCrLf & _
  FullPath_01 & vbCrLf & Date_01 & " → " & Date_02
 End If
です。

（SUNEO） 2022/06/20(月) 20:10

 Scriptファイルを書き換えるなんてトリッキーなことをやめて
 ファイルスタンプの時刻を保存するだけのテキストファイルにしたほうがいいですよ
（´・ω・`） 2022/06/20(月) 20:18

（´・ω・`）
ファイルスタンプの時刻を保持とはどのような意味でしょうか？
（SUNEO） 2022/06/20(月) 20:29

 '[File Data]
 の次の次の行に
  Date_01 = "2022/06/19 12:46:52"
 と書いてあることが条件になっていますが、
 そこは大丈夫なんですか？

 # 投稿上の雑音のためにそこが不明です。
 # プレビューして投稿内容の確認はしないのですか？

 まあ、それもこれもトリッキーな手法のせいです。
 （´・ω・`）さんから指摘があり、私も指摘していますが、
 更新日時を書きつけるテキストファイルを一つ用意すれば、
 もっと簡単なコードになるんじゃないですか？

 コードの作者は、そのテキストとの一体管理をする目的で、
 VBSそのものに情報を書きつけたようですが、それにこだわる必要ないですし、
 そうした（ログファイルというんですか）テキストファイルを持つ方が
 一般的じゃないかと思います。

（γ） 2022/06/20(月) 20:32

 >ファイルスタンプの時刻を保持とはどのような意味でしょうか？
 えっっと間違いました　ファイルのタイムスタンプです

 ちなみに、今つくってるスクリプトはどこのサイトを参考にしたものでしょうか？
 どんなことしているのか、コードの意味はちゃんとわかってますか？
（´・ω・`） 2022/06/20(月) 20:43

（´・ω・`）さん。（γ）さん
http://vba-sample.sblo.jp/article/181770291.htmlが参考したサイトです。
正直な所コードは理解できていません。
すみません。
（SUNEO） 2022/06/20(月) 20:45

再掲します。

'[File Data]
の次の次の行に
Date_01 = "2022/06/19 12:46:52"
と書いてあることが条件になっていますが、
そこは大丈夫なんですか？

（γ） 2022/06/20(月) 20:49

（γ）さん
おそらく大丈夫じゃないと思います。
同じファイル名を監視し続けるなら大丈夫かと思いますが、
今回みたいにファイル名が変わる可能性があるものに関しては有効ではない気がします。
（SUNEO） 2022/06/20(月) 20:54

アドバイスについてあーだーのこーだの言うのなら監視するのをやめろよ。
自力でどうにかしろ。
そんなことやらないから回答できないけど。

（あほ） 2022/06/20(月) 21:02

いやいや、今あるコードの内容を尋ねているだけなんです。
次に行になっているんじゃないかと聞いただけです。
答えられない質問じゃないと思いますが。

特定のHTMLのタグに変換されてしまっているので
現状が分からないので聞いたわけです。　

（γ） 2022/06/20(月) 21:10

　ファイル更新監視　--

 Date_02 = objFile.DateLastModified
On Error GoTo 0
 If Date_02 = "" Then WScript.Quit
 ForReading = 1
 ForWriteing = 2
 Set f = fso.OpenTextFile(WScript.ScriptFullName, ForReading)
 ReadAllTextFile = f.ReadAll
 f.Close
 f3 = Split(ReadAllTextFile, Chr(13) & Chr(10))
 For i = 0 To UBound(f3) - 2
   If f3(i) = "'[File Data]" Then
     If Mid(Trim(f3(i + 2)), 1, 9) = "Date_01 =" Then
       f3(i + 2) = " Date_01 = """ & Date_02 & """"
     End If
   End If
 Next
 Set f1 = fso.OpenTextFile(WScript.ScriptFullName, ForWriteing, True)
 MyString = Join(f3, Chr(13) & Chr(10))
 f1.write MyString
 f1.Close
 If Trim(Date_01) <> Trim(Date_02) Then
  WScript.echo "ファイルが更新されました" & vbCrLf & vbCrLf & _
  FullPath_01 & vbCrLf & Date_01 & " → " & Date_02
 End If
これでまともにみられますか？
（SUNEO） 2022/06/20(月) 21:17

strPath = "C:\Users\linki\Desktop\サンプル"
Set fso = CreateObject("scripting.FileSystemobject")
Set objFolder = fso.GetFolder(strPath)
For Each objFile In objFolder.Files
FullPath_01 = objFile.Name
Next
Date_01 = "2022/06/19 12:46:52"
On Error Resume Next
Set objFile = fso.GetFile(FullPath_01)
Date_02 = objFile.DateLastModified
On Error GoTo 0
If Date_02 = "" Then WScript.Quit
ForReading = 1
ForWriteing = 2
Set f = fso.OpenTextFile(WScript.ScriptFullName, ForReading)
ReadAllTextFile = f.ReadAll
.Close
f3 = Split(ReadAllTextFile, Chr(13) & Chr(10))
For i = 0 To UBound(f3) - 2
If f3(i) = "'[File Data]" Then
If Mid(Trim(f3(i + 2)), 1, 9) = "Date_01 =" Then
f3(i + 2) = " Date_01 = """ & Date_02 & """"
End If
End If
Next
Set f1 = fso.OpenTextFile(WScript.ScriptFullName, ForWriteing, True)
MyString = Join(f3, Chr(13) & Chr(10))
f1.write MyString
f1.Close
If Trim(Date_01) <> Trim(Date_02) Then
Wcript.echo "ファイルが更新されました" & vbCrLf & vbCrLf & _
FullPath_01 & vbCrLf & Date_01 & " → " & Date_02
End If
（SUNEO） 2022/06/20(月) 21:20

'[File Data]というキーワードがないですし、
'[File Data]の2行先に更新日時がないので全く動作するはずがないです。

（´・ω・`）さんがたぶん改善版を書いてくださるでしょう。（済みません）

私はこれで。

（γ） 2022/06/20(月) 21:28

（γ）さん。
すみません。
だいぶコードが崩れてました。
今までありがとうございました。
自分なりに調べてゆっくりですが調べながらやっていきます。
（SUNEO） 2022/06/20(月) 22:01

 参考サイトのコードを元に改造されてますけど、元のコードを理解しないまま改造なんてできないです
 元のコードをしっかり理解することから始めましょう

 ですが、元のコードは自分自身を書き換えるというテクニカルというかトリッキーなコードなので
 初心者にはちょっとむずかしいコードです
　
 ファイル名と更新時間を保存するファイルを別に持つほうがおすすめです
（´・ω・`） 2022/06/20(月) 22:04

（´・ω・`）さん。
難しいそうですね。
ファイル名と更新時間を保存するファイルを別に持つコードを調べてみます。

（SUNEO） 2022/06/20(月) 22:13

人任せにするのも失礼でしたか。
こんな感じでどうですか？
Log.txtファイルを準備しておいてください。（手作業で）

 == コード例 ===============

 FullPath_01 = "D:\....test"         '■フォルダパスに変更のこと
 logFile = "D:\......\Log.txt"       '■ログファイルのパス名に変更のこと
 '--------------------------
 '対象ファイルの更新時刻を取得(これはテキストファイル限定じゃない）
 'On Error Resume Next
 Set fso = CreateObject("scripting.filesystemobject")
 For each f in  fso.GetFolder(FullPath_01).Files
     Set objFile = f
 Next
 Date_02 = objFile.DateLastModified
 'On Error GoTo 0
 If Date_02 = "" Then WScript.Quit

 ForReading = 1
 ForWriting = 2

 'ログファイルから前回観測の更新時刻を取得
 Set f = fso.OpenTextFile(logFile, ForReading, True)
 Date_01 = f.ReadLine
 f.Close

 '今回観測の更新時刻を書きこみ
 Set f1 = fso.OpenTextFile(logFile, ForWriting, True)
 f1.writeLine Date_02
 f1.Close

 If Trim(Date_01) <> Trim(Date_02) Then
     WScript.echo "ファイルが更新されました" & vbCrLf & vbCrLf & _
                  FullPath_01 & vbCrLf & Date_01 & " → " & Date_02
 End If

（γ） 2022/06/20(月) 22:44

（γ）さん　
親切にありがとうございます。
試した所
Date_01 = f.ReadLinene
にて> 「ファイルの最後を越えた入力をしようとしました」
がでました。

今からネットで調べてみます。
（SUNEO） 2022/06/20(月) 22:58

（γ）さん
度々申し訳ございません。

Logファイルが空だった為、適当な文字(今回は1)を入力したら無事にできました。
（SUNEO） 2022/06/20(月) 23:02

（γ）さん
更新したら無事に通知が来ました。

なにからなにまでわがままに付き合ってもらってありがとうございます。

コード内容をネットなどを参考にして理解していき次につながるようにしていきます。

重ね重ねありがとうございました。
（SUNEO） 2022/06/20(月) 23:30

コメント返信：

[ 一覧(最新更新順) ]

[[20220617221909]] 『エクセルが更新されたら通知するVBA』（SUNEO） ページの最後に飛ぶ

[[20220617221909]]　『エクセルが更新されたら通知するVBA』（SUNEO）　ページの最後に飛ぶ