Print a pdf file from VBA?

[cbrf23]

I need to print a pdf file from vba. I also need to set the print settings to 11x17.

I wanted to see if I could get the file to print using the code below. It didnt work. I mean, I dont get any error message, and if I use F8 to step through the routine, everything executes. But nothing happens.

I have lots of questions:
1. Is this a good method for what I'm trying to accomplish?
2. How can I parse print settings to the program?
-Could be handled with code like if filetype = .pdf, then send settings?
3. Why is nothing happening when I run testPrint?

Option Explicit
Public 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
 
Public Function PrintThisDoc(formname As Long, FileName As String)
On Error Resume Next
Dim X As Long
X = ShellExecute(formname, "Print", FileName, 0&, 0&, 3)
End Function
 
Sub testPrint()
Dim printThis
Dim strDir As String
Dim strFile As String
 
strDir = "C:\Documents and Settings\ME\Desktop"
strFile = "myPDF.pdf"
 
printThis = PrintThisDoc(0, strDir & strFile)
End Sub

I'm open to suggestions? About what I did wrong, or how to accomplish the pdf printing in general?

The excel file will be manipulated from multiple workstations and I have no control over setting references on other computers. Also, the location of the acrobat reader AcroRead32.exe file might not always be in the exact same location.

I was trying to use the shell32.dll method above, to avoid those problems. I figure I dont need to worry about references, or the AcroRead32.exe location if I let windows figure out the program association.

 

[Joe4]

In your Reported Post, you mentioned that you were able to solve this. Would you mind posting your solution here? That way if others have the same issue, they can search here and find the answer.

Thanks.

 

[cbrf23]

Sure.

The reason the function was not working was very simple actually...

I had to change this:

printThis = PrintThisDoc(0, strDir & strFile)

to this:

printThis = PrintThisDoc(0, strDir & "\" & strFile)

or alternatively:

Dim strLocation  as String 'add this to your variable declarations
strLocation = strDir & "\" & strFile 'add this after defining strDir, strFile
printThis = PrintThisDoc(0,strLoc)'change printThis to pass strLoc

Also, for more info on using ShellExecute, I found This Link very helpful and informative

I started a new thread about passing print settings Here.

 

[jjq2r01h]

Hi
Can you use this code to print to another non-default printer in excel 2010 under windows 7. I cant see any parameters where I can specify the printer name.
Thanks / Jag

 

[CodeNinja]

Hi everyone,
This is an old thread, and I find myself trying to do the same thing, however, I would like some more detailed options:

- choose to print to a specific (non-default printer)
- change default print setup portrait or landscape
- change print setting from vector to raster
- change paper size from legal to letter

I may or may not need to do each of these items, depending on the PDF I am printing...

Can anyone help me out here?

(FYI, I am on 64 bit, so I had to declare this as pointer safe...)

Thanks so much,

CN.

 

[cbrf23]

@CodeNinja:

ShellExecute is a method of the Windows Shell (Shell32.dll).

ShellExecute relies upon file associations set in your registry to determine what application to launch for a given operation.
In a nutshell all it does is find the the default program for the specified operation (e.g. for a .PDF the default for Print could be Adobe Reader, Adobe Acrobat, Nitro PDf, etc.) and tells that program to perform the operation.
Shell execute has no control over how that program performs the operation.
Using ShellExecute is equivalent to right-clicking a file in explorer and choosing "print" or "open" or "edit".

If you need to pass settings for how to print, you need to find a program with an API that exposes those settings through an interface.
You then will need to set a reference to the COM library (.DLL) for that API and then you can use it to perform the print operation, instead of ShellExecute.

So in short - you cannot change printers, print settings, etc. using ShellExecute.

 

[CodeNinja]

cbrf23,
Thanks for the clear explanation. I was worried this was the case. I have resolved the problem with a very messy hack, requiring the user to set her default printer and settings the way the printout needs to be done... Fortunately it is 1 user and I think she can handle it.

Thanks again,

CN.

 

[cbrf23]

Youre welcome.
It appears adobe does have some sort of SDK, I'm not sure what all is available - but I would imagine printing and print settings would be available in their API.
I haven't looked into this any further than cursory glance, but if you need more functionality this is one possible option.
Adobe - Acrobat Developer Center | Adobe Developer Connection