PDFCreator automation in VBA Excel

[JPL007]

Thank you for reading this.
I am trying to automate the creation of pdf documents from word documents in excel. I am using PDFCreator.

I found the different bits of code below online.
However I have two issues:
- a pop up windows comes up and use nothing of the settings I asked for folder where to save. (ideally I would not even want that window to pop up so that I have a full automation).
- It seems to look indefinitely in the end.

Any help would be great. this is driving me insane ;-)
cheers
Julien


Sub GeneratePDF(ByVal sDocumentToConvert As String, ByVal sValue As String, ByVal sNewFolder As String)
Dim pdfjob As PDFCreator.clsPDFCreator
Dim p
p = ActivePrinter
WordApp.ActivePrinter = "PDFCreator"
Set pdfjob = New PDFCreator.clsPDFCreator
With pdfjob
.cStart "/NoProcessingAtStartup"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sNewFolder
.cOption("AutosaveFilename") = sValue
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the document to PDF
wrdDoc.PrintOut
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
ActivePrinter = p
End Sub

[snb]

If you use Office >2003 you don't need an external PDFcreator.

[JPL007]

Hi thank you for your reply. unfortunately, I am doing this from work. And we cannot upgrade excel.
I have office 2003.

[jolivanes]

Found these two possibilities. I don't have PDFCreator so I do not know if and how they perform.



Sub PrintToPDF_Late()
'Author : Ken Puls (www.excelguru.ca (http://www.excelguru.ca/))
Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/)
' Designed for late bind, no references req'd
Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
'/// Change the output file name here! ///
sPDFName = "testPDF.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
With pdfjob
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the document to PDF
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
pdfjob.cClose
Set pdfjob = Nothing
End Sub



Private Sub SaveAsPDF_Click()
Author : snb
With CreateObject("PDFCreator.clsPDFCreator")
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = activeworkbook.path
.cOption("AutosaveFilename") = activesheet.cells(1,2) & ".pdf"
.cOption("AutosaveFormat") = 0
.cClearCache

Workbooks.Open(activeworkbook.path & "\" & activesheet.cells(1,2) & ".xls").Sheets(1).PrintOut 1, "PDFCreator"

Do Until .cCountOfPrintjobs = 1
DoEvents
Loop
.cPrinterStop = False
Do Until .cCountOfPrintjobs = 0
DoEvents
Loop
.cClose
End With
End Sub

[JPL007]

Hi Jolivanes

thank you very much for searching the web for me.
Unfortunately I still have the same issue with the code you found.
- the pdf creator form still pop up (blocking the macro)
- It loops indefinitely at the end.

Kind regards
Julien

[snb]

Did you analyse the code carefully ?


Private Sub SaveAsPDF_Click()
c00="G:\OF\"
c01= "example"

With CreateObject("PDFCreator.clsPDFCreator")
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = c00
.cOption("AutosaveFilename") = c01 & ".pdf"
.cOption("AutosaveFormat") = 0
.cClearCache

thisworkbook.Sheets(1).PrintOut 1, "PDFCreator"

Do Until .cCountOfPrintjobs = 1
DoEvents
Loop
.cPrinterStop = False
Do Until .cCountOfPrintjobs = 0
DoEvents
Loop
.cClose
End With
End Sub

[JPL007]

Hi Snb

I did copy your code, just the modifications below in bold to get it work with my project.
but I still have the pop up window from PDFCreator.
Also it does create the first pdf and then seem to loop for ever.

thank you for your help.
Julien

Sub GeneratePDF(ByVal sDocumentToConvert As String, ByVal sValue As String, ByVal sNewFolder As String)
p = ActivePrinter
WordApp.ActivePrinter = "PDFCreator"

With CreateObject("PDFCreator.clsPDFCreator")
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sNewFolder
.cOption("AutosaveFilename") = sValue & ".pdf"
.cOption("AutosaveFormat") = 0
.cClearCache

wrdDoc.PrintOut

Do Until .cCountOfPrintjobs = 1
DoEvents
Loop
.cPrinterStop = False
Do Until .cCountOfPrintjobs = 0
DoEvents
Loop
.cClose
End With

ActivePrinter = p

End Sub

[snb]

You didn't copy, because I do not use arguments in the macro.

You should also :

- use code tags
- post the code that 'calls' this macro.

[JPL007]

filename = "2012-10-29 Call Option on CAR CS vs B"
buyerTSPath is the path of the file
WrdDoc is the active word document.

Kind regards
Julien


VBA:



Call GeneratePDF( fileName, buyerTSPath)






Sub GeneratePDF( ByVal sValue As String, ByVal sNewFolder As String)
p = ActivePrinter
WordApp.ActivePrinter = "PDFCreator"
With CreateObject("PDFCreator.clsPDFCreator")
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sNewFolder
.cOption("AutosaveFilename") = sValue & ".pdf"
.cOption("AutosaveFormat") = 0
.cClearCache
wrdDoc.PrintOut
Do Until .cCountOfPrintjobs = 1
DoEvents
Loop
.cPrinterStop = False
Do Until .cCountOfPrintjobs = 0
DoEvents
Loop
.cClose
End With
ActivePrinter = p
End Sub

[snb]

How do you refer to Word from Excel ?

[JPL007]

Sub wordDocOpenOrActivate(theFileName As String, thePath As String)

' same behaviour if defined:
'Dim WordApp As Word.Application


Dim tmpDoc As Object
Dim WDoc As String
Dim myDoc As String

myDoc = theFileName
WDoc = thePath & myDoc & ".doc"

On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If WordApp Is Nothing Then
' no current word application
Set WordApp = CreateObject("Word.application")
Set wrdDoc = WordApp.Documents.Open(WDoc)
Else
' word app running
For Each tmpDoc In WordApp.Documents
If StrComp(tmpDoc.FullName, WDoc, vbTextCompare) = 0 Then
' this is your doc
Set wrdDoc = tmpDoc
Exit For
End If
Next
If tmpDoc Is Nothing Then
' not open
Set wrdDoc = WordApp.Documents.Open(WDoc)
End If
End If

WordApp.Visible = True
WordApp.Activate
WordApp.Windows(wrdDoc).WindowState = wdWindowStateNormal

End Sub