auto save as macro in excel

HI

I have written a code in excel that save as excel file in another location when user clicks save button.

I put the code in this workbook of personal.xlsb

Option Explicit

Private WithEvents App As Application

Private Sub Workbook_Open()
  Set App = Application
End Sub
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Call SaveToTwoLocations
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call SaveToTwoLocations
End Sub

Sub SaveToTwoLocations()
Dim strFileA, strFileB


strFileA = ActiveWorkbook.Name
 Application.DisplayAlerts = False
'Define backup path shown in blue below
strFileB = "C:\backup\excelnew\" & strFileA

ActiveWorkbook.SaveAs Filename:=strFileB

Application.DisplayAlerts = True
 
End Sub

 

 

 

But when  I open an excel file(Not new) and apply some changes and then when i want to save it , I get an error : microsoft excel has stopped working

And I should close the file.

Would you please tell me why this problem happen?

Thanks

|
Answer
Answer
I want to use the same technique for word and I have copied the code of bellow in thisdocument of normal project .but it does not work .
Word is a little bit trickier as Excel, the problem is that Word.Ontime can't execute a sub within the codemodule ThisDocument.

Actually, we should place the code into a normal module, but the event variable did not work in such a module. So we have to split the code in both.

Place this code into the code module "ThisDocument" in your Normal.dot:

--- schnipp ---
Option Explicit

Public WithEvents App As Application

Private Sub App_DocumentBeforeSave(ByVal Doc As Document, _
    SaveAsUI As Boolean, Cancel As Boolean)
  Set Module1.LastDC = Doc
  Application.OnTime Now, "Normal.Module1.App_DocumentAfterSave"
End Sub
--- schnapp ---

Create a normal module, name it "Module1" and place this code into it:

--- schnipp ---
Option Explicit

'Is set from App_DocumentBeforeSave
Public LastDC As Document

Public Sub AutoExec()
  'Runs automatically when Word opens
  Set ThisDocument.App = Application
End Sub

Public Sub App_DocumentAfterSave()
  'Called from Ontime
  Dim fso As Object
  Set fso = CreateObject("Scripting.FileSystemObject")
  fso.CopyFile LastDC.FullName, "C:\backup\word\"
End Sub
--- schnapp ---

Save your Normal.dot, close and reopen Word.

Andreas.

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

There are 3 problems with your code.

a.) You start the backup from the event, means you make a backup of a file that isn't already saved! And what happens if an error occurs during a save?

b.) Who said that the ActiveWorkbook is saved? A macro can save any file. You must save the workbook that is passed as argument.

c.) If you save the file within BeforeClose, your backup contain the changes that you made after the last save.

The solution is to copy the saved file after the event. Try the code below.

Andreas.

 

Option Explicit

Dim WithEvents App As Application
Dim LastWb As Workbook

Private Sub Workbook_Open()
  Set App = Application
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)
  'Store the workbook that is saved into a global variable
  Set LastWb = Wb
  'Try to start the sub immediately, but the event interrupts the execution until the file is saved
  Application.OnTime Now, Me.CodeName & ".App_WorkbookAfterSave"
End Sub

Private Sub App_WorkbookAfterSave()
  'Make a copy of the workbook
  Dim fso As Object
  Set fso = CreateObject("Scripting.FileSystemObject")
  fso.CopyFile LastWb.FullName, "C:\backup\excelnew\"
End Sub

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated May 23, 2018 Views 3,644 Applies to: