PDA

View Full Version : Print PDF script: check for pdf printer and give dialogue to choose?



andrewvanmar
11-21-2007, 04:28 AM
I use the below script generously created by Ken Pulse but i'm encountering a problem with the PDF creator;
I.T. wants us to use that same pdfcreator, though installed on the network, and not locally.

Since this document will be used on several different networks (all with the PDF creator installed) I would like to be able to do the following:

have the script check for a locally installed pdfcreator ( should be in the ref. library then), and if it doesn't find one, pop up a choose printer dialogue.

Is this possible? (and if so, then how?)

Thanks! :thumb


Private Sub CommandButton1_Click()
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca))
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for early bind, set reference to PDFCreator
'
'Adapted by Charlize for printing certain worksheets (15/06/2007)
Application.ScreenUpdating = False
'this checks if a bunch of required fields are filled in
If IsComplete = False Then
MsgBox "Cannot PDF, please fill in all the fields in the orderform. Required fields are colored yellow. Please check all sheets for missing information."
Exit Sub
End If
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim lTtlSheets As Long
Dim mpFilename As Variant

If Not ActiveWorkbook.Saved Then
mpFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If mpFilename = False Then
Exit Sub
Else
ActiveWorkbook.SaveAs mpFilename

'go on with the routine
End If
End If
'/// Change the output file name here! ///
sPDFName = ActiveWorkbook.Name
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
Set pdfjob = New PDFCreator.clsPDFCreator
'Make sure the PDF printer can start
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "Error!"
Exit Sub
End If
'Set all defaults
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the document to PDF
lTtlSheets = Application.Sheets.Count
For lSheet = 1 To Application.Sheets.Count
On Error Resume Next 'To deal with chart sheets
If Sheets(lSheet).Name = "Information" Or _
Sheets(lSheet).Name = "Service Details" Or _
Sheets(lSheet).Name = "Signatures and pricing" Then
If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
Application.Wait (Now + TimeValue("0:00:02"))
Else
lTtlSheets = lTtlSheets - 1
End If
Else
lTtlSheets = lTtlSheets - 1
End If
On Error GoTo 0
Next lSheet
'Wait until all print jobs have entered the print queue
Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
DoEvents
Loop
'Combine all PDFs into a single file and stop the printer
With pdfjob
.cCombineAll
.cPrinterStop = False
End With
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:02"))
pdfjob.cClose
Set pdfjob = Nothing
MsgBox "PDF File successfully created. The folder containing the PDF will now be opened. Click OK to proceed.", vbInformation, "PDF creation ..."
Call ShowPDF

Application.ScreenUpdating = True
End Sub

mdmackillop
11-21-2007, 06:19 AM
I've a couple of KB items re printer selection. Check them out to see if they help.

andrewvanmar
11-21-2007, 10:11 AM
I found the script, and am playing with it. thanks

I'm still unclear hoe to formulate the "IF" where if there pdfcreator reference is missing, it should move on to:
Sub ShowPrintSelector()
PrintSelector.Show
End Sub
and how to continue from there ( do I need somekind of value?)

mdmackillop
11-21-2007, 11:32 AM
If there is no PDFCreator set up on your PC, then Choose Printer dialog will not assist. You will need either to install it locally, or link to its Server installation.

andrewvanmar
11-22-2007, 07:48 AM
There is a server installation, so that should be ok?

Charlize
11-22-2007, 07:58 AM
Application.Dialogs(xlDialogPrinterSetup).Show

andrewvanmar
11-22-2007, 08:50 AM
Been tinkering with it, but i'm at a loss where to insert this in the code for PDFi'ng

but that is because I barely understand what the pdf?ng code actually does.

I think that , where the code is hardcoded to look for thr pdfcreator is should now give this dialogue.

andrewvanmar
11-22-2007, 09:11 AM
hmm, just to test, I used the regular printing buttons, selecting the pdfcreator and telling it to print pages 1-3. This didn't work, it'only pdf'ed page 1.

So the ken pulse script is integral to making that multipage pdf.

The question is now, how to add that


Application.Dialogs(xlDialogPrinterSetup).Show


into the whole thing.

mdmackillop
11-22-2007, 12:45 PM
Here is what you asked for. However, if PDFCreator is not installed/linked from the server, opening the Setup dialog won't help.

andrewvanmar
11-23-2007, 02:10 AM
You mean if it doesn't show in the list of printers in <configuration/printers and faxes> ?

I'll explain people to install the printer from the netw :)

Thanks for the help, i'm gonna try it right away!

andrewvanmar
11-23-2007, 03:37 AM
oops found an error:
Compile error:
User-defined type not defined

Private Sub DoPrint()
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca))
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for early bind, set reference to PDFCreator
'
'Adapted by Charlize for printing certain worksheets (15/06/2007)
Application.ScreenUpdating = False
'this checks if a bunch of required fields are filled in
'If IsComplete = False Then
' MsgBox "Cannot PDF, please fill in all the fields in the orderform. Required fields are colored yellow. Please check all sheets for missing information."
' Exit Sub
'End If
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim lTtlSheets As Long
Dim mpFilename As Variant
........

The highlighted line i VBE i made bold and underlined.

(the printer on my computer is both available locally and the network


PS: what does oldprinter mean?

mdmackillop
11-23-2007, 04:51 AM
I'll look into the error later.
OldPrinter gets the name of the current printer to reset your printer after the PDF print.

andrewvanmar
11-23-2007, 05:22 AM
Ah, nice to know.

btw I commented out the "IF" statement, since it refers to a separate piece of script (to check required fields) it'll give an error too otherwise :)

Thanks for taking the time for me :fireman2:

'If IsComplete = False Then
' MsgBox "Cannot PDF, please fill in all the fields in the orderform. Required fields are colored yellow. Please check all sheets for missing information."
' Exit Sub

andrewvanmar
11-23-2007, 08:34 AM
Googeling the error gives me the info that there is something missing in the reference library.

So I looked for the PDF creaor in the ref lib, it was unchecked.
Now it doesn't give -that - error anymore.

aside fro the new error ( more on that later) this poses a real problem, since I want to deploy the doc to multiple networks where the location of the pdf creator is different every time. That's why I wanted to dialogue to appear so they can choose the printer there, instead of having it hardcoded.

Oooh, I think I figured it out:

by commenting the if statement (if printer is not found etc) it forces the dialogue to open everytime: when I choose the network printer it works, now to test on a computer with no local installation of the pdfcreator.

andrewvanmar
11-23-2007, 08:49 AM
7364Nope! I changed the local pdfcreator name, so it couldn't access that hard coded, and it gave me the following:

Runtime error 1004:
method 'activeprinter' of object '_Application failed

This line was highlighted:

Application.ActivePrinter = Printer

attached is the excell with the missing scripts, so it should work now. (atleast, it won't crash on missing code)

andrewvanmar
11-26-2007, 10:40 AM
Still can't figure out how to override the ken pulse code with the printer select. it seems that after the printer select dialogue, it just ignores the utcome and goes for the originally coded printer.

andrewvanmar
11-28-2007, 09:27 AM
Seems that this question has died a silent death?

I still can't figure out how to make the script use the chose printer from the dialogue instead of the coded printer.

help please? :boohoo

mdmackillop
11-28-2007, 11:40 AM
Hi Andrew,
A far as I can see, you can only print to a printer actually installed on your PC or installed as a link to a shared printer. If it not there, it is just not available to the Printer Dialog in Excel.
The purpose of my code is to get the "on Ne04:" suffix which is required to set an ActivePrinter in Excel. This suffix seems to be dynamic, by alpabetical order of installed printers, hence its use in multiple PCs printing to one shared printer or locally installed versions.

andrewvanmar
11-30-2007, 08:09 AM
Hi mac, (hope you don't mind),

The printer on the server is installed (linked) on my computer. when opening the print dialogue i can see and select it.
That part seems to work.

what doesn't work is where the dialogue is (i think) supposed to add some value to the ken pulse code so it prints to the chosen printer.
Right now it appears that what ever the dialogue does is disregarded, and that the ken pulse code only looks for the locally instally pdfcreator. What should be removed edited to make it "listen" to the dialogue?