Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Simultaneous Saves (2000)

    Any way to save a file once but to two (or more) different locations?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Simultaneous Saves (2000)

    This code into the thisworkbook object will save the file to c:, C:Temp, d:, as well as its "current location" ) all with the same file name. Change the paths as appropriate.

    Steve
    <pre>Option Explicit
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False
    Dim sCurrFullName As String
    Dim sFilename As String
    sCurrFullName = ActiveWorkbook.FullName
    sFilename = ActiveWorkbook.Name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ("c:" & sFilename)
    ActiveWorkbook.SaveAs ("c:temp" & sFilename)
    ActiveWorkbook.SaveAs ("D:" & sFilename)
    ActiveWorkbook.SaveAs (sCurrFullName)
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    End Sub</pre>


  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Simultaneous Saves (2000)

    Alternatively, use the SaveCopyAs method as follows :

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    <font color=red>If Not SaveAsUI Then</font color=red>
    Application.DisplayAlerts = False
    With Me
    .SaveCopyAs "G:" & .Name
    .SaveCopyAs "H:" & .Name
    End With
    Application.DisplayAlerts = True
    <font color=red>End If</font color=red>
    End Sub

    where AlternatePath1 etc are the paths to to appropriate folders where you want the copies. The file will of course also be saved to it's normal path.
    The lines in red disable the multiple saves should you use SaveAs instead of Save. You can omit these lines if required. It would probably be prudent to include some error handling to cover any possible problems with the destination folders (disk full, not connected etc).

    Andrew C

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simultaneous Saves (2000)

    There is a bug in the SaveCopyAs method that affects workbooks that have loaded userforms.
    Once one follows the savecopyas by a normal save, the userform gets corrupt.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Simultaneous Saves (2000)

    Thanks for that info Jan, I was not aware of that particular bug.

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •