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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,797
Office Version
  1. 2016
Platform
  1. Windows
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 :

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try this which works for me

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

Code:
Sub CallFunction()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        Debug.Print obj.Name, GetLinkSource(obj)
    Next
End Sub
 
Upvote 0
or select a linked object and this returns the path to message box

Code:
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
 
Upvote 0
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.

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top