Changing bulk Link references to different workbook.
 

We have an excel spreadsheet that has a lot (thousands) of links to data on
an excel spreadsheet on the C: drive.
e.g.
='C:\My Documents\Excel\data\[SourceData.xls]Sheet1'!$B$3

We are moving to Citrix, and Losing our C: drives, so I need to update all
references in all 16 worksheets from "C:\My Documents" to "H:\Working"

Is there a bulk method of doing this?

Thanx in Advance
Simon Westenra
Health Intelligence
Wellington, New Zealand.

Replying to my own post - tut tut!

Anyway I have tried altering some samples I found, and this appears to work.
It's not too fast, but I can live with this.

Sub RemapRefs()
Dim ws As Worksheet
Dim cell As Range
Dim Val1 As String
Dim Val2 As String

Val1 = "F:\[Excel_Reference_Test3.xls]"
Val2 = "F:\[Excel_Reference_Test2.xls]"
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Cells.Select
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Replace(cell.Formula, Val1, Val2)
End If
Next ' Cell
Next 'Sheet
End Sub

Cheers
"Simon Westenra" wrote:

We have an excel spreadsheet that has a lot (thousands) of links to data on
an excel spreadsheet on the C: drive.
e.g.
='C:\My Documents\Excel\data\[SourceData.xls]Sheet1'!$B$3

We are moving to Citrix, and Losing our C: drives, so I need to update all
references in all 16 worksheets from "C:\My Documents" to "H:\Working"

Is there a bulk method of doing this?

Thanx in Advance
Simon Westenra
Health Intelligence
Wellington, New Zealand.