Using Win7 and PDFCreator 1.2
I'm attempting to run a FOR loop in which each time:
-a small change occurs to a worksheet
-the worksheet is sent to PDF creator
At the end of the FOR loop, all the printed worksheets should be in one PDF after being combined by PDFcreator.
So for example, the worksheet would increment the value of cell A5 beginging at 1 for every iteration and send that worksheet to be printed. If I set the for loop to go from 1 to 10, then final product should be a single PDF with each page reading 1,2,3,4 ... 10. However it will be out of order, e.g. 1,5,2,3...10. What is the cause of this?
Here's my code, mainly taken from excelguru's tutorial:
Private Sub Test_Click()
'Author: John Carey. Credit is also due to Ken Puls of excelguru.ca for tutorial guidance on using PDF Creator with excel VBA. Tutorial can be found here: http://www.excelguru.ca/content.php?161
Dim i As Integer
Dim cellName As Integer
Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
Dim StartPg As Integer
Dim EndPg As Integer
StartPg = 1
EndPg = 15
sPDFName = "combinded.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
For i = StartPg To EndPg
cellName = i
Range("A5").Select
ActiveCell.Value = cellName
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
Next i
Do Until pdfjob.cCountOfPrintJobs = EndPg
DoEvents
Loop
With pdfjob
.cCombineAll
.cPrinterStop = False
End With
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName
pdfjob.cClose
Set pdfjob = Nothing
End Sub