宏保存文件时进行备份 [英] Macro to make a backup while saving a file

问题描述

我想要一个宏，当它被保存时，自动使我的文件备份到不同的文件夹。我找到一个工作的宏，但它每次运行它时复制（当文件被保存时不会自动）。有人可以帮我修改宏代码，按照我的描述工作吗？

I would like to have a macro which automatically makes the backup of my file to a different folder when it is being saved. I have found a working macro but it makes a copy each time when I run it (not automatically when file is being saved). Could anyone help me to amend the macro code to work as I described?

我有一个宏：

Sub Auto_Save()

Dim savedate

savedate = Date

Dim savetime
savetime = Time
Dim formattime As String
formattime = Format(savetime, "hh.MM.ss")
Dim formatdate As String
formatdate = Format(savedate, "DD - MM - YYYY")

Application.DisplayAlerts = False

Dim backupfolder As String
backupfolder = "Z:\My Documents\"
ActiveWorkbook.SaveCopyAs Filename:=backupfolder & formatdate & " " & formattime & " " & ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
MsgBox "Backup Run. Please Check at: " & backupfolder & " !"

End Sub

推荐答案

你意味着你只想要一个与原始文件同名的备份文件？只需从备份副本的文件名中删除日期和时间：

You mean you just want one backup-file with the same name as the original? Just remove the date and time from filename of the backup copy:

ActiveWorkbook.SaveCopyAs Filename:=backupfolder & ActiveWorkbook.Name

您还应该添加一些错误处理，以防在备份文件打开时尝试保存等。

You should also add some kind of error handling in case the backup file is open when trying to save etc.

编辑（根据新输入更新）

OK那么你需要捕获一个事件。我尝试过 BeforeSave 事件，它的工作原理。还有一个 AfterSave 事件可以尝试。

OK, then you need to trap an event. I've tried with the BeforeSave event and it works. There is also an AfterSave event you could try.

将以下内容添加到 ThisWorkbook 模块：

Add the following to the ThisWorkbook module:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim backupfolder As String

    backupfolder = "Z:\My Documents\"

    ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
End Sub

这篇关于宏保存文件时进行备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！