PDA

View Full Version : PDFCreator Problems with Excel



dmarciano
02-03-2009, 10:15 AM
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.Last IndexOf("\"), "\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
02-03-2009, 03:08 PM
Our member Ken Puls has posted on this subject on his own site (http://www.excelguru.ca/node/21)

dmarciano
02-04-2009, 07:11 AM
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.