Solved: Open & Print PDF

[dodonohoe]

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.

[VBA]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
Sheets("Sales").Select
Selection.PrintOut
End Sub
[/VBA]
Many thanks,

Des

[Kenneth Hobs]

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

[dodonohoe]

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]

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.

[VBA]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
[/VBA]

[dodonohoe]

Thanks very much Kenneth . I will try that. Does the shell line command allow for wild cards. For example test*

[Kenneth Hobs]

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.

[vba]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[/vba]

[Aflatoon]

You can also use ShellExecute
[vba]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
[/vba]

[dodonohoe]

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.

Thanks,

Des

[Aflatoon]

If you are referring to my code, something like
[vba]
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
loop
end sub[/vba]

[dodonohoe]

Aflatoon/Kenneth,

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

Thanks,

Des