Advertisement

04.01.2007 at 09:41AM PDT, ID: 22485533
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Convert Excel to PDF using Visual Basic, Adobe Distiller

Tags: pdf, excel, adobe, distiller, convert
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
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: michael1174
Solution Provided By: michael1174
Participating Experts: 1
Solution Grade: B
Views: 61
Translate:
Loading Advertisement...
04.02.2007 at 04:18AM PDT, ID: 18835346

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2007 at 05:10AM PDT, ID: 18835593

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2007 at 05:47AM PDT, ID: 18835828

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2007 at 09:56AM PDT, ID: 18837782

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2007 at 03:42PM PDT, ID: 18840115

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2007 at 07:35PM PDT, ID: 18840931

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 02:27AM PDT, ID: 18842066

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 04:59AM PDT, ID: 18842601

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 05:26AM PDT, ID: 18842726

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 06:43AM PDT, ID: 18843191

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 04:01PM PDT, ID: 18847474

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 04:40PM PDT, ID: 18847653

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.03.2007 at 06:11PM PDT, ID: 18847938

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.16.2007 at 05:34PM PDT, ID: 18921589

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.20.2007 at 05:20AM PDT, ID: 18945678

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • Automotive
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Displays / Monitors
  • Handhelds / PDAs
  • Components
  • Peripherals
  • Laptops/Notebooks
  • Servers
  • Misc
  • Apple
  • Embedded Hardware
  • Networking Hardware
  • Storage
  • Desktops
  • New Users
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMware
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Virtualization
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • Web Computing
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Consulting
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMware
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Automation
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Web Services
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Web Computing
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Lounge
  • Business Travel
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
  • Automotive
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
04.02.2007 at 04:18AM PDT, ID: 18835346

Rank: Guru

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
 
04.02.2007 at 05:10AM PDT, 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...
 
04.02.2007 at 05:47AM PDT, ID: 18835828

Rank: Guru

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.
 
04.02.2007 at 09:56AM PDT, 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.
 
04.02.2007 at 03:42PM PDT, 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"
 
04.02.2007 at 07:35PM PDT, ID: 18840931

Rank: Guru

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.
 
04.03.2007 at 02:27AM PDT, 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.
 
04.03.2007 at 04:59AM PDT, ID: 18842601

Rank: Guru

1/ Whats different on the sheets ?

 
04.03.2007 at 05:26AM PDT, 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.
 
04.03.2007 at 06:43AM PDT, 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.
 
04.03.2007 at 04:01PM PDT, ID: 18847474

Rank: Guru

does the sheet print to a physical PostScript printer ok ?
 
04.03.2007 at 04:40PM PDT, 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...
Accepted Solution
 
04.03.2007 at 06:11PM PDT, ID: 18847938

Rank: Guru

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.
 
04.16.2007 at 05:34PM PDT, ID: 18921589
A request has been made in Community Support to close this question:
http://www.experts-exchange.com/Community_Support/General/Q_22513670.html

If there are no objections, a moderator will finalize this question in 4 days by:
PAQ'ing the question and refunding the points
   
Please leave any recommendations here.

Vee_Mod
Experts Exchange Moderator
 
04.20.2007 at 05:20AM PDT, ID: 18945678
Question PAQ'd (closed) with points refunded

Vee_Mod
Experts Exchange Moderator
 
 
20080236-EE-VQP-29