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

 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.

You only need to click on the close button in upper rigth edge of Excel, do not click on the close button in the upper right edge of the file window (inside Excel).

Excel has a window in window feature, every file that you open, create a new window inside Excel. You can close each file by a click on the close button of that window (as you did).

If you click on the close button in upper rigth edge of Excel, all windows (files) are closed automatically.

I send you a screenshot to your personal email address.

Andreas.

Answer 4

Dear Andreas

I click this  button.But this button close all windows when we do not create personal.xlsb.when I record a macro and stop it for creating the personal.xlsb, whenever open the excel file and want to close it from the close button in upper rigth edge of Excel, should click twice.

(Would you please test it ?)

Excel unlike the word does not have the normal project in VBA and we should record macro for creating a personal.xlsb project that appears like normal project in word.

but when I create personal.xlsb then encounter with this problem at closing excel windows file.

Best  Regards

Answer 5

I can not reproduce this behavior, I did as follows:

Open Excel (only a new empty file is visible)

Start the macro recorder

Choose my personal workbook as destination for the macro

Click Ok (macro recording starts)

Select some cells

Stop the macro recorder

Click on the close button of Excel

A message appears and ask me if I want to save my personal workbook.

Click Save or Discard, doesn't matter.

Excel is closed.

Andreas.