Link to home
Start Free TrialLog in
Avatar of redekopmfg
redekopmfgFlag for Canada

asked on

Change link source on 1000 + excel workbooks

We have over a thousand Excel workbooks that we have to move to a new server as part as an upgrade.  Each of these workbooks have vLookup links in them to another shared workbook.  

Now that these workbooks have moved, this link location is now wrong.  can a script be developed to open each of these workbooks and change the source of these links to the new location.  all the workbooks are in the same folder, and the source is the same in each as well.

current location is \\serverA\jobs\...... new location is \\serverB\jobs\.....

as a furher complication, this same link location is used inside a vba module in each of these workbooks.  it is not as important to update these, but it would be nice.
Avatar of aikimark
aikimark
Flag of United States of America image

please post a sample workbook
Avatar of redekopmfg

ASKER

I was able to record a macro that updates the links, and I can run it every time I open one of the workbooks.  works good, but takes a long time.

What I really need is to get a script that traverses through all the folders opens the workbook then executes the macro when the workbook opens then save and close.

If someone can help with this, that would be great.  The structure is always the same, but the names are different.

the parent folder is called "Job Files"  then in there, there is a folder for each of the jobs...ie. "JF11234".  then within that folder, there is the excel workbook that is named in reference to its parent folder...ie. "11234Readme.xls"

is that doable?
Are the files .xls (up to XL 2003) or .xlsm (2007 and above)?  For the later format I have written a utility which can bulk update links very quickly.  It would not do the macro code though.
They are all .xls (2003) files! :(

We are using office 2010 for the most part though
* You can run VBA code in any office application to do the update.
* You can run VBScript code to do the update
* I wouldn't go the route of a workbook_open event.
* still waiting for you to post a workbook
@redekopmfg

please post a workbook
sorry for the delay........50,000 other things going on....... :)
11630---110819-Erick.zip
Now that I can see your workbook, I observe the following:
* There are two worksheets
* all the external references are on server "\\main"

=============
You need to change all the "\\main\" strings in all worksheets to some other server name in all .xls workbook files in a directory tree?
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Aikimark!  along with what I had figured out, this got me there!
Did you do the server name code changes yourself?