VBAでイベント処理に複数回入ってしまうのを防ぎたい

質問

こんにちは。
マクロのエクセルファイルを操作し、セーブ時にCSV形式でテクストファイルに書くことを実現しようと思っています。
VBAでイベント処理"BeforeSave"で以下のように書くと、初めに書かれた"test.txt"が同じ処理を行おうとしているようで、そこでも書き込みの処理を行おうとする→無限に書き込みが発生？→エラーの表示が出てきます。これはどういう風に対処すればよろしいのでしょうか？

Workbook/BeforeSaveイベント処理：
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim file_curr As String
Dim msg As String

file_curr = ThisWorkbook.Path & "\test.txt"
If Dir(file_curr) = "" Then
ActiveWorkbook.SaveAs _
Filename:=file_curr, _
FileFormat:=xlCSV, Local:=True
msg = "saved"
Else
msg = "file exist, not saved"
End If
MsgBox msg
End Sub

回答No.2

Wendy02 · ベストアンサー

>、.xlsmから.txtを記録するとともに

今は、Excel 2007 では試していません。本来、別ファイルの保存のためなら、Application.EnableEvents = False は、必要ないはずです。以下は、プロシージャを二つに分ける必要はないけれども、加工しやすさのために分けました。

'-------------------------------------------

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
　　Dim dir_Curr As String
　　Dim file_Curr As String
　　Dim flg As Boolean
　　dir_Curr = ThisWorkbook.Path
　　file_Curr = dir_Curr & "\test.txt"
　　On Error GoTo ErrHandler
　　 If Dir(file_Curr) = "" Then
　　　　Call CSVFileSaving(file_Curr)
　　　　flg = True
　　 End If
ErrHandler:
　　If Err.Number > 0 Then
　　　　MsgBox Err.Number & ": " & Err.Description
　　ElseIf flg Then
　　　　MsgBox "Saved safely", vbInformation
　　Else
　　　　MsgBox "Cancelled saving", vbExclamation
　　End If
End Sub

Sub CSVFileSaving(fileName As String)
　ActiveSheet.Copy
　With ActiveWorkbook
　　 .SaveAs fileName:=fileName, FileFormat:=xlCSV, Local:=True ' 'xlTextWindows
　　 .Close False
　End With
End Sub

お礼

Wendy02さま。
美しくやりたいことが実現できました。また、勉強になりました。御礼申し上げます。

回答No.1

keithin

イベントの連鎖は，イベントの中で行おうとしている保存（saveas）の動作がまたイベントをトリガすることで発生します。
もう一つの見落としは，before_saveイベントは「保存する前に起動するアクション」です。つまり無事イベントプロシジャを抜けると，今度は本来の「本番の保存の動作」が続いて起こります。

従って対処として，
１．まず少なくとも　cancel = true　を忘れず書いて，「本番の保存」はキャンセルする

２．イベント内部でのトリガアクションに先立って，イベントを起動させないよう抑制する

private sub workbook_beforesave(…
　dim …
　(中略)
　application.enableevents = false
　activeworkbook.saveas　何某
　application.enableevents = true
　(後略)
end sub

若しくは
３．イベントの中で「保存しない」で，何か別の対処を探る？

補足

keithinさま。
ありがとうございます。
１番と２番の方針をとって全ては解決…のように見えました。
しかし、.xlsmから.txtを記録するとともに、普通に.xlsmファイルも
セーブしたかったので、Cancel = Trueをコメントアウトすると、同じく連鎖が出てしまいました。
イベント内部でのトリガーアクションはおさえたので、「本番の保存」も一度きりのはずなのに、なぜ連鎖が出るのでしょうか？対処方法をご教授いただけると幸いです。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim dir_curr As String
Dim file_curr As String
Dim msg As String

dir_curr = ThisWorkbook.Path
file_curr = ThisWorkbook.Path & "\test.txt"

' Cancel = True '　←これを実行すると問題ないのですが…

Application.EnableEvents = False
ActiveWorkbook.SaveAs _
Filename:=file_curr, _
FileFormat:=xlCSV, Local:=True
Application.EnableEvents = True
msg = "saved"
MsgBox msg
End Sub