auto save as macro in excel

Question

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 1

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.

Answer 2

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

Answer 3

Dear Andreas

Thanks very much.

Best Regards

Answer 4

Dear Andreas

Sorry to bother you.

I am really thank you.

I have another question.

When User want to close the excel , should select close bottom twice.But this problem does not occurs in word !

Would you please tell me how can I solve this problem?

Thanks Very much

Best Regards.

Answer 5

When User want to close the excel , should select close bottom twice.But this problem does not occurs in word !

When User want to close the excel...

a.) He should click the close button twice?

To accomplish this by VBA code is not possible.

b.) He must click  the close button twice?

Sorry, no idea why this happens.

Andreas.