【Excel VBA】ファイルを保存した時に自動的にマクロを実行する方法

Question

「data_sheet」というシートのD75からW190の範囲を、
「export_sheet」というシートのA2に値で貼り付けるというマクロがあります。
この処理を作業後に必ず実施させたいのですがオペレータは忘れてしまう事があるため、
ファイルを閉じるとき、もしくはファイルを保存した時に自動的に実行させたいです。
(できればファイルを上書き保存した時に実行したい)
どうすればできますでしょうか？

Qchan1962 · Accepted Answer

忘れました　Workbook_BeforeSave
ThisWorkbookモジュールへ　
＃８コードがModule1（標準モジュール）に書かれている場合
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Module1.Make_DataCsv
End Sub

Qchan1962 · Answer

今更ミタイナことを書いてしまったので
参考出力コード
ボタン登録はMake_DataCsv

Sub Make_DataCsv()
    Dim target As Range, r1 As Range
    Dim DataAry As Variant    'セル範囲配列
    Dim endcell As Long    'データ最下位行

With ThisWorkbook.Worksheets("data_sheet")  'シート名明示
        Set target = .UsedRange.Find(What:="■交通費明細", LookAt:=xlWhole)
        If target Is Nothing Then Exit Sub    '見つからなければ、終了
        Set r1 = target.Offset(2)    '２行下
        'ターゲット右隣最終行
        endcell = .Cells(Rows.Count, target.Offset(, 1).Column).End(xlUp).Row
        If endcell < r1.Row Then endcell = r1.Row
        DataAry = .Range(r1, target.Offset(endcell - target.Row, 17))
    End With
    '範囲が確定（限定）の場合、上記With内不要、下記コードで
    'DataAry = ThisWorkbook.Worksheets("data_sheet").Range("D75:W190")

Call writeCSV(DataAry)

End Sub

Sub writeCSV(DataAry As Variant)
    Dim usName As String, tm As String
    Dim wnet As Object
    Dim csvFile As String, fileName As String
    Dim i As Long, j As Long, n As Integer

Set wnet = CreateObject("WScript.Network")
    'ファイル名設定
    usName = wnet.UserName
    tm = Format(Date, "yyyymmdd")
    fileName = tm & "_" & usName & "_data.csv"

n = FreeFile
    'ActiveWorkbook.Path出力先パス
    csvFile = ActiveWorkbook.Path & "\" & fileName
    '同名ファイル上書き、無い場合は作成されます
    Open csvFile For Output As #n
    '行方向ループ
    For i = LBound(DataAry, 1) To UBound(DataAry, 1)
        '列方向ループ
        For j = LBound(DataAry, 2) To UBound(DataAry, 2)
            If j = UBound(DataAry, 2) Then
                Print #n, DataAry(i, j)    '最終列
            Else
                Print #n, DataAry(i, j) & ",";    '継続
            End If
        Next
    Next
    Close #n
    MsgBox fileName & "を出力しました"

End Sub

Qchan1962 · Answer

＃２、6です
私の読解力がなかった為、すみません。
＞フロッピーディスク型アイコン
に登録されたVBAをコピー処理と保存処理に分けます。
標準モジュールに

sub フロッピーディスク型アイコン登録マクロ()
保存処理コード
end sub

sub コピーマクロ()
コピー処理コード
end sub

ThisWorkbookモジュールに
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
call コピーマクロ
End Sub

*call コピーマクロ　は念のため call モジュールオブジェクト名.コピーマクロ　とした方が良いと思います。（コピーマクロはプロシージャ名）

BeforeSaveは保存の前に実行されるので　フロッピーディスク型アイコンからでも、保存処理時にcall コピーマクロでコピー処理コードが実行されます。
（「保存しない」を選んだときはコピーマクロも実行しない）

＊この場合、必ず保存時にコピーマクロが実行されます。
処理速度や無駄な処理を実行しないため、何だかの判定キーを作れれば
ロジック自体を簡素化できると思いますが、現状の条件提示では仕方ないと思いますね。

ここまで書いて、他のご質問を読ませて頂きました。
コピー処理コードは、他のブックを開いてコピーするようですね。
この場合は、問題が生じるようです。（https://oshiete.goo.ne.jp/qa/12665145.htmlのスレッドですでに回答されていますね）

本ご質問とは関係ないのですが、一連のご質問で、1つ疑問に思ったことがあります。

データをコピーする先のブックは何人くらいで使うのでしょう？
シートを作るご質問や開かれていた時の処理などを聞かれていたので、、

集計のタイミングやデータ更新のタイミングなども気になりますね。

私は、ExcelをほとんどVBAで使用しています。
なので、データの入出力、加工、分析などに使う事が多いのですけれど

私だったらどうするかな？と少し考えてみました。（仕様が分からないので参考にならないかもですが）
まず、データのやり取りはデータベースを建てていないのであれば、
ユーザーごとにCSVを出力して行うと思います。（ExcelからのCSVなので文字化けや加工は不要か？最小限で済むと思います）
集計は同じフォルダに入っているCSVファイルをファイル名などで振り分け
Excelに抽出、必要データを配列などを使い集計、結果を出力フォーマットのシートに書き出し、、みたいな感じです。

CSVは追加書き込みが良いのか、同名上書きが良いのか、、判断が付きませんけれど、、　多分、処理負担は少なくなるような気がします。

Qchan1962 · Answer

こんばんは、
＞ファイルを閉じるとき、もしくはファイルを保存した時に自動的に実行させたいです。
ここに拘らなければ、＃３様が提案されているように
値で貼り付けるというマクロ実行時に保存マクロも実行するのが良いのではないかと思います。

いずれにしても、保存してはいけない状況が発生する可能性があるのなら
判断をユーザーに聞く必要があるのかなと思います。
逆に必ず保存する状況を確定できるのなら、その状況が確定する段階で
空いているセルなどに値を入れ、そのセルを
Workbook_BeforeSaveやWorkbook_BeforeCloseなどイベントで
条件分岐しSaveすれば良いかも知れません。ただし、この場合
閉じる時又は開く時にCloseやOpenマクロでその値（判定キー）を消す必要がありますね。

トモクンアヤチャン · Answer

「標準モジュール"Sample1"を実行させるというのは良い案でしょうか？」
やったことがないので、分かりませんが、それでよいのではないかと思います。

トモクンアヤチャン · Answer

Workbook_BeforeSave 　プロシージャがいいと思います。
BeforeClose　だと、保存せずに閉じる場合も起動するので、どちらがいいかは微妙です。

fujillin · Answer

こんばんは

＞この処理を作業後に必ず実施させたいのですがオペレータは忘れてしまう事があるため、
直後に実行するのでよいのなら、別のタイミングで実行などせずに、そのまま続けて実行するように制御すれば済むことではないのでしょうか？
それとも、何らかの手操作を挟んでからでないと実行できない仕組みなのでしょうか？

その場合には、
＞ファイルを保存した時に自動的にマクロを実行する
だと、ご説明のマクロを実行しなくても、保存時のマクロだけ実行されてしまう可能性がありますけれど、問題はないのでしょうか？

・・・など不明点はありますが、ご質問通りに「保存時に実行するマクロ」を記述したいのなら、BeforeCloseイベントを利用するのが宜しいかと思います。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.workbook.beforeclose

Qchan1962 · Answer

こんばんは
閉じる時に自動実行
Workbook_BeforeCloseイベントを使うのはどうでしょう

一例　ThisWorkbookモジュールに
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call 既にあるA2に値で貼り付けるというプロシージャ
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
End Sub

注意：閉じる時、必ず実行されてしまいます。
間違えで閉じる時などの為に、UIを使ったトラップを設置した方が良いと思います。

zongai · Answer

保存するときに実行ではないですが、
ファイルを閉じるボタンを用意し、そこにマクロを割り当てる。

①値貼り付けを実行（既存のマクロ）
②上書き保存をする。
③閉じる
　　・該当の1ファイルのみ開いている場合はExcelを終了
　　・複数ファイルを開いている場合は、該当ファイルのみ閉じる

こんな内容のマクロで。
↓
Sub Overwrite()
　　call 【値貼り付けマクロの名前】
　　ActiveWorkbook.Save
　　If Workbooks.Count = 1 Then
　　　　 Application.Quit
　　Else
　　　　ActiveWorkbook.Close
　　End If
End Sub

ボタンは目立つところに設置。
ボタンの文字は添付画像ではガチガチに書いてるけど、
単に「上書き終了」とか、オペレータ全員が認識できるうように。

というのはどうでしょう？

【Excel VBA】ファイルを保存した時に自動的にマクロを実行する方法

忘れました Workbook_BeforeSave

今更ミタイナことを書いてしまったので

＃２、6です

こんばんは、

「標準モジュール"Sample1"を実行させるというのは良い案でしょうか？」

Workbook_BeforeSave プロシージャがいいと思います。

こんばんは

こんばんは

保存するときに実行ではないですが、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

忘れました　Workbook_BeforeSave

Workbook_BeforeSave 　プロシージャがいいと思います。