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

Here are some similar macros I've collected. Maybe you can find something useful in them:

---

Saving in Multiple Locations http://excel.tips.net/T002774_Saving_in_Multiple_Locations.html

Saving in Multiple Locations

If the drive letter assigned to your USB drive is always the same, you can use code like the following in the ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next

    Application.EnableEvents = False

    ThisWorkbook.Save

    ThisWorkbook.SaveCopyAs "J:" & ThisWorkbook.Name

    Application.EnableEvents = True

End Sub

Change the "J" to the appropriate drive letter.

If the drive letter may change but the volume label is always the same, use code like the following in the ThisWorkbook code module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim DriveLetter As String

    Dim VolumeName As String

    On Error Resume Next

    Application.EnableEvents = False

    ThisWorkbook.Save

    VolumeName = "YourVolumeName"

    DriveLetter = GetDriveLetter(VolumeName)

    If Len(DriveLetter) > 0 Then

        ThisWorkbook.SaveCopyAs DriveLetter & ":" & ThisWorkbook.Name

    End If

    Application.EnableEvents = True

End Sub

Private Function GetDriveLetter(VolumeName As String) As String

    Dim FSO As Object

    Dim DD As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")

    For Each DD In FSO.Drives

        If DD.IsReady = True Then

            If StrComp(DD.VolumeName, VolumeName, vbTextCompare) = 0 Then

                GetDriveLetter = DD.DriveLetter

                Exit Function

            End If

        End If

    Next DD

End Function

Change VolumeName to the appropriate volume name.

In the last issue of WordTips there were a number of readers that got the idea that I was suggesting that people should use the FastSave feature. In fact, I got quite a bit of feedback on that tip, some of it quite pointed. (According to one reader, I was even “irresponsible.” Another stated that I was doing a disservice to readers.)

I am sorry if I gave the impression that I thought FastSave is a great idea. That is far from the truth; I do not. There are several other “features” of Word that I think are ill conceived or totally unnecessary, as well. It doesn’t change the fact that they are features and people can use them, if they desire. WordTips are not designed to promote one method of doing something over another, but to provide alternative ideas that people can incorporate into their Word use, if they so desire.

With that being said, let me point out specifically why you should not use the FastSave feature.

• Other word processors may not be able to open Word documents saved with the FastSave feature enabled. For instance, WordPerfect says it can open Word documents, yet some versions of WordPerfect cannot open FastSaved Word documents.
• Third-party software designed to work with Word documents (such as some grammar checkers, document management programs, and desktop publishing software) will not work correctly with FastSaved Word documents.
• Find File (either in Word or in Windows) may not work correctly with FastSaved Word documents.
• FastSave results in more complex files being saved on disk. Any time you increase complexity, you run the risk of corrupting the files easier.
• FastSaved files take up more disk space.
• There have been reports of macro viruses “living on” in FastSaved documents, even though you thought you had them removed.

The list could go on and on, but you get the idea. In short, unless you have a specific need to use FastSave (such as if you are only creating simple documents using Word 6 on a slow 286 machine), it is probably a good idea to turn it off. The FastSave feature is turned on by default in all versions of Word, even in the original Word 97. Only if you are using Word 97 SR-1 is it turned off by default.

As a side note, according to the Microsoft Knowledge Base there are several circumstances under which a FastSave is not done by Word, even if you have enabled the feature. These circumstances include the following:

• When you save a document for the first time. (This makes sense, right?) This includes when you choose Save As to save under a new name or in a new location.
• When your document is saved on a network server or remote volume. (Word only performs a FastSave to a local volume.)
• When Word reaches the limit of how much information it can save using the FastSave option. In this case Word does a normal save, and then again starts using FastSave for future saves.

Nuff said? (I hate it when I’m irresponsible.)

Excel- Saving in Two Locations

http://excel.tips.net/T003042\_Saving\_in\_Two\_Locations.html

Save the current document in two locations add-in for Word 2007 & 2010 -http://www.gmayor.com/SaveInTwoPlacesAddIn.htm

Answer 4

I think the problem is in  the function of bellow that I use it:

Sub SaveToTwoLocations()

Dim strFileA, strFileB As String

Application.DisplayAlerts = False

ActiveWorkbook.Save

strFileA = ActiveWorkbook.Name

'Define backup path shown in blue below

strFileB = "C:\backup\excelnew" & strFileA

ActiveWorkbook.SaveCopyAs Filename:=strFileB

Application.DisplayAlerts = True

End Sub

When I use the ActiveWorkbook.SaveCopyAs , the main file has not problem .When I save it , it saves correctly but a copy of it that  is saved in location of c:\backup\excelnew , has problem when I save it,I get error 1004 : microsoft excel an not access the file c:\backup\excelnew\filename.xlsx

But if i use the ActiveWorkbook.SaveAs , the main file has probelm in saving it and the copy of that file has not problem when save it.the copy file that save in location of c:\backup\ecelnew\ , is saved corectly without any problem.

Why this problem accure?

I have used this code in word But it has not this problem!

How can I solve this problem?

Thanks.

Answer 5

Hi,

Thanks very much for your reply. It was very useful thank you.

 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 .

option Explicit

Dim WithEvents App As Application

Dim LastDC As Document

Public Sub AutoExec()

    Set App = Application

End Sub

Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)

Set LastDC = Doc

    Application.OnTime Now, Me.CodeName & ".App_DocumentAfterSave"

End Sub

Private Sub App_DocumentAfterSave()

   Dim fso As Object

  Set fso = CreateObject("Scripting.FileSystemObject")

  fso.CopyFile LastDC.FullName, "C:\backup\word"

End Sub

Would you please help me ?

Best Regards

Thanks