[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4989
  • Last Modified:

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.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
michael1174
Asked:
michael1174
  • 7
  • 6
1 Solution
 
Robberbaron (robr)Commented:
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
 
michael1174Author Commented:
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
 
Robberbaron (robr)Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
michael1174Author Commented:
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
 
michael1174Author Commented:
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
 
Robberbaron (robr)Commented:
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
 
michael1174Author Commented:
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
 
Robberbaron (robr)Commented:
1/ Whats different on the sheets ?

0
 
michael1174Author Commented:
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
 
michael1174Author Commented:
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
 
Robberbaron (robr)Commented:
does the sheet print to a physical PostScript printer ok ?
0
 
michael1174Author Commented:
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
 
Robberbaron (robr)Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now