Solved

Using pdf995 in macro gets error

Posted on 2008-10-23
2
860 Views
Last Modified: 2012-08-13
I have a macro that selects and prints a chart.
When I do a "Record Macro" and tell it to print using PDF995, I get a good PDF file.
When I use :
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, PrToFileName:="c:\Zaas\dir1.pdf", the pdf file is three times bigger and won't open.
The error message says either the file's an incompatable format or it's corrupted.

I'd appreciate any help.
Thanks

Sub Dir1()

'

' Dir1 Macro

' Macro recorded 10/23/2008 by tmccar10

'

    ActiveWindow.Visible = False

    ActiveSheet.ChartObjects("Chart 1").Activate

    ActiveChart.ChartArea.Select

    Application.ActivePrinter = "PDF995 on Ne00:"

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, PrToFileName:="c:\Zaas\dir1.pdf"

    

End Sub

Open in new window

0
Comment
Question by:tmccar10
2 Comments
 
LVL 2

Accepted Solution

by:
TadSter earned 125 total points
Comment Utility
Excel does not natively print to PDF but there are addins that can do the job. If you have Adobe Acrobat you should have "Adobe PDF" printer installed. You can print to the "Adobe PDF" printer and it will stop and ask the user for a filename. Not so good.
My preferred solution is to print to PS format then convert it to PDF using Ghostscript (free download) or Distiller. This method allows you to specify the file name in your code.
Install a printer driver that supports PS file format. Windows XP comes with drivers for "Apple Color LaserWriter 12/600" Add it as a printer and call it PDFwriter. It doesn't matter what port you use because you won't use the port; LPT1 is fine.
Download and install GhostScript (free) or Adobe Distiller. The code I have is for Distiller. Be sure that you have the references as noted in the code.

'found this code on the web; am not sure where anymore.
 

'paste this into a class module named cAcroDist

Option Explicit

 

Public WithEvents odist As PdfDistiller

Public blnFinished As Boolean

Dim StartTime As Date

 

Private Sub Class_Initialize()

    Set odist = New PdfDistiller

End Sub

 

Private Sub odist_OnJobDone(ByVal strInputPostScript As String, ByVal strOutputPDF As String)

    'Sheets("PDF_Output").Range("A65536").End(xlUp)(2) = strOutputPDF & " printed successfully at " & Now()

    blnFinished = True

    'Kill strInputPostScript

End Sub

 

Private Sub odist_OnJobFail(ByVal strInputPostScript As String, ByVal strOutputPDF As String)

    'Sheets("PDF_Output").Range("A65536").End(xlUp)(2) = strOutputPDF & " failed to print at " & Now() & vbCrLf

    blnFinished = True

End Sub

 

Private Sub odist_OnJobStart(ByVal strInputPostScript As String, ByVal strOutputPDF As String)

    StartTime = Now()

    'Sheets("PDF_Output").Range("A65536").End(xlUp)(2) = strOutputPDF & " is printing " & Now()

    blnFinished = False

End Sub
 

'paste this into a module

Option Explicit

'need type reference: Acrobat Distiller
 

Sub CreatePDF()

    Dim svOutputPDF As String

    Dim svInputPS As String

    Dim fileBakName As String

    Dim PrinterName

    

    svOutputPDF = "C:\Temp\File 1.PDF"

    svInputPS = "C:\Temp\File 1.PS"

    PrinterName = "PDFwriter"
 

    'print to PS

    ActiveWindow.SelectedSheets.PrintOut PrintToFile:=True, PrToFileName:=svInputPS, ActivePrinter:=PrinterName
 

    'converto to PDF

    Call MakePDFs(svInputPS, svOutputPDF)

End Sub
 

Sub MakePDFs(svInputPS, svOutputPDF) 'Will Riley - June 2004

    

     'Amended from code given at "I Get It!!!"

     'In order not to get an error essage from the Adobe Distiller

     'you MUST set up the following settings from the CC Exp Analysis

     'Worksheet

     'From the worksheet goto File>Print .. select the Distiller

     'Goto Properties & uncheck the tickbox under

     'Adobe PDF Settings entitled "Do Not Send Fonts to Distiller

     '

     '#############################################################

     '#The Code uses the following external reference libraries:  #

     '#Microsoft Office 10.0 Object Library (9.0 also works)      #

     '#Acrobat Distiller                                          #

     '#Go to Tools > References in the VB Editor and check them   #

     '#############################################################

     

    Dim appDist As cAcroDist 'see class module

    Dim svJobOptions As String

    Dim retval

    Dim x

    Dim fso

    

    Set appDist = New cAcroDist

        

        'We don't want to see the distiller window

    appDist.odist.bShowWindow = False

        'We are using Start/Done events. If we spool, they don't fire

    appDist.odist.bSpoolJobs = False

        'Excel can print to a PostScript file and name it, but it can't

        'print to a PDF file and name it. And we DON'T want to name each

        'file individually, thanks much.

        

        'This uses our distiller class mod to make the PDF file

        'Upon successful completion, it deletes the PostScript file

    Call appDist.odist.FileToPdf(svInputPS, svOutputPDF, svJobOptions)

        'convert PS to PDF (above)

        'Distiller is SLOW. We have to sit here until the JobDone Event

        'fires and changes blnFinished to true

    Do While Not appDist.blnFinished

        DoEvents

    Loop
 

Cleanup:

    Set appDist = Nothing

    Dim fso

    Set fso = CreateObject("Scripting.FileSystemObject")

    fso.DeleteFile (Left(svOutputPDF, Len(svOutputPDF) - 3) & "log")

    fso.DeleteFile (svInputPS)

End Sub

Open in new window

0
 

Author Closing Comment

by:tmccar10
Comment Utility
Thanks Tad-

Unfortunately, I need this to distribute this to a lot of people and my company doesn't provide Acrobat to many people.

I can use pdf995 with the same intervention  you mentioned. I was trying to avoid that, as you can imagine.

I appreciate the code and I'll keep it, just in case.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 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