Databases
StackOverflow2013
Postsdbo
POVBA from Excel failing to print via PDFCreator after loop
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA from Excel failing to print via PDFCreator after loop
    primarykey
    data
    text
    <p>I've written some code to take info from 3 columns on tab one (1000 rows of data) to populate data on the second tab ( to make a form letter based on account information address etc). The code enters a Do While and the problem occures after the PDF is sent to the queue when the code continues the loop. The error only occurs when there running the code, when stepping through the code there are no issues.</p> <p>I've tried this in both 2003 and 2007 with similar results (2003 will print 3 files and I've got 2007 to print up to 6 files)</p> <p>I've also tried to add a manual delay with </p> <pre><code>Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) </code></pre> <p>and after the job enters the print queue</p> <pre><code> Sleep 3000 </code></pre> <p>I've also added a Do Until loop to wait for the print jobs to go down to zero with no success.</p> <p>The complete code is:</p> <pre><code>Sub PlaceData() Dim accountNumber As String Dim partyID As String Dim ClientAddress As String Dim bRestart As Boolean Dim totalAccounts As Long Dim pdfjob As PDFCreator.clsPDFCreator Dim dataPage As Worksheet Dim letterPage As Worksheet Dim CB As Workbook 'CB = ClientBook Set CB = ThisWorkbook Set dataPage = CB.Sheets("Data") Set letterPage = CB.Sheets("Letter") 'will iterate through the account numbers down therow = 1 'where the loop starts totalAccounts = dataPage.Cells(Rows.Count, 1).End(xlUp).Row Do While therow &lt; totalAccounts therow = therow + 1 'for the form letter letterPage.Range("F4").FormulaR1C1 = dataPage.Range("A" &amp; therow) letterPage.Range("F5").FormulaR1C1 = dataPage.Range("C" &amp; therow) letterPage.Range("B10").FormulaR1C1 = dataPage.Range("B" &amp; therow) 'accountnumber minus one digit for the file name accountNumber = letterPage.Range("F4").Text accountNumberShort = Mid(accountNumber, 1, 8) On Error GoTo EarlyExit Application.ScreenUpdating = False Set pdfjob = New PDFCreator.clsPDFCreator sPDFPath = ActiveWorkbook.Path &amp; Application.PathSeparator 'Check if PDFCreator is already running and attempt to kill the process if so Do bRestart = False Set pdfjob = New PDFCreator.clsPDFCreator If pdfjob.cStart("/NoProcessingAtStartup") = False Then 'PDF Creator is already running. Kill the existing process Shell "taskkill /f /im PDFCreator.exe", vbHide DoEvents Set pdfjob = Nothing bRestart = True End If Loop Until bRestart = False With pdfjob .cOption("UseAutosave") = 1 .cOption("UseAutosaveDirectory") = 1 .cOption("AutosaveDirectory") = sPDFPath .cOption("AutosaveFilename") = accountNumberShort .cOption("AutosaveFormat") = 0 ' 0 = PDF .cClearCache End With 'Delete the PDF if it already exists If Dir(sPDFPath &amp; accountNumberShort) = accountNumberShort Then Kill (sPDFPath &amp; accountNumberShort) 'Print the document to PDF letterPage.PrintOut copies:=1, ActivePrinter:="PDFCreator" 'Wait until the print job has entered the print queue Do Until pdfjob.cCountOfPrintjobs = 1 DoEvents Loop pdfjob.cPrinterStop = False 'Wait until PDF creator is finished then release the objects Do Until pdfjob.cCountOfPrintjobs = 0 DoEvents Loop Loop 'where the loop will end and where the issue is (I think) 'cleanups Cleanup: 'Release objects and terminate PDFCreator Set pdfjob = Nothing Shell "taskkill /f /im PDFCreator.exe", vbHide On Error GoTo 0 Application.ScreenUpdating = True Exit Sub EarlyExit: 'Inform user of error, and go to cleanup section MsgBox "There was an error encountered. PDFCreator has" &amp; vbCrLf &amp; _ "has been terminated. Please try again.", _ vbCritical + vbOKOnly, "Error" Resume Cleanup Set CB = Nothing Set dataPage = Nothing Set letterPage = Nothing Set pdfjob = Nothing End Sub </code></pre> <p>Thanks for any input or suggestions,</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USjamesC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COby no means is this a solution, but does killing spoolsv.exe at the same time as killing PDFCreator help?
      singulars
      1. POVBA from Excel failing to print via PDFCreator after loop
      1. USjon
    2. CO@jonlester no that doesn't see to help my issue. spoolsv.exe looks to not be going wonky as i've seen happen for some. I think I'll experiment more with the delay as I think the code is just not allowing the process to really get started
      singulars
      1. POVBA from Excel failing to print via PDFCreator after loop
      1. USjamesC
    3. COafter some very experimental debugging (there's a beetle in there). I think I've found the main culprit these lines are the ones where it keeps hanging : Set pdfjob = New PDFCreator.clsPDFCreator If pdfjob.cStart("/NoProcessingAtStartup") = False Then
      singulars
      1. POVBA from Excel failing to print via PDFCreator after loop
      1. USjamesC
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload