Get Path for Linked Pictures in Spreadsheet

L

Lazzaroni

Does anyone know how to return the full path of the file to which a linked
picture in Excel points to?

I use the following code to insert images into my sheet:

Set oPicture = ActiveSheet.Shapes.AddPicture(sFilename, LinkToFile:=msoTrue,
SaveWithDocument:=msoFalse, 10, 10, 100, 100)

I need to be able to get the filename of the picture that Excel is linking
to after images have been inserted into a spreadsheet.

Thank you for your help.
 
N

NickHK

Having a quick look at this now, I find:

- Using Shapes.AddPicture, the Edit>Links is greyed out and OleObjects.Count
=0. Not surprising really, as you are not using the OLEObjects collection.
It seems then that Excel handles the linking without exposing the link's
properties.

- Using
Dim NewOLE As OLEObject
Set NewOLE = ActiveSheet.OLEObjects.Add(Filename:="C:\Sample.jpg",
Link:=True, DisplayAsIcon:=False)

I could initially only get a Package to diplay, not the graphic. After
reading:
http://support.microsoft.com/kb/903177
and installing Photo Editor, I now get a white rectangle, the size of the
graphic, but not the graphic itself. Activating the graphic, I can see it in
MS Photo Editor. If you can fix that, you should be OK.

Now that you actually have an OLEObject, you can check each one. Note that
you need error handling (or some method to check the type of OLEObject) as
not all OLEObjects will have a .SourceName property.

Private Sub CommandButton2_Click()
Dim OLEObj As OLEObject

On Error Resume Next
For Each OLEObj In Worksheets(1).OLEObjects
Debug.Print OLEObj.SourceName
Next

End Sub

NickHK
 
L

Lazzaroni

Nick:

I was able to retrieve the SourceName when I insert the image as a linked
OLEObject, but as you said, linked OLEObject images do not display correctly,
even after I installed Photo Editor.

Even if I could get the image to display correctly, I don’t know that I
would use OLEObjects. It was a simple matter for me to install Photo Editor
on my test computer, but it would be another matter altogether to install it
on all of the computers that use my software, because some of them are
permanently in overseas locations.

It’s really unfortunate that there is no way to acquire the source name for
ShapeType msoLinkedPicture objects. That information must be stored somewhere
in the document. If only I could get to it.

Thank you for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top