CODEWITHSUNDEEP
excelvba
F.P
F.P

Reputation: 17831

Save copy of worksheet and revoke all changes to the original

I have a macro that modifies the workbook it is executed in and I want that macro to save a copy of the workbook to a location (preferably specified by the user), revoke all changes to the workbook (so it's in its original state again) and close the workbook.

Unfortunately I can't find any input on the revoking and closing part... Saving a copy is pretty easy, but how to do the rest of those things?

Upvotes: 0

Views: 1350

Answers (3)

Ed Bolton
Ed Bolton

Reputation: 936

I would agree with most of brettdj's response (especially that you should save the file first). However, the BrowseForFolder function is unnecessary. Instead you can use the built-in Windows folder browser in post-2002 versions of Excel

Sub Example()

Dim strFolder as String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ThisWorkbook.Save

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
    If .SelectedItems.Count = 1 Then 
    strFolder = .SelectedItems(1)
    Else
    'Quit/Show message asking to specify location
    End If
End With

'Do everything else

ThisWorkbook.SaveAs strFolder & "\" & ThisWorkbook.Name
ThisWorkbook.Close (False)

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

The (False) on closing the workbook is short for SaveChanges:=False, and may remove the need to switch off alerts

Also, if you wish to make the changes to a different workbook to the one containing the code, you may need help using multiple Excel files. The main lesson would be that you can substitute ActiveWorkbook for ThisWorkbook, or define the workbook upon opening

Upvotes: 3

brettdj
brettdj

Reputation: 55682

Is the complex revoking necessary - why don't you just save the workbook as is at the start of the code (prior to mods), make the changes, saved the change file elswehere and close Excel?

Update [Code sample that works on the workbook it is hosted in caution it will save the the file and close it in that condtion ] 

Sub SimpleSample()
Dim strFolder As String
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
'save current file in the location it was opened from
ThisWorkbook.Save

'make your mods here
'stuff

'get user directory
strFolder = BrowseForFolder
'save the modified workbook to the nuser selected folder (overwrite's any early version of the same name if they exist)
ThisWorkbook.SaveAs strFolder & "\" & ThisWorkbook.Name
'close the file
ThisWorkbook.Close
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub


Function BrowseForFolder(Optional OpenAt As Variant) As Variant
' Ken Puls, http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
'Function purpose:  To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE:  If invalid, it will open at the Desktop level
Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
               BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected.  (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename.  All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
    If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
    If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
    GoTo Invalid
End Select

Exit Function

Invalid:

'If it was determined that the selection was invalid, set to False
 BrowseForFolder = False
  End Function

Upvotes: 1

JMax
JMax

Reputation: 26591

When you close a workbook, you have several options:

ActiveWorkbook.Close False 
' closes the active workbook without saving any changes

ActiveWorkbook.Close True 
' closes the active workbook and saves any changes

ActiveWorkbook.Close 
' closes the active workbook and lets the user decide if 
' changes are to be saved or not

I guess the first one will suit you and fits to your case.

Upvotes: 0

Related Questions