Excel: Changing Inter-Spreadsheet Links 1

[B827]

I have been asked a question regarding the following problem. A department have set up a number of linked spreadsheets which include the server name in the link. Due to upgrading and rationalisation the server locations are being changed. Refreshing all these links is going to be a major task. So the question is does anybody know a quick and simple way of making the changes?

Many thanks
Sandy

 

[mmtraining]

How about find and replace?

It means the users will have to be a bit careful and not hit replace all or anything silly like that, but it should work. I use find and replace to change the contents of formulae.

Carol
Berlin, Germany

carol@fernschulung.de

 

[B827]

The problem is, which I should have mentioned in the first post, there are 300+ sheets and some have macros linking to specific servers. It's a real nightmare. Sandy

 

[mmtraining]

You poor thing!

I'd create a new file with a bit of VBA in it which looks at each file in a certain directory, opens it, does find and replace on each sheet in that file, saves and closes it.

Go to the VBA forum and see if anyone can help you there. I'm afraid I'm running round like a chicken without a head at the moment, am off on holiday in six hours, which has caused every colleague within miles to scream in panic and bring the 400 little problems he had been saving up for this sort of time.

If you don't get any answers by tomorrow, I'll have a think.

Good luck

Carol
Berlin, Germany

 

[B827]

Carol,
Thanks for putting in the effort when you're busy. i know what you mean this change has been planned for a while, the move happens at the weekend and the problem was identified last night, the sheets HAVE to be working on Monday.

I was thinking that code would be the only solution. Longer term this looks like some sort of database to me. Sandy

 

[mmtraining]

Oh sugar.

Which version of Excel have you got, What is your time zone? I'm in MET

Carol

 

[B827]

I'm in the UK using Excel97, but don't get too distracted from your real work as we can always postpone shutting the old server for a couple of days while we sort the problem. Sandy

 

[euskadi]

If the only issue is that the spreadsheets are moving... you can open the main workbook (don't update links) then choose Edit, Links, Change Source.

If you have a bazillion workbooks to process, then you can do this (quoting loosely from the Book of Help).

You can use this to enumerate the links in a workbook:

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
        MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
    Next i
End If

Once you can grab the link, with a little text manipulation you should be able to derive the new link path from the old link path, and then change the link like this:

ActiveWorkbook.ChangeLink Name:= oldFullPath, NewName:= NEWFullPath, Type:= xlExcelLinks

Then if you want to go all out, you can write something that will open up every *.xls nn a certain directory and perform the re-link action, then save and close the workbook.

 

[B827]

euskadi,
I am about to go home now, I'll try this on Monday first thing. If it solves the problem then I owe a star if not two!!

Many thanks

Sandy