Link Updating Program Needed

[Keith Paget]

I would like to automatically update all the file links
for the following scenario.
"Master Budget" workbook (call it file A) has direct
links to 150 or so "Model Budget" workbooks (call them
files B, C, D, E and so on). These 150 or so "Model
Budget" workbooks have Vlookup links to "Master Price
List" workbook (call it file Z) holding prices used in
each of them.
The problem I have is when I update prices in file Z,
links in files B, C, D, E and so on are not updated until
they are opened. So I have to manually open each one of
150 or so 4MG files to get them updated so the new cost
totals are relayed into file A (my Master Budget).
(Files B, C, D, E and so on also have a "password to
modify" because I use them as templates and can't afford
someone accidentally changing them.) I would like to
automate this procedure so I can have file B open, enter
the password to modify, update its links to file Z, save
and close. Then this procedure would then repeat
automatically for file C, D, E and so on until
all "Master Budgets" are done and then it would stop with
a message saying "Updating complete". Then when I open
file A and update its links with all files B, C, D and so
on I will get the new updated information I require.
(PS; I also copy the entire file set every month and
rename the folder "January, February" and so on. I need
a history of each months files)
Thank you for any information that will help make this
lengthy process a shorter one.

 

[Nicky]

Hi Keith

assuming all your files are stored in the same directory, and that you
master price workbook and master account workbook are already open
some variation on this might work:

Sub update_files()
'directory the files are in:
filepath = "c:\temp\temp\"
'make an array with the model budget file names:
filenames = Array("workbook1.xls", "workbook2.xls", "workbook3.xls"
"workbook4.xls", "workbook5.xls")
‘open, calculate, close the model budget files:
For n = LBound(filenames) To UBound(filenames)
Workbooks.Open Filename:=filepath & filenames(n)
updatelinks:=True, password:="password", writerespassword:="password"
Calculate
Workbooks(filenames(n)).Close SaveChanges:=True
Next
MsgBox "update completed"
End Su

 

[Keith Paget]

Thank you Nicky, Yes, I have all the files stored in the
same directory and at the same level and I can have the 2
master workbooks open during the process which are also
in the same location. I will give the code you supplied
a try and post my success or not in a few days.

-----Original Message-----
Hi Keith

assuming all your files are stored in the same directory, and that your
master price workbook and master account workbook are already open,
some variation on this might work:

Sub update_files()
'directory the files are in:
filepath = "c:\temp\temp\"
'make an array with the model budget file names:
filenames = Array

("workbook1.xls", "workbook2.xls", "workbook3.xls",

 

[Keith Paget]

I entered the code and not knowing alot about macros yet,
tried to guess the info I have filled in. Here is a copy
I ended up with. (it hangs up on the first command "Sub
update_files()"

Sub File_Link_Updating()
'
' File_Link_Updating Macro
' Macro recorded 3/4/2004 by Keith Paget
'
' Keyboard Shortcut: Ctrl+q
'
Sub update_files() ***It hangs up at this line***
'directory the files are in:
filepath = "F:\Test Copy-March 2004-Master Program"
'make an array with the model budget file names:
filenames = Array("PD - Ash Creek.xls", "PD - Birch
Creek.xls", "PD - Cherry Creek.xls", "PD - Maple
Creek.xls")
'open, calculate, close the model budget files:
For n = LBound(filenames) To UBound(filenames)
Workbooks.Open Filename:=filepath & filenames(n),
updatelinks:=True, password:="estimating",
writerespassword:="estimating"
Calculate
Workbooks(filenames(n)).Close SaveChanges:=True
Next MsgBox "update completed"
End Sub

 

[Nicky]

Hi Keith

Iit looks like you have copied the start of my code, including the Su
update_files() start code, into your code. VB will run code between su
***() and end sub, and will stop if you try to start a new sub in th
middle.

Try deleting the Sub update_files() line from your code. If you hav
copied my End Sub at the end as well, delete the duplicate.

You will also needto add a slash \ at the end of your filepath:
F:\Test Copy-March 2004-Master Program\

Its not clear from this page, but
MsgBox "update completed"
should be on a new line, not in the same line as 'next'

see if that works.
Nick

 

[Keith Paget]

Hi Nicky,

I will try to follow your thoughts here. I haven't had
time to work with yet since the last time. Hopefully
tomorrow I will have time.

Keith