Migration issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jayboy4 - 21 Jan 2004 14:53 GMT

Hi,

We a planning a big data migration.
One big problem we have is that we have hundreds of spread
sheets with embedded links that unc paths to other
spraed sheets.

Does anyone know if there is a utilty that will detect
which excel spread sheets have a Macro and then modify
the
UNC path to a new location.

Thanks

Reply to this Message

Bill Manville - 22 Jan 2004 00:55 GMT

> Does anyone know if there is a utilty that will detect
> which excel spread sheets have a Macro and then modify
> the
> UNC path to a new location.

Not sure you meant to say "Macro" there, did you mean "Link"?

If so, email a request for my prototype Link Manager to me at
Bill_Manville @ compuserve.com
(without the spaces).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Michelle - 01 Feb 2004 13:36 GMT

I think this is the same issue as mine but I'd like to have a crack at describing it and ask your prototype link manager might suit...

In file C:\logistics\delivery.xls, column A contains customer number and column B contains a delivery address lookup.
In file C:\logistics\customer.xls, column A contains customer number and column C contains an address.

In file C:\logistics\delivery.xls, cell B2 = vlookup(A2, 'C:\logistics\[customer.xls]Sheet1'!$A$A, 1, false)

I move the entire contents of C:\logistics to H:\widgets\shipping.

Now, in the file H:\widgets\shipping\delivery.xls, cell B2 still equals vlookup(A2, 'C:\logistics\[customer.xls]Sheet1'!$A$A, 1, false)
i.e it's broken because it needs to equal vlookup(A2, 'H:\widgets\shipping\[customer.xls]Sheet1'!$A$A, 1, false)

And the big problem is there's many such references. How can I repair it?

Thanks.

Reply to this Message

jaf - 01 Feb 2004 14:10 GMT

Hi Michelle,
Worksheet links?
The easiest way to prevent this (or fix) is to open all the workbooks
involved.
Then rename the file in question by doing a file>save as
H:\widgets\shipping\customer.xls.
Close Excel and delete C:\logistics\customer.xls.

Excel will change the links automatically even without deleting the c: file,
but if you reopen the c: file there can be problems.

Signature

John
johnf 202 at hotmail dot com

| I think this is the same issue as mine but I'd like to have a crack at describing it and ask your prototype link manager might suit...
|
[quoted text clipped - 11 lines]
|
| Thanks.

Reply to this Message

Bill Manville - 02 Feb 2004 00:12 GMT

> And the big problem is there's many such references. How can I repair it?

If all the references are from one file to a small number of files, the
easiest fix is to use
Edit / Links / Change Source

If there are many different references in many different files then Link
Manager will help you. Email me for a copy if so ( Bill_Manville @
compuserve.com ) without the spaces.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Michelle - 02 Feb 2004 01:26 GMT

----- Bill Manville wrote: -----

Michelle wrote:
> And the big problem is there's many such references. How can I repair it?
>

If all the references are from one file to a small number of files, the
easiest fix is to use
Edit / Links / Change Source

Brilliant! Thanks Bill. Too easy eh!:)

If there are many different references in many different files then Link
Manager will help you. Email me for a copy if so ( Bill_Manville @
compuserve.com ) without the spaces.

Won't be necessary today - maybe in the future.

Cheers
Michelle

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message