CODEWITHSUNDEEP
excelvba
Gravitate
Gravitate

Reputation: 3064

How can I use VBA to revert ThisWorkbook to a previously saved state?

I would like a macro to undo ALL changes since the workbook was last opened/saved.

I thought this would be as easy as re-opening the workbook, similar to this answer: https://stackoverflow.com/a/44244262/1473412

However, when I use:

Workbooks.Open(ThisWorkbook.FullName)

Literally NOTHING happens. No alert, no error, no change. It is as though the command is just ignored. Has this behaviour changed in recent versions of Excel?

What is the easiest way to revert a workbook to its previously saved state in the latest version of excel (2016)?

Thanks,

Upvotes: 0

Views: 2062

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Logic:

Close the Excel file without saving and simply re-open it. But then how do I reopen it from the same file?

  1. Create a VBScript from your code
  2. Put a Delay of say 3 secs in that and the code to open the Excel file
  3. Call the vbscript and close the current file.

Is this what you are trying?

Sub Sample()
    Dim file As String
    file = ThisWorkbook.Path & "\DeletemeLater.vbs"

    Sheet1.Range("A2").Value = "Blah Blah" '<~~ This change will not be saved

    '~~> Create a vbscript file
    Open file For Output As #1
    Print #1, "WScript.Sleep 300"
    Print #1, "Set oExcelApp = GetObject(,""Excel.Application"")"
    Print #1, "oExcelApp.Visible=True"
    Print #1, "Set oWB = oExcelApp.Workbooks.Open(" & Chr(34) & ThisWorkbook.FullName & Chr(34) & ")"
    Close #1

    Shell "wscript " & Chr(34) & file & Chr(34), vbNormalFocus

    ThisWorkbook.Close (False)
End Sub

And one more thing. Add this in the workbook code area

Private Sub Workbook_Open()
    On Error Resume Next
    Kill ThisWorkbook.Path & "\DeletemeLater.vbs"
    On Error GoTo 0
End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

Try this workaround:

Public Sub ReOpenWithoutSave()
    Application.EnableEvents = False
    ThisWorkbook.Saved = True
    ThisWorkbook.ChangeFileAccess xlReadOnly, , False
    Application.Wait Now + TimeValue("00:00:01")
    ThisWorkbook.ChangeFileAccess xlReadWrite, , True
    Application.EnableEvents = True
End Sub
  • It marks ThisWorkbook as already saved (but does not save it).
  • It makes ThisWorkbook read only and waits 1 second to let it proceed
  • It makes ThisWorkbook writeable again (which causes a re-open automatically)

Upvotes: 2

Related Questions