asked on

Embeded Files within an Excel Document - Code not working.

Hi there,

Up until recently, I used to embed PDF files into an Excel document whith the code below:

Sub Embed()
Dim strFileName As String, strTitle As String
Dim objOLE As OLEObject

On Error GoTo Handler
strFileName = Application.GetOpenFilename("PDF Files (*.pdf), *.pdf")
strTitle = Mid$(strFileName, InStrRev(strFileName, "\") + 1)

 
ActiveSheet.OLEObjects.Add Filename:=strFileName, Link:=False, DisplayAsIcon:=True, _
    IconIndex:=5, IconFileName:="C:\windows\installer\$PatchCache$\Managed\68AB67CA7DA73301B7449A0300000010\9.3.0\acrord32.dll", _
    IconLabel:=strTitle

With objOLE
        .Top = cell.center
        .Left = cell.center
        ' uncomment next two lines if you want to fit to the cell exactly
        .Height = cell.Height
        .Width = cell.Width
    End With
 
Exit Sub
 
Handler:
End Sub

Open in new window

This was linked to a button that, when clicked would bring up the Browse Files windows and allow you to embed the file into the Excel document.

I have now discovered that this isnt working on some computers and I have narrowed it down to machines with Acrobat X installed which clearly negates the line:

IconFileName:="C:\windows\installer\$PatchCache$\Managed\68AB67CA7DA73301B7449A0300000010\9.3.0\acrord32.dll", _

Open in new window

What would be the best way to rectify this? Is there any way I can link to a server shared .ico file? Like \\servername\share\pdf.ico?

I found this code a while back and cannot claim to be a master coder, would it be possible for someone to take a look and let me know how to resolve it so all machines can use it, regardless of Acrobat being installed or not?

JanEnEm

Having taken a quick lance at the code:
The ActiveSheet.OLEObjects.Add statements specifies a file loccation for the iconfile name refereing to Acrobat 9/ Would that be the problem when using Acrobat X?
Especially when the location does not exist (anymore)?

Jan Meijer

ASKER CERTIFIED SOLUTION

ultra-it

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JanEnEm

@ultra-it:

The code seems normal to met too.
The only thing I can think of is that the VBA code may interact with Adobe, once it discovers a filetype of PDF is encountered, simply because the filetype is associated to Adobe.

I have no further in depth knowledge of the behavior of the VBA interpreter.

May try it out with a non-PDF style document. But you may have already tried that one out...

Regards

Jan Meijer

JanEnEm

Hi,

I would suggest to keep the entry available for others. I know all too well that sometimes you can hit an entry in the EE database that explains why you have run into problems. Even when a little bit off subject.
Isn't that what a knowledge database is all about?

Jan Meijer