Opening PDFs from VBA??

[Eugene]

Hi All,

I am write a simple macro to open a PDF from VBA, but I want the name
of the PDF and location of the PDF as a string value, such that one
could assign the macro to a button in excel and use it when needed.

So far I have:

Sub pdfVBA()
Dim pdfLoc, pdfName As String

'change this to location of directory
pdfLoc = "M:\"

pdfName = ActiveCell.Value

Shell "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe" & _
pdfLoc & pdfName, 1

End Sub

But I am receiving an "File not found" error. I suspect I has
something to do with using the string values, because when I replace
pdfLoc and pdfName with the location and file the code works.

Thanks for any help in advance
Eugene

 

[papou]

Hi Eugene
Try concatenating both strings with
Dim FullName$
FullName = pdfLoc & pdfName
And then use FullName in your shell command.

HTH
Cordially
Pascal

 

[John Green]

Hi Eugene,

You need to include a space between the program path and the file path and
you probably also need to generate quotes around the file path. Try the
following:

Shell "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe """ & _
pdfLoc & pdfName & """", 1

Copy the above code to a module to see more clearly what is there. The
embedded quotes are doubled and there is a space after .exe

John Green

 

[Rob van Gelder]

I find the following approach to be quite flexible. Not only will it take
PDF, but any type of document (.doc, .hlp, .txt, .pdf, etc...) - including
URLs.

In a standard module:

Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal
hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal
lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long)
As Long

Sub OpenPDF()
ShellExecute 0, "Open", ActiveCell.Value, vbNullString, vbNullString,
vbNormalFocus
End Sub

Assign Macro to OpenPDF

 

[Eugene]

Thanks Pascal and John - the macro seems like it is working now, I
believe4 the little space seemed to clear up the problem.

Thanks Rob as well, I am currently "genericising" the macro for added
functionality!

Thanks all of you again!