Print Excel Sheet

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Upvote 0
I have a small issue with my PDFcreator being in a "waiting" mode after my code runs. I have code as listed in the previous posts from Ken Pul's website to automatically create a PDF. Then I have code to attach it to an email but it only works sometimes. The problem is, sometimes the PDF does not fully create and is in a "waiting" mode under the PDFCreator's status. So what I have been doing is opening the PDFCreator icon and the status will switch from "waiting to "printing" but at this time my code for the email has already finished and thus no attachment is made in my email! It is strange how it will attach it sometimes, but only about 10% of the time. Can anyone help me figure this small problem out so my 1 year long project can be finished and 100% perfect! Thanks for any help guys

Here is my code for the PDFCreator:
Code: 
Sub PrintToPDF_MultiSheetToOne_Early()
'Author       : Ken Puls (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

    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
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub
 
Upvote 0
Yes, I have the same problem sometimes.
It seems that the Excel process 'continues' too soon, so I added a 'wait' at the end of the code.
So my last three lines of code are:
Code: 
Application.Wait Now() + TimeValue("00:00:" & Right("00" & Trim(Str(intPDF_Time_Delay)), 2))
oPDFjob.cClose
Set oPDFjob = Nothing
Where IntPDF_Time_Delay is a variable that gets its value from a "Control" worksheet.
I usually have that set to 5 but sometimes I have to change it to 10.
When the process gets stuck, I abandon the PDF task in Task Manager.
 
Upvote 0
Thanks for the reply Derek,
I get an error with how you have
Code: 
oPDFjob.cClose
and in my code I had
Code: 
PDFjob.cClose
. Would this matter? Should I take the "o" out or change all my code maybe?
 
Upvote 0
It should be as you have in your code for that and the next line - just add the one line with the 'wait' code.
It is just that I use a particular naming convention with Object variables starting with "o", string variables starting with "str" etc.
 
Upvote 0
The code works however, the wait does not help. The pdf's status still is in "waiting" mode when I open the PDFCreator icon. But once I open it, it then prints it! So it must be another problem. So im not sure why it even worked sometimes (5-10% of the time). I also just got an email from Ken himself though. He sent me an alternative code for this. So I'm going to try working with it and see what happens. Thanks for your help though Derek
 
Upvote 0
Thank you for the update - hope that it works OK for you.
No need to answer this question but I later wondered if you were printing a large number of worksheets.
For me I think the maximum is less than 12 and I would have listed the "required" sheets in a "Control" sheet so that I could be more selective.
 
Upvote 0
Ok, this is still not working, the status of the pdf creator seems to stay in waiting mode until I click on the desktop icon (meaning, it will stay in waiting mode until I click on the icon and open it up. However, if I click on the icon while my code is running, the status will go from "waiting" to "printing" and it will work (if done in time).

So the Solution I think is it have my code just open my PDFCreator icon within my code! Does anyone know how to do this? It is in the location
Code: 
C:\Program Files\PDFCreator\PDFCreator.exe
and I would also have to know how to close it out each time within my code as well. I think this will finally solve this huge issue. Any help greatly appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Emailing from Excel and automatically attaching pdf
Replies
1
Views
344
dermie_72
D
P
  • Question Question
VBA to loop through list of filenames and print
Replies
1
Views
256
Micron
M
T
  • Question Question
Printing to PDF use VBA - Was working but now different sheets are different widths
Replies
1
Views
268
Tanquen
T
C
  • Solved
change file path to look at a cell in the sheet
Replies
9
Views
76
curtoliver68
C
T
  • Solved
Using Excel, I'd like to show the page number on the first sheet, where the start other sheets can be found.
2 3
Replies
26
Views
435
Tanquen
T

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top