VBA xl2003- Edit Links, Change Source

[iainmartin100]

Hi,

Iv'e searched the forum but unfortunatly can't find the answer, hoping someone can help.

If in the current workbook I have a link to another workbook how can I have a marco to bring up the Edit>Links dialog box and then selects change source?

To make life easier say I have the link is in Sheet1,cell A1 - do I need to refrance this cell to extract the link? and how.

Basiclly I want to do the below, the only issue is that the current link name 123.xls may change and the new link 456.xls will be user difined:

ActiveWorkbook.ChangeLink Name:= _
"C:\123.xls", _
NewName:= _
"C:\456.xls", _
Type:=xlExcelLinks

Any help would be really appriciated.

 

[iainmartin100]

I have managed to solve the problem I have posted the resolution below to help if anyother people have a simular issue. Thanks

Sub Link_Update()
ChDrive "c"
ChDir "c:/"
alink = ThisWorkbook.LinkSources
If IsEmpty(alink) Then
MsgBox "No links found"
ElseIf UBound(alink) = 1 Then
Application.Dialogs(xlDialogChangeLink).Show alink(1), "", 1
Else
MsgBox "More than 1 link found"
End If
End Sub