Changing Links in VBA

[jw191]

Hi All,

I have a worksheet filled with formulae to gather data from another excel workbook. For example:

=IF('C:\Documents and Settings\admin\Desktop\Current Work\Forecasts\[Forecast1.xls]Team Members'!B6=0,"",'C:\Documents and Settings\admin\Desktop\Current Work\Forecasts\[Forecast1.xls]Team Members'!B6)

I am trying to write a macro that will duplicate this sheet but change all the external links to a different document (eg ie need to change

C:\Documents and Settings\admin\Desktop\Current Work\Forecasts\[Forecast1.xls]

to

C:\Documents and Settings\admin\Desktop\Current Work\Forecasts\[Forecast2.xls]

in all cells keeping the rest of the formula the same.

The macro I have so far:

**********************************MACRO*******************

Sub TestDup()
'
' TestDup Macro
' Macro recorded 19/01/2007
'

'Get Files Location

Dim strName As String

strName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
1, "Select Worksheet to add", , False)

'If Nothing Entered Abort
If Not strName = ("") Then

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)

'Change Filepath

'Skipped to here if no data was entered
Else

MsgBox ("No Data Entered - Creation Aborted")

End If
End Sub

******************************END MACRO*******************

I have tried recording macros to see if the replace function in the edit menu could be used but with no luck. The other problem is the dialgue called in the macro returns:

C:\Documents and Settings\admin\Desktop\Current Work\Forecasts\Forecast2
.xls

i.e. without the square brackets required around "Forecast2.xls"

Please can anyone please help?