saving an amended file

  hansa 19:56 05 Jan 2010
Locked

My brain hurts tonight.

I have an excel spreadsheet saved in 3 different locations. If I amend the file in one location is there any easy one step way to update it to the other 2 locations?

  VoG II 20:04 05 Jan 2010

Something like this should work. Right click the Excel logo just to the left of File on the menu bar, select View Code and paste in


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
ThisWorkbook.SaveCopyAs Filename:="C:\Folder1\" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs Filename:="C:\Folder2\" & ThisWorkbook.Name
ThisWorkbook.Save
Application.EnableEvents = True
End Sub

Change the two alternate locations to the actual paths then press ALT + Q to close the code window.

  VoG II 20:07 05 Jan 2010

This will work with the file saved in one location only. To have it work in any location you would need to check for the location (ThisWorkbook.Path) and then use a Select Case statement in the above code to determine where the two other locations would be.

  VoG II 20:12 05 Jan 2010

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Select Case ThisWorkbook.Path
Case "C:\Folder1"
ThisWorkbook.SaveCopyAs Filename:="C:\Folder2\" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs Filename:="C:\Folder3\" & ThisWorkbook.Name
Case "C:\Folder2"
ThisWorkbook.SaveCopyAs Filename:="C:\Folder1\" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs Filename:="C:\Folder3\" & ThisWorkbook.Name
Case "C:\Folder3"
ThisWorkbook.SaveCopyAs Filename:="C:\Folder1\" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs Filename:="C:\Folder2\" & ThisWorkbook.Name
End Select
ThisWorkbook.Save
Application.EnableEvents = True
End Sub

  hansa 20:21 05 Jan 2010

Evening VoG,

Right clicking the Excel logo (2010 beta) only shows the usual Move, Size, Minimize & Maximize.

I have now done it by individually saving to the various locations but would love to find a way to save all in one fell swoop.

H

  VoG II 20:26 05 Jan 2010

Press ALT + F11 to open the Visual Basic Editor then double click ThiwWorkbook in the Project - VBA Project window and paste the code in there.

  hansa 20:44 05 Jan 2010

Sorry Vog,
Visual Basic is a foreign language to me.
I have the original file saved on C with one copy on my wife's laptop (public documents)and the other on on a memory stick in the Belkin wireless server. Both the external saves would be via wireless?

Got mixed up with all the different \'s and drive letters and have made a complete horse's ??? of it.

  VoG II 21:02 05 Jan 2010

You basically need to find the addresses then change them. The code shouldn't mind whether the address is a direct address to a file like C:\My Documents or an address on a network like \\mynetwork...

  hansa 21:08 05 Jan 2010

Thanks again VoG.

Will give it a try in the cold light of day and get back to you.

  hansa 19:14 07 Jan 2010

VoG™,

sorry for the delay in getting back to you.

Tried several times with your suggestion but afraid my feeble intellect couldn't handle it.

In the end, dragged and dropped in to the other locations.
The file is a Xmas card list so will hardly need frequent updates.

Thanks for your time.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?