PDFCreator Problems with Excel

[dmarciano]

First let me state that I'm using VB.NET (not VBA) however I have asked this question on numerous .NET sites and have been referred to this site as there have been several post about using PDFCreator with Excel.

Basically I have multiple Excel files and each file has four sheets. I am trying to print each work book to a separate PDF. The first work books converts to a PDF with no problem however on the second go-around PDFCreator hangs and only the last page of the second work book is saved as a PDF. I've been working on this for two weeks now and can't figure out what else to do. Below is the code (with comments) for the sub routine that is supposed to do the conversion. The lines between asterisks is where the code hangs. Thanks in advance.

With m_ExcelApp   'This is the Excel COM object
            .Visible = False  'Ensure that all work is done in the background

            pdfjob = New PDFCreator.clsPDFCreator   'The PDFCreator object is created

            '/// Make sure the PDF printer can start ///
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                MsgBox("Can't initialize PDFCreator.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error!")
                Exit Sub
            End If

            '/// This is opening up the Excel file one by one ///
            For i As Integer = 0 To IntDistList.Count - 1
                PSFileName = IntDistList(i).ToString.Insert(IntDistList(i).ToString.LastIndexOf("\"), "\Reports") & ".pdf"
                workbook = .Workbooks.Open(IntDistList(i).ToString.Insert(IntDistList(i).ToString.LastIndexOf("\"), "\Reports") & ".xml")
                sheet = .ActiveWorkbook.Sheets(2)
                sheet.Activate()
                sheet.PageSetup.PrintTitleRows = "$1:$9"
                sheet = .ActiveWorkbook.Sheets(3)
                sheet.Activate()
                sheet.PageSetup.PrintTitleRows = "$1:$10"
                .ActiveWorkbook.Save()

                '/// Change the output file name here ///
                div = PSFileName.Split("\")
                sPDFName = div(div.GetUpperBound(0))
                sPDFPath = PSFileName.Substring(0, PSFileName.LastIndexOf("\")) & "\"

                '/// Set all default settings ///
                With pdfjob
                    .cOption("UseAutoSave") = 1
                    .cOption("UseAutosaveDirectory") = 1
                    .cOption("AutosaveDirectory") = sPDFPath
                    .cOption("AutosaveFilename") = sPDFName
                    .cOption("AutosaveFormat") = 0    ' 0 = PDF
                    .cClearCache()
                End With

                '/// Print the file to PDF ///
                lTtlSheets = .Sheets.Count
                For lSheet = 1 To .Sheets.Count
                    If .Sheets(lSheet).usedrange IsNot Nothing Then
                        .Sheets(lSheet).printout(activeprinter:="PDFCreator")
                    Else
                        lTtlSheets -= 1
                    End If
                Next

                '/// Wait until all print job have entered the print queue ///
                '*******************This is where the program hangs on the second go-around but works fine for the first file*****************
                Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
                    Thread.Sleep(30)
                Loop
                '************************************************

                '/// Combine all PDFs into a single file and stop the printer ///
                ChangeStatus("Combining Files")
                With pdfjob
                    .cCombineAll()
                    .cPrinterStop = False
                End With

                '/// Wait until PDF creator is finished then release the objects ///
                ChangeStatus("Finalizing File")
                Do Until pdfjob.cCountOfPrintjobs = 0
                    Thread.Sleep(1000)
                Loop

                Thread.Sleep(2500)

                ChangeStatus("Closing File")
                .ActiveWorkbook.Close()
                PSFileMerge.Add(PSFileName)

            Next

            ChangeStatus("Releasing Objects")
            pdfjob.cClose()
            pdfjob = Nothing

        End With

[mdmackillop]

Our member Ken Puls has posted on this subject on his own site

[dmarciano]

Actually I just figured out what the problem is. Before looping to the next Excel file I needed to stop the printer with pdfjob.cPrinterStop = True. But thanks for the help.