DataMigration - external links

[dh_ander]

Hi

I need some help to tackle the following problem.
I need to migrate lot's of data from old NT4 servers to a new w2003 environment.
Problems are expected with excel sheets that contain links to other excel sheets or other objects.
Before the migration I want to scan all my excel files and list the ones that can give problems.
Any suggestions how I go about this.
Thanks in advance

 

[dh_ander]

Help

 

[GlennUK]

What problems are expected exactly?

Do you know which files have external links? They would be the ones that would cause most trouble when migrating .... depending on what problems you are expecting of course.

 

[dh_ander]

No I don't which files contain external links, that's why I want to run some utility and tell me which files contain external links.

The problem with external links is dat if you migrate files from one data area to another these links now refer to some old (=now broken) link and sheets don't get updated anymore correctly so I would like to inform the users regarding which files we speak.

 

[GlennUK]

The problem with external links is dat if you migrate files from one data area to another these links now refer to some old (=now broken) link and sheets don't get updated anymore correctly so I would like to inform the users regarding which files we speak.

That problem only occurs if the design of the hierarchy doesn't allow automatic link tracking. If the supporting files for any Excel book are in the same folder as, or a lower folder than the Excel book, copying will not affect links.

I don't know of any utility to list what links each file has.

 

[dh_ander]

The following add-in http://www.oaltd.co.uk/MVP/Default.htm

Is very neat for finding links in an open excel sheet but not practical if you have to chek over 1000 excel sheets

 

[Richard Schollar]

David

The LinkSources Method in VBA could be of use to you - you could set up a utility to go thru excel files in a directory and list their links. This wouldn't be fast (I believe you'd need to open all files - which can be programmed into the utility) but as long as it wouldn't cause you a problem to leave it running on a specified machine, then at least you'd have your list of files with links.

Best regards

Richard

 

[dh_ander]

David

Thanks for your reply, since my skills in vba are still somewhat limited could you elaborate a bit on "The LinkSources Method in VBA"?

perhaps a little sample to start with?

 

[Richard Schollar]

Hi David

I've left you a PM - I think I may have something which can help.

Richard