使用 VBA 对 excel 文件执行所有操作后，如何取回原始文件？

How do I get back my original file after performing all the Operations on the excel file using VBA?

提问by niko

Can Anyone tell me how do I undo all my changes to my workbook? I have file excel1.xlsx and I have did sorting and many operations on the excel.xlsx using vba. But at the end I want the excel1.xlsx to be the same which was at the start. How do i Undo all my changes using vba?

谁能告诉我如何撤消对工作簿的所有更改？我有文件 excel1.xlsx，我使用 vba 对 excel.xlsx 进行了排序和许多操作。但最后我希望 excel1.xlsx 与开始时相同。如何使用 vba 撤消所有更改？

 activeworkbook.saved = True

I have found that it retains back all the contents as at the begginning but its not working.So is there any command where i can get back my original file after performing operations over it. Well yes

我发现它保留了开始时的所有内容，但它不起作用。所以有什么命令可以让我在对其执行操作后取回我的原始文件。嗯，是

              wb1.Sheets(1).Activate
              ActiveWorkbook.Close savechanges:=False

It works but I dont want my workbooks to be closed it should be still opened. How do I make it? Thanks in advance.

它有效，但我不希望我的工作簿被关闭它应该仍然打开。我该怎么做？提前致谢。

采纳答案by aevanko

In order to undo a sub routine, you can either choose not to save the file and just close it, or you have to write a special sub routine to save the state of the file, then restore the state (custom undo). This is one of the pains with sub routines is that they cannot be undone through normal undo. Most people, me including, will reccomend you work off a backup.

为了撤消子程序，您可以选择不保存文件并关闭它，或者您必须编写一个特殊的子程序来保存文件的状态，然后恢复状态（自定义撤消）。这是子例程的痛苦之一，它们无法通过正常的撤消来撤消。大多数人，包括我在内，会建议你使用备份。

When making your custome undo routine, the big question is what do you need to save the state for? Saving all information about the file would be unnessesarily heavy, so it's good to know what you want to save.

在制定您的客户撤消例程时，最大的问题是您需要为什么保存状态？保存有关文件的所有信息会非常繁重，因此最好知道要保存的内容。

Update: This is a dirty way to backup the sheet if you only have 1 sheet of data. This is more of a proof of concept of one way to create a backup and not finalized perfect code. It just creates a backup copy of the currentsheet and when you'restore' you are simply deleting the original sheet and renaming the backup to what it used to be called. :p

更新：如果您只有一张数据，这是一种备份工作表的肮脏方式。这更像是一种创建备份的方法的概念证明，而不是最终确定的完美代码。它只是创建当前工作表的备份副本，当您恢复时，您只需删除原始工作表并将备份重命名为以前的名称。:p

How to test:Put some data and value in your original sheet then run the Test() sub-routine!

如何测试：在原始工作表中放入一些数据和值，然后运行 ​​Test() 子例程！

Public backupSheet As Worksheet
Public originalSheet As Worksheet
Public originalSheetName As String

Sub CreateBackup()
    Set originalSheet = Application.ActiveSheet
    originalSheetName = originalSheet.Name
    originalSheet.Copy After:=Sheets(Application.Worksheets.Count)
    Set backupSheet = Application.ActiveSheet
    backupSheet.Name = "backup"
    originalSheet.Activate
End Sub

Sub RestoreBackup()
    Application.DisplayAlerts = False
    originalSheet.Delete
    backupSheet.Name = originalSheetName
    Application.DisplayAlerts = True
End Sub

Sub ZerosFromHell()
    Range("A1:Z100").Select
    Cells.Value = 0
End Sub

Sub Test()
    Call CreateBackup
    Call ZerosFromHell
    MsgBox "look at all those darn 0s!"
    Call RestoreBackup
End Sub

回答by Jean-Fran?ois Corbett

Short answer: you can't. Sorry.

简短的回答：你不能。对不起。

Changes you make to your sheet using VBA cannot be undone at the click of a button or with a single, standard VBA statement.

