Print Excel Sheet

[Shamsuddeen]

Hi,

How to print sheet1 into a PDF file with the filename given in cell A1.

Thanks

 

[Derek Brown]

As your code is likely to be different to mine, could you provide a listing of the code please?
I can then have a look at it to try to work out why it is not working for you.
Could you also say how many worksheets are being printed.

 

[MightyQuinn]

Ok, well first I just got it to work by adding in

Shell ("C:\Program Files\PDFCreator\PDFCreator.exe")

right after my code. Now I need to figure out how to close this same PDFCreator back out within my code. But here is my code (minus the newly added bit at the end):

Sub PrintToPDF_MultiSheetToOne_Early()

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "PES Receipt.pdf"
    sPDFPath = "X:\Logbook\"
    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 Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        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("00:00:" & Right("00" & Trim(Str(intPDF_Time_Delay)), 2))
    pdfjob.cClose
    Set pdfjob = Nothing
    
    'Application.Wait Now() + TimeValue("00:00:" & Right("00" & Trim(Str(intPDF_Time_Delay)), 2))
'pdfjob.cClose
'Set pdfjob = Nothing
End Sub

 

[MightyQuinn]

I am printing two worksheets btw.

 

[Derek Brown]

I have three observations:
1. intPDF_Time_Delay has not been declared (Dim intPDF_Time_Delay as Integer) and consequently has not been given a value. Since the process is not failing I guess that it is using zero?
2. I also have an "Application.Wait" immediately after the "PrintOut" line.
3. Instead of "New PDFCreator.clsPDFCreator" I use CreateObject but I would not expect that to make a difference.
My usual 'wait' period is 5 seconds and that is usually enough but occasionally I need to set it to 10 seconds.

 

[MightyQuinn]

I am getting it to work everytime now by adding the code

Shell ("C:\Program Files\PDFCreator\PDFCreator.exe")

after.

I'm not sure if your changes will fix this for me, nor do I know enough to make those changes. I guess all I need it code on how to close the same program I opened above, because it will not work a second time when the PDFCreator is opened (unless, I maybe made changes in the code, which I do not know how to do).

 

[bchandz]

I am just trying to print my active Excel 2003 sheet to my "Bullzip PDF Printer". Is there a version of this code (post of Aug 5th, 2010 11:20 AM) in 2003 that will do that?

Here's the code I'm using to display the pdf "save" dialog. I then need to either click oni ts"Save" button or press Enter, then again to verify overwrite, then Ctrl-Q to close the saved .pdf. The Sendkeys ("~") doesn't work. Macro just hangs up there on the first one.

Range("C2:O48").Select 'Highlights the print area for chart and table for pdf
Application.ActivePrinter = "Bullzip PDF Printer on Ne05:" 'Chooses BullZip PDF printer for printing
Selection.PrintOut Copies:=1, ActivePrinter:="Bullzip PDF Printer on Ne05:" Displays Save .pdf dialog

' SAVE .PDF
Application.SendKeys ("~")

' OVERWRITE EXISTING .PDF
Application.SendKeys ("~")

' CLOSE .PDF
Application.SendKeys ("^Q")

Thanks much for any help available.