MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automatic printing of Pivot report - 3 times a day


Posted by Kevin Mac on December 24, 2001 9:05 AM

Is there anyway, and can someone provide sample code, of how to automatically print a report from a pivot table and/or pivot chart 3 times a day. Say I want it to print to a specific printer on the network at 9:00 am, 2:00 pm, and 5:00 pm. I am also looking for a solution to just send a report of the data in the pivot chart via email 3 times a day as I outlined above.


Posted by Ivan F Moala on December 25, 2001 8:50 PM

Here is an example....change as required OR
repost;

This is the initial procedure to run

Sub RunMyPrint_Procedures()
'Run 1st Job
Application.OnTime TimeValue("16:55:00"), "MyPrintJob1"
End Sub

Sub MyPrintJob1()
MsgBox "running my print job1"
'-------------------------------
'Call your print procedure here
'
'
'
'-------------------------------
'Now call routine to cancell Ontime
CancelOT 1
'Now Call 2nd print job
Application.OnTime TimeValue("16:56:00"), "MyPrintJob2"
End Sub

Sub MyPrintJob2()
MsgBox "running my print job2"
'-------------------------------
'Call your print procedure here
'
'
'
'-------------------------------
'Now call routine to cancell Ontime
CancelOT 2
'Call 2nd print job
Application.OnTime TimeValue("16:57:00"), "MyPrintJob3"
End Sub

Sub MyPrintJob3()
MsgBox "running my print job3"
'-------------------------------
'Call your print procedure here
'
'
'
'-------------------------------
'Now call routine to cancell Ontime
CancelOT 3
End Sub

Sub MyPrint_Procedure()
'This is run to print to
'your network printers
End Sub

Sub CancelOT(Whichone As Single)

Select Case Whichone
Case 1
'Cancell 1st Job
Application.OnTime TimeValue("16:55:00"), "MyPrintJob1", False

Case 2
'Cancell 2nd Job
Application.OnTime TimeValue("16:56:00"), "MyPrintJob2", False

Case 3
'Cancell 3rd job
Application.OnTime TimeValue("16:57:00"), "MyPrintJob3", False

End Select

End Sub

HTH

Ivan