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

I think For first time close the book1 project and for second time close the personal.xlsb project. so The user should click twice the close bottom.

But I don't know how to close both project with one click.

Thanks .

Best Regards.

Answer 4

Normally the PERSONAL.XLSB file is not visible (like your AddIn's) and can't be closed manually. Switch to that file, click the View tab and hide this window.

Andreas.

Answer 5

DEAR

Yes.the PERSONAL.XLSB file is not visible and I have hided it. but If I open an Excel file, and

then close it, the Excel program remains open (and blank). I would like the

Excel program to close when I close the last file.I think this occurs due to the personal.xlsb/xls file.So I have to click close twice.

if I uncheck the "Show all windows in the taskbar" feature under Excel Options, Advanced, Display, it work correctly and when I click close button , excel is closed. but this is not a good way . when we open multiple excel files  , only one excel file is displayed in taskbar.

Would you please offer another way for solving this problem?

Thanks

Best Regards