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

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在excel上做过排序和很多操作。使用vba 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.

可以，但是我不希望我的工作簿被关闭它应该仍然被打开。我怎么做?提前谢谢。

4 个解决方案

#1

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.

在执行custome undo例程时，最大的问题是要保存状态的原因是什么?保存关于该文件的所有信息会变得不必要地繁重，所以最好知道要保存什么。

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

更新:如果只有一页数据，这是一种很脏的备份方式。这更多地证明了一种创建备份而不是最终完成的完美代码的方法的概念。它只创建一个currentsheet的备份副本，当您“恢复”时，您只需删除原始表并将备份重命名为以前调用的内容。: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

#2

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过程，该过程将跟踪您所做的所有更改。根据您执行的操作，与只关闭而不保存和重新打开相比，反转它们可能是一件非常复杂的事情。但如果你坚持，一定要把自己打倒!

#3

Save a copy of the original workbook prior to running your macro. using the .SaveAs method at the beggining of the sub routine.
在运行宏之前保存原始工作簿的副本。在子例程开始时使用。saveas方法。
Run the VBA macro routine in the original workbook.
在原始工作簿中运行VBA宏例程。
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).
现在有了第二个宏“撤消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正常工作，您需要将它放入一个Addin或一个独立的工作簿(一个Addin是更干净的)。这将允许您运行. saveas方法，并覆盖第(2)步中的原始工作簿，此时该工作簿将被关闭，以防止出现VBA运行时错误消息。

#4

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文件(数据是在一个名为“数据”的工作表,从第二行开始,并使用列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

#1