Solved

Convert Excel to PDF using Visual Basic, Adobe Distiller

Posted on 2007-04-01
15
4,886 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

914 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

12 Experts available now in Live!

Get 1:1 Help Now