Automatic VBA printing

N

noobdisaster

I need to have a multi page workbook print at a certain time of the day
Is this possible in VBA
 
N

noobdisaster

I tried and tried, but I can't get it to work. I guess I'm not
understanding Chip Pearson's instructions. I keep getting a compilling
error.

This is the code that I have been tinkering with:

Public RunWhen As Double
Public cRunIntervalSeconds = 10 ' two minutes
Public cRunWhat = "The_Sub"
Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub


Sub The_Sub()
'
' Printing Macro
'

'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
Sub Macro3()
'
' Macro1 Macro
'

'
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

'
StartTimer

End Sub
 
D

Dave Peterson

First, excel has to be open for this to work. If that's not possible, then this
technique won't work.

Chip's code shows how to run a macro every xx minutes/seconds.

You only want to run it once a day.

Maybe something like:

Option Explicit
Public RunWhen As Double
Public Const cRunWhat = "The_Sub"
Sub auto_open()
RunWhen = Date + TimeSerial(12, 35, 0)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
Sub The_Sub()
ThisWorkbook.Worksheets("sheet1").PrintOut _
preview:=True, Copies:=1, Collate:=True
End Sub

I changed the worksheet to be printed. Who knows what window and what
worksheets will be active? (also, I used preview:=true to save paper while
testing.

This line tells when to run it:

RunWhen = Date + TimeSerial(12, 35, 0)
12:35:00 for today.

You can still use Chip's stoptimer routine if you need to.
 
N

noobdisaster

Thanks Dave, but what does the date and time code mean? I have searched
but will continue searching. I need to be able to print this at 2 A
five days a week (not Saturday and Sunday). the work book can be lef
open
 
D

Dave Peterson

Each time you want to run this procedure, you have to tell it when to start.

In my example: RunWhen = Date + TimeSerial(12, 35, 0)

Date is today's date. Timeserial(12,35,0) means 12:35 PM.

So if you opened the workbook during the day (say 4:00PM), you could use:

RunWhen = Date + 1 + TimeSerial(2, 0, 0)

date + 1 is tomorrow's date.
timeserial(2,0,0) is 2:00 AM.

=====

You may find that making sure excel is open and running this code is not what
you want. You may want to use a windows scheduling program that can accomplish
this same kind of thing.

You'd just name the print subroutine auto_open() and then have that scheduling
program start excel (and your workbook) at your specified times.

Some versions of window have this kind of scheduling program built in. But
there are lots of free programs out there, too. Searching google.com or
shareware.com may lead you to something you like.
 
N

noobdisaster

I will look into the auto open option. If I understand you correctly
the macro has to be changed every day to tell the program to print
Isn't there a way to set it to repeatedly print (Mon, Tue, Wed, Thu
Fri, Mon, Tue, etc.)? I seem to just be getting more confused.
apologize and thanks again
 
D

Dave Peterson

Date will change to match the current date--just like the date/time in your
windows taskbar.

But excel has to stay open once you run this macro that sets the time/date to do
the printing.

So if you don't use a windows scheduling program, you'll be responsible for
opening this workbook each day to set it up for printing the next morning.

And since I wouldn't want to show up on Sunday to make sure it prints at 2:00 AM
Monday, it sounds like a windows scheduling program would be much better--you
will have to leave the pc on, though.

I use a program called "nnChron lite". If my pc is off and the scheduled time
goes by, then it will run that tasks the next time my pc opens (if I recall
correctly, this is an option for each task--but it's been a long time since I
read the help file).

http://www.nncron.ru/

The file where you set up the tasks is kind of ugly (more UNIX based than human
based!), but after you read the manual a few times, it seems to work perfectly
(for me anyway).
 
D

Dave Peterson

And just to add, if "nnChron lite" isn't powerful enough, you can use nnChron
(also on that same web page).
 
Top