Getting Current Link Value

[Dkline]

I have a "Core" workbook which several workbooks can use. Right now I have
to physically open the Core workbook and point it to the whichever workbook
it should be looking at now.

How can I determine what is the Core workbook currently linked to using VBA?
The ActiveWorkbook.ChangeLink takes as its first argument the name of the
workbook to which it is currently linked.which I have to replace.

The below is from a recorded macro. My hangup is getting the name of the
existing workbook for that first argument.

ActiveWorkbook.ChangeLink Name:="Jamie11032005XXX.xls", NewName:= _
"PremiumFinancingJamie.xls", Type:=xlExcelLinks
..

 

[Dave Peterson]

If you know that you're only linking to one workbook, you could use something
like:

Option Explicit
Sub testme()

Dim aLinks As Variant
Dim iCtr As Long

aLinks = ThisWorkbook.LinkSources

If IsEmpty(aLinks) Then
'no links
Else
MsgBox aLinks(LBound(aLinks))
'or loop through them
'For iCtr = LBound(aLinks) To UBound(aLinks)
' MsgBox aLinks(iCtr)
'Next iCtr
End If

End Sub

(Mostly taken from VBA's help for Linksources.)

 

[Dkline]

It took me a while to get this right but it is working now. I was forgetting
that LinkSources returns an array and that I had to index into the array
even though there was only one value for all the links. I have hundreds of
cells linked between workbooks but they come from only one source.

Thanks for your help.