Excel終了時に自動バックアップを作成する方法【VBAコード付き】

ExcelVBA
2023.10.13

・こんな悩みはありませんか？

  • 共有フォルダの Excel ファイルを誰かが誤って削除してしまった
  • 上書き保存してしまい、元の状態に戻せない
  • 毎日使う Excel のバックアップを自動で残したい
  • 複数人で使うファイルなので、誰が操作したかも記録したい

Excel は便利ですが、データ消失のリスクが常にあります。 特に共有フォルダで運用している場合、誤操作による削除や上書きは避けられません。

・この記事でできること（結論）

  • 自動でバックアップファイルを作成
  • 日付・時刻入りのファイル名で保存
  • 必要に応じて「操作したユーザー名」も付与

といった仕組みを VBA だけで簡単に実装できます。

・なぜ自動バックアップが必要なのか？（実体験）

ある日、取引先から「共有 Excel ファイルを削除してしまった」と連絡がありました。 幸い NAS にバックアップが残っていたため復旧できましたが、最新の情報ではなく2日分のデータは消失し、再入力に大きな時間がかかりました。
この経験から、 「Excel を閉じた瞬間に自動バックアップを作る仕組みが必要だ」 と痛感し、今回の VBA を作成しました。

・この方法が特に役立つ方

  • 毎日 Excel を使って業務データを更新している
  • 複数人で共有しているファイルを扱っている
  • 過去の状態に戻せるようにしておきたい
  • バックアップを自動化して安心したい

・実装する VBA の動き

Excel を閉じるときに自動で以下を行います

1.　現在のファイルを保存
2.　元ファイル名を取得
3.　日付・時刻を付けたバックアップ名を作成
4.　指定フォルダに保存
5.　必要ならユーザー名も付与

・今回使用するコードの説明

構文①(アクティブブックを名前を変えて保存)
ActiveWorkbook.SaveAs Filename:= “保存したいフォルダ\バックアップファイル名.xlsm”

構文②(ログインしているユーザを取得する)
Environ(“USERNAME”)

・コードの入力する場所

ThisWorkbook内に下記を入力
入力場所の開き方はExcelタブの「開発」→「Visual Basic」→「ThisWorkbook」

・VBAコード（そのまま使える入力例）

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.Save

Dim fName As String
Dim fPath As String
Dim fNameS As String

'ファイル名
fName = ActiveWorkbook.Name
'ファイルパス(※保存したい場所に直接指定も可能)
fPath = ActiveWorkbook.Path
'拡張子は含まない(-5は「.xlsm」の5文字)
fNameS = Left(fName, Len(fName) - 5)

'ファイルの保存(ファイル名:現ファイル名に年月日、時間、分)
ActiveWorkbook.SaveAs Filename:=fPath & "\" & fNameS & "_" & Format(Now, "yymmddhhnn") & ".xlsm"

End Sub

・応用版（複数で利用している場合、保存したユーザー名もファイル名に付けたい場合）

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Save

    Dim fName As String
    Dim fPath As String
    Dim fNameS As String
    Dim uName As String

    fName = ActiveWorkbook.Name
    fPath = ActiveWorkbook.Path
    fNameS = Left(fName, Len(fName) - 5)
    uName = Environ("USERNAME")

　　'ファイルの保存(ファイル名:現ファイル名に年月日、時間、分、ユーザー名)
    ActiveWorkbook.SaveAs Filename:= _
        fPath & "\" & fNameS & "_" & Format(Now, "yymmddhhnn") & "_" & uName & ".xlsm"

End Sub

・運用時の注意点（入力例を使った場合で運用した際の注意）

  • ファイルを閉じるたびにバックアップが増えるため、定期的に不要分を削除する
  • バックアップ先は PC 本体ではなく、サーバーや NAS など別の場所が望ましい
  • VBA の動作保証は環境に依存するため、初回はテストしてから運用する

VBAを使わずにバックアップしたい場合（Excel標準機能）

VBAコードの入力が不安だったり、それ以外で対応したい場合のバックアップの方法として、Excelの機能でのバックアップがあります。(Excel2019)

バックアップをしたい対象ファイルの「ファイル」タブをクリックし「名前をつけて保存」を選択します。

名前を付けて保存のダイアログが表示されたら「ツール」をクリックし「全般オプション」を選択します。

全般オプションのダイアログが表示されたら「バックアップファイルを作成する」にチェックをしてOKします。

この設定をしておくと上書き保存をした際にバックアップが行われます。
ただし、

  • 拡張子が .xlk になる
  • 上書き保存時のみ作成
  • バージョン管理は弱い

という制限があるため、VBA の方が柔軟で実用的です。

・まとめ

  • Excel のデータ消失は誰にでも起こり得る
  • 自動バックアップを仕組み化すれば安心
  • VBA を使えば「閉じるだけでバックアップ」が実現できる
  • ユーザー名や日付を付けて管理しやすくできる

この記事を通して新しいアイディアが生まれたり、VBAに興味を持っていただけたら幸いです。
ここまでご覧いただき、誠にありがとうございます。

ご注意


・掲載されている内容には細心の注意をしてるつもりですが、
間違いやご指摘等がありましたら、「お問い合わせ」からご連絡をいただけますと幸いです。
・掲載されている入力VBAコード等は動作を保証するものではなく、あくまでサンプルとして掲載しております。
・掲載されている入力VBAコード等は自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
・対応バージョン
　Excel2021、2019、2016

コメント