Mass Re-linking of Broken Excel 2000 and 2003 Files

[Guest]

We are moving customer files to a different server and have several
individuals who have MANY linked Excel 2000 and 2003 files to other Excel
files. I am getting pressure from some individuals on finding a tool to
re-link moved files more easily than on a one-by-one basis when they are
opened.

Does anyone know of anything like this?

 

[Ardus Petus]

Have a look in Help at Worbook.ChangeLink

HTH

 

[Ardus Petus]

Sorry I've been a bit abrupt.
You should consider searching microsoft.public.excel.links

HTH

 

[Guest]

That would work for doing it one by one (atleast until the source file is
updated). What these people want it to take all their files at once and have
the links reconnected to the new directory. The file name will remain the
same it is just the directory that will change. Their files don't
necessarily all link to the same file. Their links may link to different
spreadsheets throughout the directory.

Example: z:\user\excel\xxxxxxx.xls (linked to
z:\user\excel\primary\xxxxxxx.xls)
changed to r:\user\excel\xxxxxxx.xls (they want to relink to file
r:\user\excel\primary\xxxxxxx.xls

They want to take ALL the files that they have in this directory that have
links and have the links re-linked without having to open each file
separately and re-establishing all the links to all the different files.

 

[Ardus Petus]

That will require a macro that opensall files one by one, check their links,
and update them if necessary

HTH

 

[Harlan Grove]

Ardus Petus wrote...

Sorry I've been a bit abrupt.
You should consider searching microsoft.public.excel.links

Abrupt but accurate.

Changing links in .XLS file require opening those .XLS files, changing
the links, and saving the files. There is no reliable alternative.

That said, the task would be made MUCH EASIER if the OP created a table
containing all original workbook full pathnames in one column and their
corresponding new full pathnames in the next column to the right. Then
use a macro to open these files one at a time, iterate through the
workbook's LinkSources(xlExcelLinks) array, use VLOOKUP with each
link's current full pathname to find the corresponding new full
pathname from the table, use the ChangeLink method to change the links,
then save and close the workbook.

I suppose these workbooks would reside both in shared directories on
file servers as well as on users' local/nonnetwork drives. If so, IT
staff should convert the server-based workbooks. As for local
drive-based workbooks, there's no alternative to opening and changing
workbook files one at a time, but it can be automated. Still, it could
take a fair chunk of time. Up to users to decide whether to take the
time needed or fix their files on an ad hoc basis on their own.