View Full Version : Solved: Modifying workbook links
Ruckley
02-22-2010, 07:06 AM
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
02-22-2010, 08:53 AM
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
02-22-2010, 09:10 AM
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.
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
02-23-2010, 09:16 AM
Hi Vladimir, this would work fine, but I don't know what the other user could have changed their workbook name to.
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
02-24-2010, 06:27 AM
Вы гений
It works. Thank you sir, I am in your debt.
:bow:
Вы гений
Wow, my native Russian words - thanks!
There is a lot of geniuses here, but I'm just clever :biggrin:
Best Regards,
Vladimir
:beerchug:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.