PDA

View Full Version : Auto printing command



idiot_na
10-12-2008, 06:54 AM
Hi all

I have an excel which is always on logging datas.
Isit possible to set it to autoprint the whole excel file every 1st day of the month?.
If so it would be great if i can have the example of how the code is written, as i can only find the code .ontime and not .onmonth

Many Thanks.

Demosthine
10-12-2008, 11:20 AM
Good Morning.

I would say using the OnTime Function is probably your best choice since the workbook is always open. Set OnTime "12:00 AM", "AutoPrint_Workbook".

Then, inside your AutoPrint_Workbook Function, check to ensure the day of the month is the first day.


If Day(Now()) = 1 Then
' Print the Workbook
Else
' Don't print the Workbook.
End If


Hope this helps.
Scott

idiot_na
10-12-2008, 06:44 PM
Hi demosthine

Thanks for the quick reply. are you able to write a full sample on how the full code would look like? Sorry my vb skills are very basic.

Demosthine
10-12-2008, 07:02 PM
Hey There.

I'm sure I can as I've done quite a bit of automation like that. I'll need you to provide a list of all of your requirements (and the Workbook if you can), such as Number of Copies, Print All Sheets or Selected Sheets, Default Printer or Other Printer, etc.

Let me know.
Scott

idiot_na
10-12-2008, 07:25 PM
Hi demosthine

I currently dun have the file with mi hope these are the info u need.

Excel file is name Billing
inside there are about 70+ sheets (tenant 1,2 and so on)
i have already writtern the linking and formule for each sheets, so every month end each sheet will be auto updated with that month's info.

i just need the excel to auto printout the whole billing file @ the start of every new month.

Demosthine
10-12-2008, 07:42 PM
Hi again.

Try this:


' ThisWorkbook Class Module
Option Explicit

Private Sub Workbook_Open()
Application.OnTime "12:00 AM", "OnTime_AutoPrint"
End Sub



' modAutoPrint Module
Option Explicit

Public Sub OnTime_AutoPrint()
If Day(Now()) = 1 Then
With ThisWorkbook
.PrintOut
End With
Else
' Don't Print.
End If
End Sub


Scott

idiot_na
10-12-2008, 08:13 PM
Hi

I tried the codes with a blank excel
Putting the first code into a class module
2nd code into a module

but it did not autoprint when it reaches the time.

Did i place the codes in the wrong place?

Demosthine
10-12-2008, 08:19 PM
The first set of code should not be placed into a Class Module, but should be placed into the ThisWorkbook class module. In your Project Explorer, double click on the Object ThisWorkbook.

The second key to this is that the initial OnTime assignment occurs when the workbook opens. You can either save the workbook, close it, and then open it again for the Workbook_Open Event to run. Or you can place your cursor inside the Workbook_Open Event and press F5. Either way, wait for the time and it should work.

Scott

idiot_na
10-12-2008, 08:52 PM
Hi again

Iam still unable to achive the autoprinting.
Attached is a blank excel i tested with the codes. Do advice mi on where i went wrong.

Very sorry for the trouble.

GTO
10-13-2008, 08:21 AM
' ThisWorkbook Class Module
Option Explicit

Private Sub Workbook_Open()
'// Hey there - as Scott is a pal of mine, I'm sure he won't mind me advising //
'// of a small tweak or two. Neither of the these will work,... //
' Application.OnTime "13:00:00", "OnTime_AutoPrint"
' Application.OnTime "12:00 AM", "OnTime_AutoPrint"

'//... but this will work, //
' Application.OnTime #13:00:00#, "OnTime_AutoPrint"
'// (If you do use a date literal, the above may be "corrected" by Excel //
'// automatically... to the below.) //
' Application.OnTime #1:00:00 PM#, "OnTime_AutoPrint"
'// ... or: //
Application.OnTime EarliestTime:=TimeValue("13:00:00"), _
Procedure:="OnTime_AutoPrint", _
Schedule:=True

'// Note: Very easy mistake to make, I've done it numerous times and //
'// still occassionally get to stare at the "Stupid laptop!" wondering why its //
'// not working... //
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'// In case the workbook is closed, I try and remember to remove the running //
'// background timer. //
Application.OnTime EarliestTime:=TimeValue("13:00:00"), _
Procedure:="OnTime_AutoPrint", _
Schedule:=False
End Sub


