saving spreadsheet on two separate drives without box displa

  • I have a spreadsheet that I want to save on my C drive over the existing file and also on the network G drive over the existing file however when I place the code


    ActiveWorkbook.SaveAs ("G:\blah blah.xls")


    ActiveWorkbook.SaveAs ("C:\blah blah.xls")


    in my macro
    A box comes up stating "The file 'G:\blah blah.xls' already exists. Do you want to replace it"?


    When I click yes, then another box comes up stating "The file 'C:\blah blah.xls' already exists. Do you want to replace it"?


    How can I save this file to two separate drives (using VBA code) without those boxes coming up?


    Please Help!


    Thanks!

  • Thanks, RoyUK. That code did eliminate the diplay boxes, but it did not save changes.


    How would I be able to save my changes in two separate drives (C: and G; keep in mind the file is already saved in these two drives) without the "Do you want to replace box" showing up?


    Please Help!
    Thanks

  • 21MSU,


    This code and the attached files will do the job.


    Sub FileUpdate()
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Turn off alerts
    Application.DisplayAlerts = False
    'Use ThisWorkbbok save for current path or SaveAs with new path name
    ThisWorkbook.SaveAs ("C:\Temp\blah blah.xls")
    Application.ThisWorkbook.Save
    'Use File System Objects to copy saved file to network location and "True" to overwrite without prompt
    fso.CopyFile "C:\Temp\blah blah.xls", "P:\MARKETING\Temp\", True
    'Turn alerts back on
    Application.DisplayAlerts = True
    End Sub


    Good Hunting.


    AJW

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!