mdmackillop
06-01-2005, 04:43 PM
I'm trying to print from up to six spreadsheets to a single pdf file. Selecting all the sheets and printing does not work as it should, so I'm trying for a workaround. The following code snippet should print each spreadsheet to the same temporary file name (OldName), this is then renamed to sequentially numbererd files and these are finally combined using a PDF exe file. I've built in some delay loops but still finding inconsistent results, as per the first item from the debug file. Any ideas/suggestions?
'Print out separate pdf files and rename to consecutive numbers
'Delete numbered files on completion
OldName = Temp & "Valbook.pdf"
For j = 1 To i
If MySheets(j) = "Letterhead" Then
Sheets(MySheets(j)).PrintOut From:=1, To:=1
Else
Sheets(MySheets(j)).PrintOut
End If
'Loop to permit printing to complete
Wait = Timer
Do
DoEvents
Loop Until Timer - Wait > 3
Debug.Print Dir(OldName)
NewName = Temp & "Valbook" & j & ".pdf"
PDFList = PDFList & NewName & " "
Name OldName As NewName
Wait = Timer
Do
DoEvents
Loop Until Timer - Wait > 2
Debug.Print MySheets(j) & " - " & Dir(NewName)
Next
PDFList = "C:\pdf995\combine.exe " & PDFList & OldName
'Combine PDFs into a single document; move to JobFolder
Shell PDFList
Debug output
Valbook.pdf
Letterhead -
Valbook.pdf
tmpFinStat - Valbook2.pdf
Valbook.pdf
tmpInstruct - Valbook3.pdf
Valbook.pdf
tmpVaries - Valbook4.pdf
Valbook.pdf
Certificate - Valbook5.pdf
'Print out separate pdf files and rename to consecutive numbers
'Delete numbered files on completion
OldName = Temp & "Valbook.pdf"
For j = 1 To i
If MySheets(j) = "Letterhead" Then
Sheets(MySheets(j)).PrintOut From:=1, To:=1
Else
Sheets(MySheets(j)).PrintOut
End If
'Loop to permit printing to complete
Wait = Timer
Do
DoEvents
Loop Until Timer - Wait > 3
Debug.Print Dir(OldName)
NewName = Temp & "Valbook" & j & ".pdf"
PDFList = PDFList & NewName & " "
Name OldName As NewName
Wait = Timer
Do
DoEvents
Loop Until Timer - Wait > 2
Debug.Print MySheets(j) & " - " & Dir(NewName)
Next
PDFList = "C:\pdf995\combine.exe " & PDFList & OldName
'Combine PDFs into a single document; move to JobFolder
Shell PDFList
Debug output
Valbook.pdf
Letterhead -
Valbook.pdf
tmpFinStat - Valbook2.pdf
Valbook.pdf
tmpInstruct - Valbook3.pdf
Valbook.pdf
tmpVaries - Valbook4.pdf
Valbook.pdf
Certificate - Valbook5.pdf