9000 External Workbook Link updates

[Exsilium]

Hoping you guys can help me... im out of ideas...

Basics: (excel 2007) I have a file with over 9000 links in it. All of these links need to be replaced because the files are being moved to a new NAS location.

Problem: Using search and replace, each of the link updates takes about 50 seconds to update as Excel goes out and verifies the new path. Do the math... and to complicate things further, I have 7 of these files to update.

Solution: I need to make excel NOT verify the path when its changed.

Failed Ideas:

1. Options - Advanced - When calculating this workbook - unchecked Update links to other documents. Failed.
2. Options - Trust Center - Trust Center Settings - External Content - Set both to disabled. Failed.
3. Data Tab - Edit Links - attempted to set Item Update to Manual, but that radio option is greyed out and its stuck on Automatic. I dont know how to enable the Manual option.

 

[AlphaFrog]

I haven't tested this but I think it might help.

With your workbook with all the 9000 links open (let's call it Book1 for this example), also open the other workbook(s) that it is currently linked to (let's call those workbooks Linked1, Linked2, etc.)

Then SaveAs the Linked1, Linked2, etc., workbooks to the new location.

Excel should automatically update all the links in Book1 each time you save a Linked file to its new location and it should be relatively fast.

 

[Exsilium]

I haven't tested this but I think it might help.

With your workbook with all the 9000 links open (let's call it Book1 for this example), also open the other workbook(s) that it is currently linked to (let's call those workbooks Linked1, Linked2, etc.)

Then SaveAs the Linked1, Linked2, etc., workbooks to the new location.

Excel should automatically update all the links in Book1 each time you save a Linked file to its new location and it should be relatively fast.

Great idea... but... there are over 1000 external workbooks this workbook links to, with 9 links per external workbook. Opening 1000 workbooks at once would be... problematic.

Ive been digging around for a way to enable the greyed out Manual option in the edit links dialog window... seems to be a common problem and I have yet to find a solution.

 

[Exsilium]

Great idea... but... there are over 1000 external workbooks this workbook links to, with 9 links per external workbook. Opening 1000 workbooks at once would be... problematic.

Ive been digging around for a way to enable the greyed out Manual option in the edit links dialog window... seems to be a common problem and I have yet to find a solution.

I should mention that I have mTools add-in. That would update all my links with zero effort... its the time that im worried about. Taking 50 seconds per link to update is a massive 150 hour process just for this one file... and i have 7 such files. I need excel to stop verifying the new path... its simple but how?

 

[CWatts]

Well, your NAS will have to be mapped to a certain drive letter. Determine what the drive letter is for your NAS and disconnect.

Set up another drive in your computer, or maybe a fast flash drive, and make sure the drive letter is the same as your NAS is going to be. Update your workbook with find/replace so all links will point to that drive letter. Close workbooks, rename the drive letter back for your USB drive or whatever, and hook up your NAS again with that drive letter.

Maybe that will work for you.

 

[AlphaFrog]

Just throwing out another idea...

Would it help if your workbook with all the links was saved in the new directory path or folder as the 1000 external workbooks. Then you use mTools to update the links. Is that faster? After the links are updated, you can move the file if needed.

 

[Exsilium]

Well, your NAS will have to be mapped to a certain drive letter. Determine what the drive letter is for your NAS and disconnect.

Set up another drive in your computer, or maybe a fast flash drive, and make sure the drive letter is the same as your NAS is going to be. Update your workbook with find/replace so all links will point to that drive letter. Close workbooks, rename the drive letter back for your USB drive or whatever, and hook up your NAS again with that drive letter.

Maybe that will work for you.

Im not the only one that uses these files unfortunately so I can not make the path use a specific drive letter, but a fully qualified path. There is no way to know what all the other users will have as a drive letter.

 

[Exsilium]

Just throwing out another idea...

Would it help if your workbook with all the links was saved in the new directory path or folder as the 1000 external workbooks. Then you use mTools to update the links. Is that faster? After the links are updated, you can move the file if needed.

This is already the case actually. The rollup for that specific DC is in the same folder as all the elevations for that DC excepting that each DC is split into several zones and each Zone is in its own folder.

 

[Exsilium]

/bump