Hi @ all
I've created a windows forms application which should create a pdf out of specific excel file with selected worksheets! Unfortunately, the code below only generates corrupt PDF files and I can't see the reason: "Adobe Reader could not open 'Document.pdf' because it is either not a supported file type or because the file has been damaged..."
I've already asked in the pdfforge.org forum, but there seems to be nobody who can help! Why is this code not working???
Here's the situation:
- PDFCreator 1.2.3
- MS Excel 2010 / MS Office 2010
- Visual Studio Professional 2010
...and here's the modified code from Ken Puls (VBA to VB.NET):
I've created a windows forms application which should create a pdf out of specific excel file with selected worksheets! Unfortunately, the code below only generates corrupt PDF files and I can't see the reason: "Adobe Reader could not open 'Document.pdf' because it is either not a supported file type or because the file has been damaged..."
I've already asked in the pdfforge.org forum, but there seems to be nobody who can help! Why is this code not working???
Here's the situation:
- PDFCreator 1.2.3
- MS Excel 2010 / MS Office 2010
- Visual Studio Professional 2010
...and here's the modified code from Ken Puls (VBA to VB.NET):
Code:
Public Sub MainXLS()
' Declare variables
Dim ExcelInstance As New Microsoft.Office.Interop.Excel.Application
Dim ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook
' Hide excel instance
ExcelInstance.Visible = False
' Open workbook
ExcelWorkbook = ExcelInstance.Workbooks.Open("C:\Users\ad\Desktop\411539_XXXXXX_CAM.XLS")
' Create PDF
PrintToPDF_SpecifiedSheetsToOne_Early(ExcelWorkbook, _
ExcelInstance, _
"DECKBLAT.XLU,DECKBLAT.XLS,SPEZ_SW.XLS", _
"C:\Users\ad\Desktop", _
"411539_XXXXXX_CAM")
' Close workbook and Excel instance
ExcelWorkbook.Close(SaveChanges:=False)
ExcelInstance.Quit()
' Clean up
ExcelWorkbook = Nothing
ExcelInstance = Nothing
End Sub
Private Sub PrintToPDF_SpecifiedSheetsToOne_Early(ByRef ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook, _
ByRef ExcelInstance As Microsoft.Office.Interop.Excel.Application, _
ByVal SheetSelection As String, _
ByVal OutputPath As String, _
ByVal OutputName As String)
' Author : Ken Puls (www.excelguru.ca)
' Modified by : Olphi
' Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for early bind, set reference to PDFCreator
' Declare variables
Dim pdfJob As PDFCreator.clsPDFCreator
Dim strPDFName As String
Dim strPDFPath As String
Dim strSheets() As String
Dim boolRestart As Boolean
Dim longSheet As Long
Dim longTotalSheets As Long
Try
' Define the output
strPDFName = OutputName & ".pdf"
strPDFPath = OutputPath & ExcelInstance.PathSeparator
' Split the sheets into an array
strSheets = SheetSelection.Split(",")
' Turn off screen updates
ExcelInstance.ScreenUpdating = False
' Create new PDFCreator instance
pdfJob = New PDFCreator.clsPDFCreator
' Check if PDFCreator is already running and attempt to kill the process if so
Do
boolRestart = False
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)
System.Windows.Forms.Application.DoEvents()
pdfJob = Nothing
boolRestart = True
End If
Loop Until boolRestart = False
'Assign settings for PDF job
With pdfJob
.cVisible = False
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = strPDFPath
.cOption("AutosaveFilename") = strPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache()
End With
' Delete the PDF if it already exists
If Dir(strPDFPath & strPDFName) = strPDFName Then Kill(strPDFPath & strPDFName)
'Print the document to PDF
For longSheet = LBound(strSheets) To UBound(strSheets)
' Check if there is printable data inside the selected worksheet
If Not IsNothing(ExcelInstance.Sheets(strSheets(longSheet)).UsedRange) Then
' Print worksheet
ExcelInstance.Sheets(strSheets(longSheet)).PrintOut(copies:=1, ActivePrinter:="PDFCreator auf NE01:")
' Increase sheet counter
longTotalSheets = longTotalSheets + 1
End If
Next longSheet
' Wait until all print jobs have entered the print queue
Do Until pdfJob.cCountOfPrintjobs = longTotalSheets
System.Windows.Forms.Application.DoEvents()
Loop
' Combine all PDFs into a single file and stop the printer
With pdfJob
.cCombineAll()
.cPrinterStop = False
End With
' Wait until the file shows up before closing PDF Creator
Do
System.Windows.Forms.Application.DoEvents()
Loop Until Dir(strPDFPath & strPDFName) = strPDFName
Catch ex As Exception
'Inform user of error, and go to cleanup section
MsgBox("There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error")
Finally
'Release objects and terminate PDFCreator
pdfJob = Nothing
Shell("taskkill /f /im PDFCreator.exe", vbHide)
' Turn on screen updates
ExcelInstance.ScreenUpdating = True
End Try
End Sub