Hello...

See if the above rectifies?

Mark

idiot_na
10-13-2008, 08:39 AM
Hi GTO

Thanks for the reply
I update to the new code but it just wont print @ the stated timing....

GTO
10-13-2008, 08:58 AM
I hate to ask, but are you remembering to change the date (either the computer's date or in the Sub) and the timevalue to something that will run in a few minutes (for testing)?

Mark

idiot_na
10-13-2008, 09:52 AM
Yap i did i even tested the days befor and after the date i set. Even tried with different months. I just cant seem to get it to work.....

GTO
10-13-2008, 11:18 AM
Yap i did i even tested the days befor and after the date i set. Even tried with different months. I just cant seem to get it to work.....

Here is the code under class ThisWorkbook
' ThisWorkbook Class Module
Option Explicit

Private Sub Workbook_Open()
Application.OnTime EarliestTime:=TimeValue("11:05:00"), _
Procedure:="OnTime_AutoPrint", _
Schedule:=True

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("11:05:00"), _
Procedure:="OnTime_AutoPrint", _
Schedule:=True
End Sub


And here is the procedure from a standard module ( I tossed in the MsgBox due to not having a printer installed.)
' modAutoPrint Module
Option Explicit

Public Sub OnTime_AutoPrint()
If Day(Now()) = 13 Then

MsgBox "Hello!"


With ThisWorkbook
.PrintOut
End With
Else
' Don't Print.
End If
End Sub


I just ran it, and it was fine. Please see whether mods to the Day or time are the issue.

Shooot, it's a tiny file, here's a copy... Hope this helps.

Mark

GTO
10-13-2008, 11:21 AM
OOPs. Won't effect running, but set 'Schedule:=False' under closing.

Mark

idiot_na
10-14-2008, 09:42 AM
Hi GTO

Sorry for the late reply
I tried the sample u have attached.
The code did work when i press F5 the first time, as the day which as coded was same with my desktop date. but after i manually set my desktop date to the nxt month and adjusting the time to watch wad happens when the time auto change over to the selected date. But nuting seems to happen. Or is there an internal lag? or am i verfiying the code incorrectly?

Demosthine
10-14-2008, 05:02 PM
Good Evening again.

I'm glad GTO stepped in to correct my slight oversight. Usually we aren't assigning static times like that, so we use the TimeValue Function which does some of the conversion for us. But hey, life is one giant learning experience.


In regard to the OnTime event not running the second time, you have to set a new OnTime event every time you want it to run. Once the Workbook opens and reaches the OnTime declared in the Workbook_Open Event, it executes it and clears the time from the Application. At the end of your OnTime_AutoPrint, you'll want to add the same set of code that you have in your Workbook_Open.


Option Explicit

Public Sub OnTime_AutoPrint()
If Day(Now()) = 13 Then

MsgBox "Hello!"

With ThisWorkbook
.PrintOut
End With
Else
' Don't Print.
End If


Application.OnTime EarliestTime:=TimeValue("11:05:00"), _
Procedure:="OnTime_AutoPrint", _
Schedule:=True
End Sub


Scott

GTO
10-14-2008, 05:34 PM
Just to specify my previous self correction, I did goober up the BeforeClose at post# 14. 'Schedule' should be FALSE, so in case the workbook is closed, the timer will be cancelled. Sorry about that :-(

Mark

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("11:05:00"), _
Procedure:="OnTime_AutoPrint", _
Schedule:=False
End Sub

idiot_na
10-15-2008, 05:35 AM
Hi all

Thanks for the all the feedbacks, I finally got the macro to run as required. Could not have done it without all the help i have gotten in here.

Just a small qns. the macro seems to be printing 1 more new set each new month. Isit because i cancelled the previous month printing?(knowing that it sents the file to the print que was enought) i tried going up to a year and by the last month its quing up 12 files when the marco activates.

GTO
10-15-2008, 06:31 AM
...Just a small qns. the macro seems to be printing 1 more new set each new month. Isit because i cancelled the previous month printing? (italics added)

Eh? My answer is a "sorta" and "maybe."

My bet is that you cancelled the first print, then went and tested for the next month, and so on... Most likely, the first print job has not actually deleted and jobs are stacking behind it; making it appear that its now sending two, three, etc print jobs.

Mark