'============================================================================
'- MACRO TO PRINT ALL WORKSHEETS TO PDF995 .PDF FILE
'============================================================================
'- Requires installation of application from http://www.pdf995.com/
'- ** Record macro of a test print to get your version of "PDF995 on Ne01:"
'- ADJUSTMENT : "Wait" lines repeated to enable adjustment to your system
'- (MAY NEED LONGER TIMES FOR BIGGER SHEETS)
'- run from the worksheet - not the VB Editor (produces time delays)
'- ** DO NOT USE THE COMPUTER WHILE THIS IS RUNNING **
'-
'- Brian Baulsom July 2007
'============================================================================
Sub PRINT_SHEETS()
Dim CurrentPrinter As String ' current printer to be reset
Dim PDF995printer As String
Dim ws As Worksheet
Dim wsName As String
'------------------------------------------------------------------------
'- delete test PDF files (tested on workbook with 2 sheets)
On Error Resume Next
Kill "C:\PDF995\Sheet1.PDF"
Kill "C:\PDF995\Sheet2.PDF"
On Error GoTo 0
'------------------------------------------------------------------------
'- save current printer
CurrentPrinter = Application.ActivePrinter
'------------------------------------------------------------------------
'- ** set PDF995 printer ** needs your version
Application.ActivePrinter = "PDF995 on Ne01:"
'------------------------------------------------------------------------
'- Print worksheets one at a time
For Each ws In Worksheets
'Set ws = ActiveSheet
wsName = ws.Name
Application.StatusBar = ws.Name
'--------------------------------------------------------------------
'- Print Sheet
ws.PrintOut
DoEvents
'--------------------------------------------------------------------
'- PDF995 dialog box
'- stop code until File Save dialog opens
Application.Wait Now + TimeValue("00:00:05") ' 5 seconds
'--------------------------------------------------------------------
'- enter worksheet name into dialog box
SendKeys wsName, True
Application.Wait Now + TimeValue("00:00:01") ' 1 second
'--------------------------------------------------------------------
'- close File Save dialog - requires keypresses - 2 Tabs and Enter
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
'--------------------------------------------------------------------
'- stop code until ...
'- PDF995 application opens - file saves - Acrobate appears
Application.Wait Now + TimeValue("00:00:15") ' 15 seconds
'--------------------------------------------------------------------
'- close Acrobat
SendKeys "%{F4}", True ' keys ALT+F4
DoEvents
Application.Wait Now + TimeValue("00:00:01") ' 1 second
Next
'------------------------------------------------------------------------
'- reset printer
Application.ActivePrinter = CurrentPrinter
MsgBox ("Done")
Application.StatusBar = False
End Sub
'=========== END OF PROCEDURE ===============================================