2

As the title states, I haven't found a way to retrive either the name of the embedded object or its path (attached image) Sample Screenshot

In the example provided, I would like to get either the full path location or the 67CE8... name at least -since I know those are stored in the Temp folder-, so I could save them automatically by VBA. I have tried ".SourceName", without luck since it seems only to get hyperlink (but there is no real hyperlink hence err 1004).
EDIT For code that was in comments:

ActiveSheet.OLEObjects.Add(Filename:= _ "https://...dummylink" _ , Link:=False, DisplayAsIcon:=False).Select x = Selection.Name Selection.Verb Verb:=xlPrimary
Sgdva
  • 2,800
  • 3
  • 17
  • 28
  • Do you mean hyperlink something like for embedded pictures or shapes from PC. – skkakkar May 18 '16 at 04:53
  • Please post the code you are using – SierraOscar May 18 '16 at 06:06
  • Sorry! Code is: ActiveSheet.OLEObjects.Add(Filename:= _ "https://...dummylink" _ , Link:=False, DisplayAsIcon:=False).Select x = Selection.Name Selection.Verb Verb:=xlPrimary @skkakkar Kind of, I noticed after retriving the object is stored in the temp folder. – Sgdva May 18 '16 at 14:28

2 Answers2

3

Looking at the icon in your image, you have inserted an object that is a file and not ticked 'Link to file' meaning (as you referenced) it is embedded, it has no path as it exists within the file.

I found a previous question answered here but could not get this to run for me without popping up a dialog box.

There is more than likely another way to do this but in Word a had similar problem and I solved it using the below steps: -

  1. Select and copy the object
  2. Create a new document (workbook)
  3. Paste the object in
  4. Save the new document (workbook) as HTML
  5. Look through the saved package for the only image file (as it was the only thing in the file)
  6. Move that to a location so I could use it elsewhere
  7. Delete the new document (workbook) and HTML copy of it
Community
  • 1
  • 1
Gary Evans
  • 1,850
  • 4
  • 15
  • 30
  • I tried the solution provided in the link, but, I keep getting a '1004 Unable to get the object property of the OLEObject class' – Sgdva May 18 '16 at 14:36
  • Hmm, you have an interesting object there. I would recommend working on the steps rather than pursuing this option. But it would be useful to understand your issue a bit more as others may have an answer, Could you write out the steps needed to get the object into the excel file. – Gary Evans May 18 '16 at 14:40
  • I get it with the following code: ActiveSheet.OLEObjects.Add(Filename:= _ "https://...dummylink" _ , Link:=False, DisplayAsIcon:=False).Select x = Selection.Name Selection.Verb Verb:=xlPrimary Interesting thing, I just spotted that, if I ran it standalone, it doesn't retrieve the object, however, there is some other data that I need to gather from the same domain and when running that one first I can run this one. – Sgdva May 18 '16 at 14:43
  • Sorry Sgdva, I've just done another test to ensure what I'm saying is correct. You will never get the path of the embedded object because it is embedded (`Link:=False`). Embedding places a copy of the source into the Excel file and does not retain the path. If you link to it (`Link:=True`) then you will be able to get the path from `.SourceName` as you suspected. – Gary Evans May 19 '16 at 05:25
  • Don't worry! Saddly, If I set the Link:= True, the object is not downloaded to the hard drive, it's just a ".tmp" file in the local temp folder. – Sgdva May 19 '16 at 17:12
0

It seems there's no other way than to work around with the temp files, I got an answer here (click) on a workaround code to do so, thanks again!

Sgdva
  • 2,800
  • 3
  • 17
  • 28