Save file in 2 different places Excel for Mac

April 15, 2010 at 12:19:11
Specs: Macintosh
How can I save a file in two different folders that, whenever I open it from either, and save it, the changes will be saved in both files.
Thanks. Saul

See More: Save file in 2 different places Excel for Mac

Report •


#1
April 15, 2010 at 13:04:56
Not sure this will work on a Mac, but it does on my Excel 2000, XP. (Caveat Emptor)

You can Link two workbooks the same way you link worksheets.

Open WorkbookA
Open WorkbookB

In cell A1 in WorkbookB you will need to put a formula like:

=[BookA.xls]Sheet1!$A$1

Which contains the Book Name, Sheet Name and Cell reference.

Now as long as both books are open,
the B workbook will automatically update when you enter data into A.

Now if B is closed, then upon opening B the first time, you will get a message asking if you want to update.

MIKE

http://www.skeptic.com/


Report •

#2
April 15, 2010 at 13:21:10
Did a bit more testing and it seems if the two workbooks are NOT in the same directory, it won't work. Sorry.

MIKE

http://www.skeptic.com/


Report •

#3
April 15, 2010 at 13:53:45
A bit more testing again, and it seems you need to supply the complete path name in Workbook B, something like:

='C:\Documents and Settings\Owner\My Documents\[BookA.xls]Sheet1'!$A$1

Even then it's not guaranteed to work.

Someplace along the line, Workbook B is modified, so the complete path name is removed.

Perhaps someone else will have an answer.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 15, 2010 at 15:03:32
Ok, this is getting a bit weird.

If Workbook A and Workbook B are in separate directories,
then you need to supply the complete path name in Workbook B IE:

='C:\Documents and Settings\Owner\My Documents\[BookA.xls]Sheet1'!$A$1


After entering data into Workbook A, saving and closing Workbook A
if you then navigate to Workbook B, open it, it will ask you if you wish to update.
Answering yes updates Workbook B to reflect the changes in Workbook A.

Now here is the strange part,

If you have Workbook A closed and open Workbook B, the formula will display as shown above with the complete path, but if you now open Workbook A, while also having Workbook B open, then the formula reverts to the

=[BookA.xls]Sheet1!$A$1

If you then close Workbook A the formula reverts back to the complete path.

So it will work, but boy is it flakey.

MIKE

http://www.skeptic.com/


Report •

#5
April 15, 2010 at 15:46:14
Hi Mike.
Thanks for trying that.
I know what you mean. In 2008 for Mac it works the same way, if I write in cell A1 in workbook 1 the formula "=[Workbook2]Sheet1!$A$1" then every change I will write in A1 workbook 2 will be changed automatically in work 1, and then if I copy format to the whole sheet(s) I can link the whole sheet(s).
But it is pretty complicated, besides, you then can write only on workbook 2, because the moment you write in workbook 1 it erases the formula and the cells are not linked anymore.

I am looking for a way to open either of the workbooks, 1 or 2, make changes and the changes will be made also in the second workbook.


Report •

#6
April 15, 2010 at 15:52:11
You will probably need sometype of VBA solution, if there is one.

MIKE

http://www.skeptic.com/


Report •

#7
April 15, 2010 at 16:35:30
I think MS dropped the possibility of using VBA in 2008 for Mac (and even if they didn't I don't know how to do it)...

Report •

#8
April 15, 2010 at 18:04:51
Here are 2 macros that work for Windows based Excel installations. Sorry, I can't tell you how to handle this on a Mac.

The first (DualSave) opens the Save As dialog box so you can choose a location, saves the file there and then opens the Save As dialog box again so you can choose a different location.

The second (AutoDualSave) automatically saves the file to My Documents and the Desktop.

As written, both of these macros need to be run manually. However, they could be placed inside the BeforeSave event and run automatically when you choose Save.

Sub DualSave()
'
' DualSave Macro
' Macro Written 6/25/2005 by DerbyDad03
'
'Get Filename, Default = FileName
    fileSaveName = Application.GetSaveAsFilename()
'Save File to Chosen Location
    If fileSaveName <> False Then
        ActiveWorkbook.SaveAs Filename:=fileSaveName
    End If
'Do it again
    fileSaveName = Application.GetSaveAsFilename()
    If fileSaveName <> False Then
        ActiveWorkbook.SaveAs Filename:=fileSaveName
    End If
End Sub

Sub AutoDualSave()
'
' AutoDualSave Macro
' Macro Written 6/25/2005 by DerbyDad03
'
'Disable Alerts
'Don't tell the user that the file already exists
 Application.DisplayAlerts = False
'Save the file to MyDocuments
  ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\user_name\My Documents\Book1.xls"
'Save the file to the desktop
   ChDir "C:\Documents and Settings\user_name\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\user_name\Desktop\Book1.xls"
'Enable Alerts
 Application.DisplayAlerts = True
End Sub


Report •

#9
April 15, 2010 at 19:25:56
Thanks Derby, but Excel 2008 does not support macros or VBA.

Report •

#10
April 15, 2010 at 19:47:47
You'll just have to break down and learn Applescript and convert the VBA to something you can use. :-)

MIKE

http://www.skeptic.com/


Report •

#11
April 16, 2010 at 06:06:13
Well... apparently you are right (and apparently it could be a good joke if it wasn't true)... :-)
Anyway, at least something good came out of my question. I visited your site (don't know if it's YOURS, the skeptic one) and I liked it....
:-)

Report •

#12
April 16, 2010 at 07:05:08
Hey! I'm deeply hurt.

IMO, more good than just a visit to Mike's site came out of your question.

My code suggestions should give you the basics on how to accomplish your goal, as long as you can find an way of using the logic in a scripting language that is compatible with Excel 2008.

To ignore the "goodness" provided by my post cuts me to the core. ;-)


Report •

#13
April 16, 2010 at 07:40:26
Not my site, just one that I find of interest.

And DerbyDad03 is correct
if you get around to working with Applescript, the logic should work just as well,
and don't let his outrage fool you, he loves doing this stuff.

MIKE

http://www.skeptic.com/


Report •

#14
April 18, 2010 at 12:48:08
OK guys, got the picture. Rolling my sleeves and starting learning AppleScript! (Derby, I will use your macro as the platform to build my script)... :-)
(Will that do for you to stop being cut to the core?)... :-)
Thanks both of you for your help.
Enjoy this coming week.
Smiles. Saul

Report •

#15
April 18, 2010 at 19:25:47
I getting better, slowly.

Report •

#16
April 18, 2010 at 19:56:14
Phewww!!!
I AM relieved!
Man! You had me worried for awhile!...:-)

Report •


Ask Question