Solved: Modifying workbook links

[Ruckley]

Hi,

I have a macro that imports a worksheet from another user's workbook.
What I find is that there are links copied over and that this is undesirable.
I need a code to remove the links, but leave the formula.

e.g. To change

='C:\Documents and Settings\My Documents\[Workbook_demo.xls]sheet1'!$D$8

to

= sheet1!$D$8

This is complicated by the fact that I don't know what the name of the path will be so I can't string match.

Any help gratefully received.:bow:

[lucas]

Welcome to VBA Express.

Wondering why you wouldn't want to just break the links and leave the value in the cell instead of a formula that references a sheet that might not be there or the data might not be there.

[Ruckley]

The sheet and cell reference are part of a workbook, the structure of which is fixed. This is for sharing data between users using the same workbook (which may be named differently) in different locations.

[ZVI]

Hi,

Try this:


Sub ChangeLink()
Const WbName = "Workbook_demo.xls" ' <-- the name of the source (linked) Wb
Dim Lnk, Sh
On Error GoTo exit_
With ActiveWorkbook
For Each Lnk In .LinkSources(Type:=xlExcelLinks)
If UCase(Lnk) Like UCase("*[" & WbName & "]*") Then
.ChangeLink Name:=Lnk, NewName:=.Name
For Each Sh In .Worksheets
Sh.Calculate
Next
Exit For
End If
Next
End With
exit_:
'If Err <> 0 Then Debug.Print "No links"
End Sub

Regards,
Vladimir

[Ruckley]

Hi Vladimir, this would work fine, but I don't know what the other user could have changed their workbook name to.

[ZVI]

I have a macro that imports a worksheet from another user's workbook.
So, you can recognize the name of that another user's workbook in your macro code and pass it to the modified Sub ChangeLink1:


Sub ChangeLink1(WbName As String)
Dim Lnk, Sh
' here is the previous code which is below the Dim statement in the example of post#4
End Sub

Another way is in changing of all external links, surely if it’s applicable for your task:


Sub ChangeLink2()
Dim Lnk, Sh
On Error GoTo exit_
With ActiveWorkbook
For Each Lnk In .LinkSources(Type:=xlExcelLinks)
.ChangeLink Name:=Lnk, NewName:=.Name
Next
For Each Sh In .Worksheets
Sh.Calculate
Next
End With
exit_:
'If Err <> 0 Then Debug.Print "No links"
End Sub

Just let us see your code if you stuck with its tweaking.

Vladimir

[Ruckley]

Вы гений

It works. Thank you sir, I am in your debt.
:bow:

[ZVI]

Вы гений
Wow, my native Russian words - thanks!
There is a lot of geniuses here, but I'm just clever :biggrin:
Best Regards,
Vladimir
:beerchug: