How to retrieve the path and name of a linked OleObject ?

[Jaafar Tribak]

Hi all,

Let's say we have an embeeded and linked document in a worksheet - Does anybody know of a VBA method for retrieving the link source name ?

I am referring to the actual document full path name that is displayed on the oleobject icon or in the formula bar when the oleobject is selected.

I was thinking something like the following function :

Function GetLinkSource(ByVal EmbedeedObject As OLEObject) As String

End Function

The workbook LinkSources Method returns an array containing the source names of all the linked objects but I am after the source name of just the oleobject that I choose.

I guess I could have VBA select the oleobject and brute-force reading the text in the formula bar via using SendKeys or some API calls but that is just too ugly and may not always work.

I have done some research but nothing has come up so far.

Regards.

 

[Yongle]

Try this which works for me

Function GetLinkSource(ByVal EmbedddedObject As OLEObject) As String
    Dim objLink As String
    With EmbedddedObject
        If .OLEType = xlOLELink Then objLink = Replace(Replace(.SourceName, "Package|", ""), "!'", "")
    End With
    GetLinkSource = objLink         
End Function

Sub CallFunction()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        Debug.Print obj.Name, GetLinkSource(obj)
    Next
End Sub

 

[Yongle]

or select a linked object and this returns the path to message box

Sub SelectedObject()
    Dim obj As OLEObject
    If TypeName(Selection) = "OLEObject" Then
        Set obj = ActiveSheet.OLEObjects(Selection.Name)
        If obj.OLEType = xlOLELink Then MsgBox Replace(Replace(obj.SourceName, "Package|", ""), "!'", ""), , obj.Name
    End If
End Sub

 

[Jaafar Tribak]

Hi Yongle,

Thanks very much for answering .

Your code works great !

I completely forgot about the SourceName Property and I was already writing some rather complicated COM code.

You don't even have to select the oleobject, this simple function worked just fine for me.

Function GetLinkSource(ByVal EmbedeedObject As OLEObject) As String
    If EmbedeedObject.OLEType = xlOLELink Then
        GetLinkSource = Replace(Split(EmbedeedObject.SourceName, "|")(1), "!'", "")
    End If
End Function

Sub Test()
    MsgBox GetLinkSource(Sheet1.OLEObjects(1))
End Sub

Thanks again.

 

[Yongle]

Good - thanks for the feedback