Error in VBA for saving to PDF

[DJ_Wilson]

I am trying to write a macro that will convert an Excel workbook to Adobe PDF. I am using Adobe Acrobat 5.0.5 and I have Distiller installed. If I Use the menu options in Excel to print to Distiller and save the output to the required location/file name i can then open the resultant PDF in Acrobat as normal. My problems are in the fact that if i use my macro code and try to set the ActivePrinter to Acrobat Distiller I get the error:

"Method 'ActivePrinter' of object '_Application' failed".

I have trawled the web looking for help on this and the Adobe site indicated that the is a version of both Acrobat 5.x and 6.x on my PC. I removed any reference of Acrobat from my PC, including the registry and re-installed it. I am still getting the same error.

The code I'm using was posted on this site:

'Form1 Code
Option Explicit

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 " & Mid(Application.ActivePrinter, Len(Application.ActivePrinter) - 7)
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


Private Sub Form_Load()
    MakeWorkBookPDFs
End Sub

'----------------------------------------------------

'Class cAcroDist Code:

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

Any help would be greatly appreciated! I have seen possible solutions that go with the option of CutePDF or similar products but would like to avoide this route.

Thanks again in advance for your help!

DJ