Push updates to other workbooks?

[ChrisP]

I have a master workbook and 5 different workbooks that have links to the
master workbook. I have to password protect my master workbook as it has
salaries and not everyone should be allowed to see those. I would like to
make an update to the master workbook and have the update be "pushed down"
into the other workbooks, can this be done?

I don't want users to open the other workbooks and select update because
they then need to know the password to the master workbook, which would mute
the point of having the master workbook being password protected.

Thanks!!!

 

[Mike H.]

A simple macro like this should work. The macro would reside in the master
and would be run after you've updated the master. The links should update
automatically.

Sub UpdateLinks()
'Update First Workbook
Workbooks.Open Filename:="C:\Sub1.xls"
ActiveWorkbook.Save
ActiveWindow.Close


'Update Last Workbook
Workbooks.Open Filename:="C:\Sub2.xls"
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[Nenad @ Bästconsulting]

You could write a "update file" with a macro (VBA) that includes the passord
and path to your master file from which their individual 5 files can copy the
data and paste into their workbooks where the data belongs.
This way you could have your master file centrally located for you to modify
and they can re-use the "update file" to get the updates.

 

[ChrisP]

This is wonderful!!! Thanks so much! One other question, how do I write it if
the other workbooks are password protected?

Thanks again!

 

[Mike H.]

Assuming the passwords are "Frogs", the code is:

Workbooks.Open Filename:="c:\Sub1.xls", Password:="Frogs",
UpdateLinks:=3, WriteResPassword:="Frogs"

 

[ChrisP]

Great!!! Now my last question, how would I get this code to run when the user
closes the workbook?

Thanks again for all the wonderful answers!!!

 

[Mike H.]

Go into the VBA editor and on the tree on the left, click the ThisWorkbook
under the Microsoft Excel Objects and then over to the right select the
"Workbook" pulldown and then select the "BeforeClose" pulldown to the right
of that. There will now be a sub created:
Private Sub Workbook_BeforeClose(Cancel as Boolean).

Place the code there inside the Sub.

 

[ChrisP]

I did that and now I'm getting an error that says "Compile error: Member
already exists in an object module from which this object module derives".
What am I doing wrong?

 

[ChrisP]

I figured it out, nevermind. Thanks for all the help!!! I really appreciate
it!!!

 

[Dave Peterson]

You can only have one workbook_beforeclose event.

You'll probably want to merge the two into one procedure.

 

[ChrisP]

Last question (I hope)... when I use this code the files physically open,
update, save then close. Is there anyway to have all this run in the
background so the user doesn't see it?