Saving Excel documents in two places at once

Risteard

For anyone who might be able to help me, is there a way to save a document so that it saves in two places at once?

What I mean is, I have two copies of an excel doc. for college. One on my USB and another on my computer. Is there a way that when I click 'Save' on my work that it will update both the files on the USB and Computer instead of having to do the thing manually the whole time?

Keano

It looks like you will have to write a macro. More info here http://www.mrexcel.com/forum/showthread.php?t=70677

I have never tried to this though!

snappieT

Consider having a look at Dropbox. It makes a folder on your computer, just like your "My Documents" folder. Anything that you save in that folder (say, your excel spreadsheet) will get uploaded to the Dropbox website, as well as staying in that folder, thus keeping a backup for you.
You can then install Dropbox on another PC (in college, whatever) and it will have the same folder contents as you home PC. If you don't have permission to install in college/work, you can just visit the website and download your files directly from there.

peterdoherty

take a look at Microsoft SyncToy http://www.microsoft.com/Downloads/details.aspx?familyid=C26EFA36-98E0-4EE9-A7C5-98D0592D8C52&displaylang=en

which you can use to sync 2 folders which may suit you

The_Thing

Hi there, Risteard.

Keano A Legend is correct, a macro will do this for you. MrExcel.Com is a very good site if you're looking for help with Excel or other components of MS Office.

However, what you are looking for is very simple and you should have no problem implementing this yourself if you follow the steps below:

For a start we'll assume that Windows has assigned the following letters for your internal \ external drives.

C:\ for your hard drive on which Windows itself is installed.
\ for your optical drive.
E:\ (or a letter lower down the alphabet) for your USB drive.

We'll also assume that you have a folder called 'Excel_Workbooks' on the root of both your C:\ and E:\ drives. The full path to this folder will therefore be either C:\Excel_Workbooks\ or E:\Excel_Workbooks\

At this point in time fire up Excel, by default it should have a workbook called Book1 already opened for you.

On the 'Tools' menu Select 'Macro' -> 'Record New Macro'

A dialog box will pop up with several options, one of which is to rename 'Macro1' to something more memorable - I renamed it to 'DoubleSave'. Leave the other options as they are for the time being, but just take note of the 'Store Macro In' [This Workbook] option which I will get back to later.

Now click 'OK'. The dialog box will disappear and be replaced with a small form on which there is a stop button. From this point onwards Excel will be waiting for you to perform actions which it will record for later playback via the macro.

Click the 'Save' icon on the toolbar, navigate to C:\Excel_Workbooks\, rename your workbook to something more informative than 'Book1', and click 'Save'. At this point in time Excel has recorded enough of your actions to perform the functionality you require so click the 'Stop' button.

But I bet you're saying to yourself '...I've only saved it in one place'. Don't worry, we're going to take care of that now by editing the macro we've just recorded.

Click 'Tools' -> 'Macro' -> 'Macros'. A dialog box will pop up which lists all the macros associated with your workbook - at this point in time there should only be the one called 'DoubleSave' (or whatever name you gave it). Click 'Edit' and Microsoft's VBA (Visual Basic for Applications) editor will open up. In the right-hand pane of the editor you will see the programming commands which make up your macro.

The text in green at the top are comments which you can safely ignore. To achieve the full functionality of your macro all we need to do is copy the entire chunk of code below the comments and paste this copy underneath the first chunk as I have below.

ChDir "C:\Excel_Workbooks"
ActiveWorkbook.SaveAs Filename:="C:\Excel_Workbooks\DoubleSaveTest.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ChDir "E:\Excel_Workbooks"
ActiveWorkbook.SaveAs Filename:="E:\Excel_Workbooks\DoubleSaveTest.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

In the second chunk of code change the drive letters from C:\ to E:\
Click 'Save' and then exit the VBA editor.

To run your macro go back to 'Tools' -> 'Macro' -> 'Macros' and click 'Run'. The first time you do this you will get one pop up window warning you that a file already exists in 'C:\Excel_Workbooks', every time thereafter you run the macro you will get two warnings, one for C:\Excel_Workbooks and one for E:\Excel_Workbooks.

We can tweak this macro a little more, but that's it for the moment, I'm going for a cup of tea

Let me know how you get on?

Risteard

Thanks for the replies, and sorry for the late reply.

I'm currently using synctoy as suggested above but I'll give that macro a try when I have more time.

Again, Thanks for your help.