Excel作業で意外と多いのが、次のようなミスです。
- 上書き保存してから「やっぱり前の状態に戻したい…」
- 壊れた・数式が消えた・データを消した
- 保存せずに閉じてしまった
- 同じファイルを複数人で触っていて、いつの版が正しいか分からない
こういう事故を防ぐには、自動バックアップがかなり効きます。
そこで今回は、Excelを閉じる前に自動でバックアップを作成するVBAをご紹介します。
「閉じる操作」をしたタイミングで、日時付きファイルとして別フォルダにコピー保存します。
この記事でできること
- ブックを閉じる直前にバックアップを自動作成
- バックアップは「日時付きファイル名」で保存（上書きされない）
- 保存先フォルダが無ければ自動で作成
- ユーザーは普通に閉じるだけでOK
注意：この仕組みは「ThisWorkbook」に書く
今回の仕組みは、ブックを閉じる直前（BeforeClose）に動かしたいので、
コードを書く場所は「標準モジュール」ではなくThisWorkbookです。
手順：
- Alt + F11 でVBE（VBAエディタ）を開く
- 左側の「Microsoft Excel Objects」から ThisWorkbook をダブルクリック
- そこに貼り付ける
Excelを閉じる前に自動バックアップするコード（コピペOK）
以下を ThisWorkbook に貼り付けてください。
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim backupFolder As String
Dim backupName As String
Dim backupPath As String
' ===== 設定：バックアップ保存先 =====
' ① このブックと同じフォルダに「backup」フォルダを作って保存する例
backupFolder = ThisWorkbook.Path & "\backup"
' ※ブック未保存（ThisWorkbook.Path が空）の場合はデスクトップへ
If Len(ThisWorkbook.Path) = 0 Then
backupFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\backup"
End If
' =====================================
' バックアップフォルダがなければ作成
CreateFolderIfNotExists backupFolder
' バックアップ名（元ファイル名_日時.xlsx）
backupName = GetBaseName(ThisWorkbook.Name) & "_" & Format(Now, "yyyymmdd_HHMMSS") & GetExtension(ThisWorkbook.Name)
backupPath = backupFolder & "\" & backupName
' バックアップ作成（このブック自体をコピー保存）
' ※ブックの保存状態に関係なく「コピーとして保存」するため SaveCopyAs を使う
ThisWorkbook.SaveCopyAs backupPath
End Sub
' フォルダが無ければ作成する
Private Sub CreateFolderIfNotExists(ByVal folderPath As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(folderPath) Then
fso.CreateFolder folderPath
End If
End Sub
' 拡張子を除いたファイル名を取得
Private Function GetBaseName(ByVal fileName As String) As String
Dim p As Long
p = InStrRev(fileName, ".")
If p > 0 Then
GetBaseName = Left(fileName, p - 1)
Else
GetBaseName = fileName
End If
End Function
' 拡張子（.xlsx など）を取得
Private Function GetExtension(ByVal fileName As String) As String
Dim p As Long
p = InStrRev(fileName, ".")
If p > 0 Then
GetExtension = Mid(fileName, p)
Else
GetExtension = ""
End If
End Function
このマクロのポイント（初心者向け）
① Workbook_BeforeClose は「閉じる直前」に動く
Private Sub Workbook_BeforeClose(Cancel As Boolean)このイベント（イベント＝特定のタイミングで自動的に動く処理）を使うと、
ユーザーが「×」で閉じる、Alt + F4 で閉じる、などのタイミングで自動実行されます。
② SaveCopyAs で「コピー保存」する
ThisWorkbook.SaveCopyAs backupPathSaveCopyAs は、ブックをコピーとして別名保存します。
元ファイルには影響しないので、バックアップ用途に最適です。
③ 日時付きファイル名で「上書きされない」
Format(Now, "yyyymmdd_HHMMSS")日時をファイル名に入れることで、毎回別ファイルとして保存されます。
過去の版に戻したいときも、日時で選べて便利です。
よくあるカスタマイズ
① バックアップ保存先を固定フォルダにしたい
例：Cドライブ直下に保存
backupFolder = "C:\ExcelBackup"② バックアップを「最大30件まで」にして古いものを削除したい
バックアップが増えすぎるのを防ぐ場合は、
古いファイルを削除する処理を追加できます（応用編として別記事で紹介できます）。
③ 閉じる前に確認メッセージを出したい
If MsgBox("バックアップを作成して閉じます。よろしいですか？", vbYesNo) = vbNo Then
Cancel = True
Exit Sub
End If
Cancel = True にすると閉じるのを中止できます。
動作確認のしかた
- このExcelブックを保存する（または未保存でもOK）
- Excelを閉じる（×ボタンなど）
- 同じフォルダ内に backup フォルダが作られる
- 日時付きのバックアップファイルが保存されている
まとめ：自動バックアップは「事故防止」の最強保険
Excelは便利ですが、上書き事故やデータ破損は意外と起きます。
今回の仕組みを入れておくと、閉じるだけで自動でバックアップが残るので安心です。
- 保存忘れや上書き事故の保険になる
- 日時付きで過去版に戻しやすい
- 作業者が多いファイルほど効果が高い
ぜひ、重要な業務ブックから導入してみてください。
コメント