View Full Version : Solved: Open & Print PDF

12-27-2012, 05:15 AM
Hi all,

I wish to open and print a pdf document using VBA. However I do not wish to use the hyperlink method as I need to use a wildcard.

For example I want to open a document called "safekeeping", using safe* so that it will open any document with safe as the first characters.

I have succeeded in doing this for excel and word. Just not for PDF. Any help would be gratefully received. Here is my code for an excel file.

Sub MyFiles2()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\"
MyFile = Dir(MyFolder & "safe*.xls")
If MyFile <> "" Then
Workbooks.Open MyFile
End If
End Sub

Many thanks,


Kenneth Hobs
12-27-2012, 07:44 AM
Welcome to the forum! Please use VBA code tags to post code.

Your needs are unclear. Did you want to print a range or print a pdf file or create a pdf file? If you are just printing a range, the printout method should suffice.

To create a pdf file, you might want to review this thread. http://www.vbaexpress.com/forum/showthread.php?t=21590

12-27-2012, 12:05 PM
Hi Kenneth , thanks for the quick response. I am trying to open a PDF and then I would like to print either the entire document or a selection of pages. I want to use a wild card and not a hyperlink to achieve this. I was successful doing this for word and excel but can't seem to open a PDF. Thanks des

Kenneth Hobs
12-27-2012, 12:32 PM
If you have Adobe Acrobat with Distiller and not just Adobe Reader: http://www.vbaexpress.com/forum/showthread.php?t=38468

IF you just have the reader, use Shell() with command line switches. http://excel.itags.org/q_microsoft-excel_461980.html

Here is a string that I use for the method above but with Acrobat to show the command line parameters that you need for the Shell() method. I don't have time right now to work out the error catching routines. I normally use a method to find the PDF application and try command line parameters for it.

Sub t()
Dim q As String, filename As String
q = """"
filename = "c:\pdf\test.pdf"
s = "cmd /c " + q + "C:\Program Files\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe " + q + " /s /o /n /h /t " + filename
Shell s, vbMinimizedFocus
End Sub

12-27-2012, 01:51 PM
Thanks very much Kenneth . I will try that. Does the shell line command allow for wild cards. For example test*

Kenneth Hobs
12-27-2012, 02:00 PM
Set the filename first using the Dir() command with the wildcard.

In a Module, this prints all sheets for my Acrobat.exe but leaves a blank instance open.

Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" _
(ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long

Sub t()
Dim q As String, filename As String, s As String
q = """"
filename = ThisWorkbook.Path & "\Pages1to5.pdf"
s = q & ExePath(filename) & " " & q & " /n /s /o /h /t " & filename
Shell s, vbMinimizedFocus
End Sub

Function ExePath(lpFile As String) As String
Dim lpDirectory As String, sExePath As String, rc As Long
lpDirectory = "\"
sExePath = Space(255)
rc = FindExecutable(lpFile, lpDirectory, sExePath)
sExePath = Left$(sExePath, InStr(sExePath, Chr$(0)) - 1)
ExePath = sExePath
End Function

12-28-2012, 03:17 AM
You can also use ShellExecute
Private Declare Function ShellExecute Lib "shell32.dll" 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
Const SW_HIDE As Long = 0&
Const SW_SHOW As Long = 5&

Sub PrintFile(strFilePath As String)
ShellExecute Application.hWnd, "Print", strFilePath, 0&, 0&, SW_HIDE
End Sub
Sub OpenFile(strFilePath As String)
ShellExecute Application.hWnd, "Open", strFilePath, 0&, 0&, SW_SHOW
End Sub

12-28-2012, 04:45 AM
Hi that works nicely for static file names. I have never used the dir function before so using your example how might that look if I want to print all pdf files that begin with the letters "safe" ? Once I have that piece I think it is problem solved.



12-28-2012, 05:06 AM
If you are referring to my code, something like

Sub PrintPDFs()
dim sFile as string
dim sFolder as string
sfolder = "C:\some folder\"
sfile = dir(sfolder & "safe*.pdf")
do while sfile <> ""
printfile sfolder & sfile
sfile = dir
end sub

12-28-2012, 06:25 AM

Thank you both very much. That works perfectly. I will do some testing and then mark the question as solved.