Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove link to another workbook | New Users to Excel | |||
Changing cell references | Excel Discussion (Misc queries) | |||
how can i link sheets in my excel workbook | Excel Worksheet Functions | |||
Can't link cells in worksheets in same workbook | New Users to Excel | |||
How do I link sorted data to other workbook sheets? | Excel Worksheet Functions |