Creating many PDFs from Excel VBA

This code works, but it's unreliable because the 5 second wait times are sometimes not long enough and then the application hangs.

It is hard to guess from the "self documenting" COM interface what to test for to determine when it is safe to continue, so I just wait 5 seconds (as a result of trial and error).

There must be some property which tells me (a) when the printout task has completed, (b) when the printer has stopped, and (c) anything else of an asynchronous nature where I should wait on something before continuing.

Can any experienced user guide me to a solution, please?

``` WorkbookFileName = Dir(ExcelPath & "\\*.xls")

Do While WorkbookFileName <> “”

Set PDFTask = New PDFCreator.clsPDFCreator

PDFFileName = Left(WorkbookFileName, Len(WorkbookFileName) - 3) & “pdf”

PDFTask.cStart “/NoProcessingAtStartup”

PDFTask.cOption(“UseAutosave”) = 1

PDFTask.cOption(“UseAutosaveDirectory”) = 1

PDFTask.cOption(“AutosaveDirectory”) = PDFPathName

PDFTask.cOption(“AutosaveFilename”) = PDFFileName

PDFTask.cOption(“AutosaveFormat”) = 0


Workbooks.Open ExcelPath & “\” & WorkbookFileName

Set WorkbookObject = ActiveWorkbook

WorkbookObject.Worksheets.PrintOut Copies:=1, ActivePrinter:=“PDFCreator”

WorkbookObject.Close False

Application.Wait Now + TimeValue(“00:00:05”)

PDFTask.cPrinterStop = False

Application.Wait Now + TimeValue(“00:00:05”)


Set PDFTask = Nothing

WorkbookFileName = Dir()