Adobe Acrobat/MS Excel VBA Code
I Get It! Development uses Adobe Acrobat® to
produce newsletters, classroom materials, and most importantly invoices!
The
portable document format (PDF) is the de facto standard for sharing documents on
the web. You can download
Acrobat Reader
for free, but in order to produce PDF files, you must purchase the full
version of Acrobat.
You must also set a reference to the Adobe library in the VB Editor before
attempting to program. See the movie showing how to set the reference in
Windows Media format or
RealOne format for more
information.
The code below looks at a constant called strFolderToSearch and searches for
all Excel spreadsheets in that folder. Remember to set it to the right folder on
your system or use the Excel dialog box to ask the user each time what the folder
name is. Under that folder you need PS Files and PDF Files.
C:\FilesToPrint
|------\PS Files\
|-------\PDF Files\
There is no way we know to print directly to an Adobe PDF file without being
prompted for a file name. If you have lots of files, it gets old quickly. The
workaround used here is to create a PostScript file first and then convert it to
a PDF file.
The converted file names are written to the original Excel workbook along with
how long the jobs ran (well, and whether or not they ran... it's a little
flaky).
(continued...)
Excel Webinars
CPE Link is a new provider of web-based continuing professional education (CPE)
for accounting professionals. Practitioners can choose from approximately ten live
webcasts scheduled per month plus online self-study courses. To view all CPE Link’s
products and services, visit
http://www.cpelink.com |
Excel doesn't do background printing, so there's no need to disable it in
this flavor of the macro. Acrobat Distiller doesn't fire up until Excel is done
printing.
You will see in the class module that there is a OnJobDone event which
we use to make sure the job did get done and to see how long it took. We also use the
event to delete the PostScript file. There was additional ugliness (in earlier
versions!) with the macro trying to delete the file while Acrobat Distiller was
still using it.
Why can't we all just get along?
If you'd like to download the Excel workbook with the standard module below
and the Class Module it relies on, just save this document to your hard drive
(right click, Save As): ExcelToPDF.xls. If you'd
like to download the whole file structure and some sample docs,
download this zip file. The files will run in an
Office 2000 environment but it may be more flaky.
Your Acrobat Distiller printer driver may be named something other than
"Acrobat Distiller," so if your system hangs while trying to set the
ActivePrinter, go to Control Panel and see what it's called then change the
module accordingly.
Oh yeah.... Don't put this file in the folder with the other Excel workbooks, It'll
try to print itself with some unsavory results...
Sub MakeWorkBookPDFs()
'---------------------- Start module ------
'I Get It! Development provides programming examples
'for illustration only, without warranty either
'expressed or implied, including, but not limited to,
'the implied warranties of merchantability and/or
'fitness for a particular purpose. This article
'assumes that you are familiar with the programming
'language being demonstrated and the tools used to
'create and debug procedures. These examples assume that
'you have licensed copies of all relevant software installed
'on the machine upon which the examples will be run.
'This routine goes through a folder and prints all
'Excel Files in it to a PDF file
'
'!!!!!!!!!!!!!!!!!
'It uses the following external reference libraries:
'Microsoft Office 10.0 Object Library (8.0 & 9.0 also work (I think!))
'Acrobat Distiller
'Go to Tools > References in the VB Editor and check them
'!!!!!!!!!!!!!!!!!
Dim appDist As cAcroDist 'see class module
Dim strActivePrinter As String
Dim strFolderToSearch As String
'FoundFile is a string, but since it's used
'in a For Each...Next loop, it must be a variant
Dim FoundFile As Variant
Dim wbCurrent As Workbook
Dim svInputPS As String
Dim svOutputPDF As String
Dim svJobOptions As String
Dim strBase As String
Set appDist = New cAcroDist
strActivePrinter = Application.ActivePrinter
Application.ActivePrinter = "Acrobat Distiller on Ne02:"
strFolderToSearch = "C:\FilesToPrint"
'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
'Application.Filesearch is part of the shared Office library
'The loop below searches for Word files in the above named strFolderToSearch
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = strFolderToSearch
.Execute
For Each FoundFile In .FoundFiles
'We open the workbook read only so we don't mess it up (and close it w/o
changes)
Set wbCurrent = Workbooks.Open(FoundFile, ReadOnly:=True)
'Use the wb name to decide the pdf name
strBase = Left(wbCurrent.Name, Len(wbCurrent.Name) - 4)
svInputPS = strFolderToSearch & "\PS Files\" & strBase & ".ps"
svOutputPDF = strFolderToSearch & "\PDF Files\" & strBase & "_XL.pdf"
'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.
wbCurrent.PrintOut prtoFilename:=svInputPS, PrintToFile:=True
'Just for that little added extra bit of paranoia, closing a read-only file
'expressly without saving changes.
wbCurrent.Close SaveChanges:=False
'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)
'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
Next FoundFile
End With
Application.ActivePrinter = strActivePrinter
'Clean up
Set appDist = Nothing
Set wbCurrent = Nothing
End Sub
Class Module cAcroDist
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)
ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1) = strOutputPDF & "
printed successfully at " & Now()
ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1) = "Job took " &
DateDiff("s", StartTime, Now()) & " seconds."
blnFinished = True
Kill strInputPostScript
End Sub
Private Sub odist_OnJobFail(ByVal strInputPostScript As String, ByVal
strOutputPDF As String)
ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1) = strOutputPDF & "
failed to print at " & Now() & vbCrLf
ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1) = "Job took " &
DateDiff("s", StartTime, Now()) & " seconds."
blnFinished = True
End Sub
Private Sub odist_OnJobStart(ByVal strInputPostScript As String, ByVal
strOutputPDF As String)
StartTime = Now()
ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 2, 1) = strOutputPDF & " is
printing " & Now()
blnFinished = False
End Sub