Solved

Convert Excel to PDF using Visual Basic, Adobe Distiller

Posted on 2007-04-01
15
4,919 Views
Last Modified: 2009-08-24
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.FileSystemObject") 'Good old FSO

'Identify printers
OriginalPrinter = Application.Printer.DeviceName 'Application.ActivePrinter

'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("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
    PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
    'Print to PS file in nominated folder
    s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter, printtofile:=True, collate:=True, prtofilename:=PSFileName
    '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("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.PS"
fs.DeleteFile DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.LOG"
Application.Printer = OriginalPrinter 'Resets to original printer selection in Excel
0
Comment
Question by:michael1174
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
15 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18835346
Is it always the one sheet that doesnt print ?

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("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
           
           '      Print to PS file in nominated folder
           s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter, printtofile:=True, collate:=True, prtofilename:=PSFileName

     endif


Next s

For Each s In oWB.Worksheets
    sName = s.Name  'Sheet name
    If sName = "Access" Then
           'skip
      else
          PSFileName = DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & 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, ""

         
Next s



Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files

fs.DeleteFile DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.PS"
fs.DeleteFile DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.LOG"
Application.Printer = OriginalPrinter 'Resets to original printer selection in Excel
0
 
LVL 2

Author Comment

by:michael1174
ID: 18835593
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...
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18835828
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:michael1174
ID: 18837782
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, printtofile:=True, collate:=True, prtofilename:=PSFileName

I added the DoEvents, but it looks like its not helping.
0
 
LVL 2

Author Comment

by:michael1174
ID: 18840115
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.FileSystemObject") 'Good old FSO

'Identify printers
'OriginalPrinter = Application.Printer.DeviceName

'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, PrintToFile:=True, collate:=True, prtoFilename:=PSfileName
    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("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.PS"
fs.DeleteFile DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.LOG"
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18840931
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.
0
 
LVL 2

Author Comment

by:michael1174
ID: 18842066
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.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18842601
1/ Whats different on the sheets ?

0
 
LVL 2

Author Comment

by:michael1174
ID: 18842726
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.
0
 
LVL 2

Author Comment

by:michael1174
ID: 18843191
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.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18847474
does the sheet print to a physical PostScript printer ok ?
0
 
LVL 2

Accepted Solution

by:
michael1174 earned 0 total points
ID: 18847653
I finally got it working.  In excel, for the tabs that were crashing, I needed to go to File->Page Setup and then click options button and uncheck "Do not send fonts to Adobe PDF".  Previously, I went into the printers and faxes, clicked on printing preferences for the adobe pdf printer and unchecked that, also went to the printing defaults button, and unchecked that.  So, it was three places I needed to make sure that the "Do not send fonts to Adobe PDF" wasn't checked.

So, the error message was correct.  But unchecking in the printers and faxes didn't always affect each sheet from being unchecked.

Thanks for your help though...
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18847938
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question