Excel: how do I get the filename from an embedded oject? 1

[natureboy]

(Note: I'm a VBA newbie...be kind

I have a spreadsheet with some embedded PDF files. The files are attached with the following code:

NumDocs = InputBox("How Many **Documentation** files do you wish to attach?&quot
Dim DocPath As String
i = 0
MsgBox "Select the .PDF file you wish to attach as your Documentation."
Do Until NumDocs = 0
thefile = Application.GetOpenFilename("PDF Files, *.pdf&quot
ActiveSheet.OLEObjects.Add(Filename:= _
thefile, Link:= _
False, DisplayAsIcon:=True, IconFileName:= _
"C:\Program Files\Adobe\Acrobat 5.0\Reader\AcroRd32.exe", IconIndex:=0, _
IconLabel:= _
"Documentation" & i).Select
NumDocs = NumDocs - 1
i = i + 1
Loop

I'd like to write some code to grab the filenames of these embedded files, as a precursor step to uploading then into an Oracle db (via Oracle SQL loader). However, I have been only able to get the OLEObject Name in the format of "Object XX":

j = 2
For Each obj In Worksheets("Attachments&quot.OLEObjects
If obj.OLEType = 1 Then ' don't show command buttons
MsgBox "OLEOject name: " & obj.Name
End If
j = j + 1
Next


If I right-click the embedded file, then select "Package Object", "Edit Package" I can see the orignal file's name. This is the "name" I need...

Any suggestions?

Many Thanks,
Greg

 

[SkipVought]

Hi,

Try this...

    For Each obj In ActiveSheet.OLEObjects
        MsgBox obj.SourceName
    Next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[natureboy]

Thanks for the suggestion Skip.

With the new code, I get a runtime error 1004 - the SourceName in the Objects window shows:
SourceName : <Application-defined or object-defined error> : String
Is Excel unable to handle PDF attachments?

G

 

[SkipVought]

If the file is not linked, then you will get this error. The embedded file needs to be linked to the source.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[natureboy]

Thanks Skip!

That did the trick.

Best,
Greg

 

[xlbo]

NatureBoy: I know you've thanked Skip but......
If Skip helped you (which it looks like he did), you should award him a &quot;star&quot;
You can do this by clicking on the &quot;Mark this post as a helpful / Expert post&quot; link at the bottom of the appropriate thread. This is not only the TT way of saying thanks but it also helps those people who search the archives to look at only those posts with helpful / expert comments / solutions.

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes