Embed PDF and select file name and path

[Jenny B.]

Hi All,

I’m looking for a way to not only insert/embed an object into my worksheet
(which works below), but have the directory select the file based on an
existing PDF named file. I have users entering data into certain cells which
in turn uses a formula to concatenate their entries into cell A1. This cell
will be the name of the file that’s already residing out on the noted path.
So far, I can only get it to embed the file when the name is static
(hardcoded name in question mark fields). I’d like to instead have it select
the file based on the name in the A1 cell. The worksheet is called Sheet1
and again the cell that names the PDF is A1.

Any thoughts or ideas on how to make this happen?

Thanks in advance – Jenny B.




Sub InsertNamedObject()

ActiveSheet.OLEObjects.Add(Filename:= _
"C:\Documents and Settings\Jenny B.\My Documents\???????", Link:= _
False, DisplayAsIcon:=True, IconFileName:= _

"C:\WINDOWS\Installer\{AC76BA86-1033-F400-7760-000000000004}\_PDFFile.ico", _
IconIndex:=0, IconLabel:= _
"C:\Documents and Settings\Jenny B\My
Documents\BKA1303005.PDF").Select

End Sub

 

[Jacob Skaria]

Something like the below...

Sub InsertNamedObject()

Dim strFile As String
'If sheet1 is the active sheet
strFile = Trim(Activesheet.Range("A1"))

'If Sheet1 is not the active sheet
'strFile = Trim(Sheets("Sheet1").Range("A1"))

ActiveSheet.OLEObjects.Add(Filename:= _
"C:\Documents and Settings\Jenny B.\My Documents\" & strFile, Link:= _
False, DisplayAsIcon:=True, IconFileName:= _
C:\WINDOWS\Installer\{AC76BA86-1033-F400-7760-000000000004}
_PDFFile.ico", _
IconIndex:=0, IconLabel:= _
"C:\Documents and Settings\Jenny B\My Documents\BKA1303005.PDF").Select

End Sub

If this post helps click Yes

 

[Jenny B.]

That did it!

Thank you very much for taking the time to reply and appreciate the prompt
response.

Jenny B.