Solved

Convert Excel to PDF using Visual Basic, Adobe Distiller

Posted on 2007-04-01
15
4,882 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
  • 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now