redekopmfg
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.
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.
please post a sample workbook
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?
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.
ASKER
They are all .xls (2003) files! :(
We are using office 2010 for the most part though
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
* 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
please post a workbook
ASKER
sorry for the delay........50,000 other things going on....... :)
11630---110819-Erick.zip
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?
* 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Aikimark! along with what I had figured out, this got me there!
Did you do the server name code changes yourself?