Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

natureboy

Programmer
Jun 16, 2003
12
US
(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?")
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")
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").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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top