【Excel VBA】ブックを保存するたびにバックアップデータを作成する方法

皆さんこんにちは、エンジニアのIMMRです。

例えば下記のようなブックがあったとします。

図１　ブック５９

このようなブックに日々、一人ひとりが記載して保存をするブックとします。

例えば、このブックを保存ボタンをクリックした度に別の場所にバックアップ用のデータを作成したいとします。

作業としては、その都度、別名で保存とかの作業をするのではなく、

下記の図のように、現物であるブックの保存ボタンを押したら、自動で別名で、別の場所にバックアップデータを作成します。

図２　保存形式

1.イベント機能について

何かのデータを入力してからプログラムを実行するのではなく、上書き保存ボタンをクリックした度の実行なのでイベント機能を使用します。

イベント機能については下記、記事でも説明しております。

【Excel VBA】セル内の値が変更された時点で、プログラムを実行する方法

設定方法などについては、上記記事を参照ください。

そして、今回は保存をした後の実行のなので「Workbook_AfterSave」を使用します。

図３　保存後の実行

この中にプログラムを記載します。

２.ブックのバックアップ作成について

ブックのバックアップを行うのに必要な情報があります。

①どこに保存するか

今回は下記、フォルダー（C:\バックアップデータ）に作成することにします。

図４　バックアップ用のフォルダー

②どのような名前にするか

同じ名前だと上書きをしてしますので、毎回名前を変える必要があります。

いろいろ方法はありますが、ここでは、

「元のブック名」＋「＿」＋「保存をした日時（年・月・日・時・分・秒）」

とした名前にしたいと思います。これなら何度行っても名前が重なることはないです。

３.ブックのバックアップ作成（サンプルプログラム）について

下記がサンプルプログラムです。

サンプルプログラム

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Dim sa_Path As String, sa_Data As String

    sa_Path = "C:\バックアップデータ"

    sa_Data = Replace(ActiveWorkbook.Name, ".xlsm", "") & "_" & _
              Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now)

    Application.EnableEvents = False
        ActiveWorkbook.SaveAs sa_Path & "\" & sa_Data & ".xlsm"
    Application.EnableEvents = True

    Application.Quit

End Sub

では、サンプルプログラムについて解説します。

変数「sa_Path」には、バックアップのデータを保存するパスをセットしています。

変数「sa_Data」には、バックアップデータの名前をセットしています。

　　　　　　　　　Replaceでは、元のデータ名から拡張子を外しています。ABC.xlsm　→　ABC

　　　　　　　　　その後ろに”＿”（アンダーバー）を付けています。

　　　　　　　　　そして、最後に保存ボタンを押した時の年・月・日・時・分・秒をつけています。

ActiveWorkbook.SaveAs で保存

　　　　　　　　バックアップを保存するパス　＋　「sa_Data」で作成したデータ名　＋　拡張子

　　　　　　　　で別名の保存をするのですが、ここで保存をすると

　　　　　　　　保存をした（イベント）ということでWorkbook_AfterSaveの出だし（最初）に戻ってしまいます。

　　　　　　　　そうなると無限ループになり、処理が終わらなくなってしまいます。

　　　　　　　　そのようにならないためにApplication.EnableEventsを前後に記載しています。

　　　　　　　　Application.EnableEventsは、イベントの機能を無効にするためのものです。　　　　　　　　

　　　　　　　　Falseにすることで、イベント機能を無効にします。

　　　　　　　　そして、保存を行います。イベント機能を無効にしているのでイベント機能は発生しません。

　　　　　　　　保存が終わったら、Trueにして、イベント機能の無効を解除します。

最後にApplication.Quitを使ってブックを閉じます。

４.ブックのバックアップ作成（サンプルプログラム）の実行

では、実際に実行をしていきたいと思います。

まず、下記のようにバックアップのフォルダーの中には、なにもないです。

図５　バックアップ用のフォルダー（実行前）

では、保存ボタンを押してプログラムを実行してみます。

図６　プログラムの実行

保存ボタンを押して実行していますが、ここで重要になってくるのが保存ボタンを押した日時です。

ここでは、２０２３年６月２９日１０時１１分　に実行しています。

では、バックアップのフォルダーを確認してみます。

図７　バックアップ用のフォルダー（実行後）

「元のブック名」＋「2023629101140」という名前で保存されています。

「2023629101140」は、２０２３年６月２９日１０時１１秒４０秒ということです。

無事、保存を押した時点の時間でコピーが出来ています。

ブックを保存するたびにバックアップデータを作成することができました。