Convert Excel to PDF using Visual Basic, Adobe Distiller
I'm trying to convert each excel tab to a pdf using the pdf distiller. I am able to create some of the pdfs, but I get an error and it doesn't create all of the pdfs.
I get the following error message:
"do not send fonts to distiller"
Even though I have it unchecked in the properties. Like I metioned it does create some of the pdfs, but then errors out and doesn't finish.
Here is my code:
'convert to pdfs
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim OriginalPrinter, PDFPrinter As String
Dim sName, PSFileName, PDFFileName As String
Dim s As Worksheet
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
'Identify printers
OriginalPrinter = Application.Printer.Device
'MsgBox OriginalPrinter
PDFPrinter = "Adobe PDF on Ne01:" 'Change this to suit the Acrobat Distiller printer
'Cycle through each sheet, retrieves sheet name and generate PS file in selected folder
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then GoTo MoveOn 'Here you can identify sheets you don't want printed
PSFileName = DLookup("[ExcelTemplatePat
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Print to PS file in nominated folder
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
MoveOn:
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
Do Until fs.FileExists(PDFFileName)
Loop
fs.DeleteFile DLookup("[ExcelTemplatePat
fs.DeleteFile DLookup("[ExcelTemplatePat
Application.Printer = OriginalPrinter 'Resets to original printer selection in Excel
I get the following error message:
"do not send fonts to distiller"
Even though I have it unchecked in the properties. Like I metioned it does create some of the pdfs, but then errors out and doesn't finish.
Here is my code:
'convert to pdfs
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim OriginalPrinter, PDFPrinter As String
Dim sName, PSFileName, PDFFileName As String
Dim s As Worksheet
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
'Identify printers
OriginalPrinter = Application.Printer.Device
'MsgBox OriginalPrinter
PDFPrinter = "Adobe PDF on Ne01:" 'Change this to suit the Acrobat Distiller printer
'Cycle through each sheet, retrieves sheet name and generate PS file in selected folder
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then GoTo MoveOn 'Here you can identify sheets you don't want printed
PSFileName = DLookup("[ExcelTemplatePat
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Print to PS file in nominated folder
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
MoveOn:
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
Do Until fs.FileExists(PDFFileName)
Loop
fs.DeleteFile DLookup("[ExcelTemplatePat
fs.DeleteFile DLookup("[ExcelTemplatePat
Application.Printer = OriginalPrinter 'Resets to original printer selection in Excel
ASKER
Yes, it crashes 3 quarters the way through the tabs I have in an excel workbook (crashes on the 9th tab). I have 12 tabs all together. I tried your version of the code, and it still crashes but its was able to process another excel tab than before (was able to process the 9th tab). Do you think a pause or some kind of sleep command could help. If so, where would you recommend I put it?
Thanks...
Thanks...
1/ so it doesnt look like a font issue at all.
2/ does it still crash if you step your way through the code .... F8 etc. Check the value of PSFileName to make sure it is a valid one...
3/ if no, then this suggests a timing issue as you have thought
4/ I would put a DoEvents inside the loops before "Next s" This gives the OS a timeslice to catch up on other things.
2/ does it still crash if you step your way through the code .... F8 etc. Check the value of PSFileName to make sure it is a valid one...
3/ if no, then this suggests a timing issue as you have thought
4/ I would put a DoEvents inside the loops before "Next s" This gives the OS a timeslice to catch up on other things.
ASKER
Ok, it still crashes. The PSFileName does exist, but its 0KB when it crashes at the following statement:
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
I added the DoEvents, but it looks like its not helping.
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
I added the DoEvents, but it looks like its not helping.
ASKER
Anyone with any tricks up their sleeve? Still crashing 3 quarters the way through.
My code as it stands now:
'convert to pdfs
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim OriginalPrinter, PDFPrinter As String
Dim sName, PDFFileName, PSfileName As String
Dim s As Worksheet
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
'Identify printers
'OriginalPrinter = Application.Printer.Device
'PDFPrinter = "Adobe PDF on Ne01:" 'Change this to suit the Acrobat Distiller printer
PDFPrinter = "Adobe PDF" 'Change this to suit the Acrobat Distiller printer
'Cycle through each sheet, retrieves sheet name and generate PS file in selected folder
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then
'skip
Else
PSfileName = strExcelTemplatePath & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
'Print to PS file in nominated folder
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
End If
DoEvents
Next s
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then
'skip
Else
PSfileName = strExcelTemplatePath & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
PDFFileName = Left(PSfileName, Len(PSfileName) - 2) & "PDF"
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSfileName, PDFFileName, ""
End If
DoEvents
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
fs.DeleteFile DLookup("[ExcelTemplatePat
fs.DeleteFile DLookup("[ExcelTemplatePat
My code as it stands now:
'convert to pdfs
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim OriginalPrinter, PDFPrinter As String
Dim sName, PDFFileName, PSfileName As String
Dim s As Worksheet
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
'Identify printers
'OriginalPrinter = Application.Printer.Device
'PDFPrinter = "Adobe PDF on Ne01:" 'Change this to suit the Acrobat Distiller printer
PDFPrinter = "Adobe PDF" 'Change this to suit the Acrobat Distiller printer
'Cycle through each sheet, retrieves sheet name and generate PS file in selected folder
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then
'skip
Else
PSfileName = strExcelTemplatePath & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
'Print to PS file in nominated folder
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
End If
DoEvents
Next s
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then
'skip
Else
PSfileName = strExcelTemplatePath & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
PDFFileName = Left(PSfileName, Len(PSfileName) - 2) & "PDF"
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSfileName, PDFFileName, ""
End If
DoEvents
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
fs.DeleteFile DLookup("[ExcelTemplatePat
fs.DeleteFile DLookup("[ExcelTemplatePat
1/ did you step through code ?
2/ step through code and skip the printing except for the sheet in concern. This will eliminate any jobs being in the queue before the problem sheet. Trying to isolate is it is a 'sheet' problem or a printer problem.
2/ step through code and skip the printing except for the sheet in concern. This will eliminate any jobs being in the queue before the problem sheet. Trying to isolate is it is a 'sheet' problem or a printer problem.
ASKER
I did step through the code, and I just skipped the print for the sheet with the problem, and it crashed again on the following sheet. So, it looks like its having a problem with the last 3 sheets in my workbook.
1/ Whats different on the sheets ?
ASKER
I can't tell.. they are just invoices of some sort. My client made them up, I was just merging fields from access, then converting them to PDF. I just sent an email asking him if he could tell any differences.
ASKER
I heard back from my client and they don't know.. so I'm at a loss, and I know realize that its something with the sheet, but I don't know what.
does the sheet print to a physical PostScript printer ok ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you might want to see if you can make clearing this option part of your VBscript.
Have a look at the Application.Printer. object settings. It may allow changing.
Have a look at the Application.Printer. object settings. It may allow changing.
Another check is to all the printing first , then start doing the PDF conversions.
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then
'better form. allows multiple names
else
PSFileName = DLookup("[ExcelTemplatePat
' Print to PS file in nominated folder
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter,
endif
Next s
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then
'skip
else
PSFileName = DLookup("[ExcelTemplatePat
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
fs.DeleteFile DLookup("[ExcelTemplatePat
fs.DeleteFile DLookup("[ExcelTemplatePat
Application.Printer = OriginalPrinter 'Resets to original printer selection in Excel