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


[VBA]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[/VBA]

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


[VBA]
Sub PrintToPDF_Late()
'Author : Ken Puls (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
[/VBA]

[VBA]
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

[/VBA]

[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 ?

[VBA]
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
[/VBA]

[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

[VBA]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[/VBA]

[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:

[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
[/VBA]

[snb]

How do you refer to Word from Excel ?

[JPL007]

[VBA]

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


[/VBA]