Getting Current Link Value
 

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
..

Getting Current Link Value
 

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 wrote:

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

Getting Current Link Value
 

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.


"Dave Peterson" wrote in message
...
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 wrote:

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