使用 VBA 对工作表所做的更改无法通过单击按钮或使用单个标准 VBA 语句撤消。

The right thing to do would seem to be: do your VBA-driven work on a copy of the sheet, and delete/don't save this copy if you don't want to keep the changes (and reopen the original if you need to do so). But from your question, it sounds like you don't want to do that.

正确的做法似乎是：在工作表的副本上执行 VBA 驱动的工作，如果您不想保留更改，请删除/不保存此副本（如果需要，请重新打开原件）这样做）。但从你的问题来看，听起来你不想那样做。

Your only alternative is then to write your own VBA procedure that backtracks all the changes you've done. Depending on what operations you performed, reversing them could be a ridiculously complicated thing to do, compared to just closing without saving and re-opening. But if you insist, by all means, knock yourself out!

您唯一的选择是编写您自己的 VBA 过程，以回溯您所做的所有更改。根据您执行的操作，与仅关闭而不保存并重新打开相比，反转它们可能是一件非常复杂的事情。但如果你坚持，无论如何，把自己打倒！

回答by Anonymous Type

1. Save a copy of the original workbook prior to running your macro. using the .SaveAs method at the beggining of the sub routine.
2. Run the VBA macro routine in the original workbook.
3. Now have a second macro "Undo VBA changes" that opens the workbook copy from step (1) , closes the workbook that ran the macro in Step (2) and calls the .SaveAs method again overwriting the existing workbook from step (2).

1. 在运行宏之前保存原始工作簿的副本。在子例程开始时使用 .SaveAs 方法。
2. 在原始工作簿中运行 VBA 宏例程。
3. 现在有第二个宏“撤消 VBA 更改”，它打开步骤 (1) 中的工作簿副本，关闭在步骤 (2) 中运行宏的工作簿，并再次调用 .SaveAs 方法覆盖步骤 (2) 中的现有工作簿。

Note: In order to get this UndoMacro to work you will need to put it in an Addin or a seperate workbook (an addin is cleaner). This will allow you to run the .SaveAs method and overwrite teh original workbook from Step (2) which will at this point have been closed to prevent an VBA runtime error message occuring.

注意：为了让这个 UndoMacro 工作，你需要把它放在一个插件或一个单独的工作簿中（插件更干净）。这将允许您运行 .SaveAs 方法并覆盖步骤 (2) 中的原始工作簿，该工作簿此时已关闭，以防止出现 VBA 运行时错误消息。

回答by WayneVerbeek

If all of your data is cleanly organized, this works pretty well. Much like the OP, I needed to go back to the original state of an Excel file, and didn't want to have to re-load the original (it takes about 25 seconds to load, due to aged infrastructure, and slow PCs).

如果您的所有数据都组织得井井有条，这将非常有效。与 OP 非常相似，我需要返回 Excel 文件的原始状态，并且不想重新加载原始状态（由于基础设施老化和 PC 速度慢，加载大约需要 25 秒） .

What I did was to copy all of the data into a variant array, do all of my processing to the workbook, then write back the variant array to the Excel file (data is on a worksheet called "Data", and starts at Row 2, and uses columns A through Z). While this uses a bit of RAM, the reading/writing is nearly instantaneous. Relevant code:

我所做的是将所有数据复制到一个变体数组中，对工作簿进行所有处理，然后将变体数组写回 Excel 文件（数据位于名为“数据”的工作表上，从第 2 行开始，并使用 A 到 Z 列）。虽然这使用了一点 RAM，但读取/写入几乎是瞬时的。相关代码：

Dim varDataArray As Variant, wb As Workbook, ws As Worksheet, lngEndRow as Long

Set wb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets("Data")

With ws
 ' This simply finds the last row with data
 lngEndRow = .Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
 ' This reads all cell data from A2 to Z###, where ### is the last row
 varDataArray = .Range("A2:Z" & lngNumberOfRows).Value

 ... do things ...

 ' This writes all the data back to Excel
 ' 26 is the numeric column equivalent to "Z"
 .Range("A2").Resize(lngEndRow, 26) = varDataArray

End With