Home	Case Studies	MS Office Tips and Tricks	Sample Code	Helpful Videos	Custom Development	Custom Training	Join Us	Contact Us
Search Our Site:

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...)

A Word From Our Sponsor

Jos. A. Bank is The Expert in Men's Apparel

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