How do I schedule a task to print a document

H

Hershmab

I have an Excel document with date formulas to keep it up-to-date. I wish to
print its only worksheet automatically once a week. How can I specify the
scheduled task, i.e. how do I run Excel from the command line to print a
document?

I am using Excel 2002 SP3 under Windows XP SP2.
 
G

Gord Dibben

Place some print code in Thisworkbook workbook_open module.

Then you could stick the workbook in your XLSTART folder and set up
Windows Task Scheduler to just start Excel.

Or point task scheduler to a short cut like

"C:\Program Files\Microsoft Office\Exceldata\12months.xls"

You will have to enter a Windows login password if you use one.

See Help and Support "schedule tasks" for more info on how to set up a scheduled
task.


Gord Dibben MS Excel MVP
 
H

Hershmab

(1) I am afraid I do not have the knowledge to understand your first sentence.
(2) If I placed the workbook in XLSTART, would it not start up every time I
open Excel, rather than just once a week?
(3) Do you mean that if I carried out your first sentence and then scheduled
that workbook as a task, the task would automatically print it when started?

Item (3) seems exactly what I would like to do, so please point me at the
instructions for writing print code and placing it in the corresponding
workbook_open module. I am already familiar with the procedure for adding a
scheduled task!
 
G

Gord Dibben

Yes, mistake on my part. Don't stick in XLSTART folder.

The workbook would open each time with Excel and the print code would run....not
good.

You are best going with the second method.

But..........Will you be working on the workbook during the week and just print
when scheduled?

In that case you would have to trap for the date and not activate the print if
not on a specific day of the week

With your workbook active, right-click on the Excel Icon left of "File" on the
menu.

Select "View Code"......................copy/paste this into that module.

Private Sub Workbook_Open()
Dim MyDate, MyWeekDay
MyDate = Date
MyWeekDay = Weekday(MyDate)
If MyWeekDay = 6 Then
Sheets("Sheet1").PrintOut
Else
MsgBox "not a Friday" 'optional
End If
End Sub

When that workbook is opened, Sheet1 will print only if the date is Friday.

Refinements to the print code can be made if you record a macro while making
your print setup then call that macro in the workbook_open code.

Private Sub Workbook_Open()
Dim MyDate, MyWeekDay
MyDate = Date
MyWeekDay = Weekday(MyDate)
If MyWeekDay = 6 Then
MyPrintMacro
Else
MsgBox "not a Friday" 'optional
End If
End Sub


Gord
 
H

Hershmab

That seems to work pretty well. I just need to add a little wrinkle to it for
testing and I know too little VB.... to know how to do it:
I would like the MsgBox to require a Yes/No answer and then to apply a
test based on which has been chosen. How do I script that?
 
G

Gord Dibben

If you just want to test your printout and setup, change the WeekDay to a number
that matches the day you open the workbook and the printout will commence.

6 is Friday.........1 is Sunday, etc.

But if you want to test something else....................

Private Sub Workbook_Open()
Dim MyDate, MyWeekDay
Dim Msg, Style
MyDate = Date
MyWeekDay = Weekday(MyDate)

If MyWeekDay <> 6 Then
Msg = "Not a Friday. Do you want to print anyway?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbYes Then
Sheets("Sheet1").PrintOut
Else
'do something else or just quit
End If
End If
End Sub

See VBA help on msgbox for other parameters.


Gord
 
H

Hershmab

Thanks again, that is just what I need. For some unknown reason I do not have
VBA help available, so I had to ask that very simple-minded question.
 
H

Hershmab

Sorry to have to ask more, but as I just said below I do not have VBA help.
(How can I install it?)

(1) I have set my macro security level to high and (tried to) digitally sign
the relevant VBA project with an existing and valid certificate But every
time I exit the VBA, the digital signature disappears! I do not know whether
to save the workbook or not after resetting the signature.
NB: I have macros in Word also, but I do not get the same problems with
validating them, even though security is set to medium and the macros are NOT
digitally signed.

(2) I have added the workbook to my task schedule, but
(a) it keeps on requiring interactive response about enabling macros
(b) Excel does not close down after printing (and requires a save/no-save
response).
(c) apart from that the macro works, but it would be much simpler if I
could simply schedule a print-only task as from Explorer.
 
G

Gord Dibben

See responses in-line

Sorry to have to ask more, but as I just said below I do not have VBA help.
(How can I install it?)
Click to expand...

Control Panel>Add or Remove Programs>Change>Add or Remove Features

Make sure VBA Help is installed to run from My Computer............you may need
a CD
(1) I have set my macro security level to high and (tried to) digitally sign
the relevant VBA project with an existing and valid certificate But every
time I exit the VBA, the digital signature disappears! I do not know whether
to save the workbook or not after resetting the signature.
NB: I have macros in Word also, but I do not get the same problems with
validating them, even though security is set to medium and the macros are NOT
digitally signed.
Click to expand...

I don't know what's going on there. Have you tried using a signature created by
Office SelfCert tool?
(2) I have added the workbook to my task schedule, but
(a) it keeps on requiring interactive response about enabling macros
Click to expand...

That will clear up when you get your signature working properly
(b) Excel does not close down after printing (and requires a save/no-save
response).
Click to expand...

Add a couple of lines to the Workbook_Open code

ThisWorkbook.Close SaveChanges:=True 'if you want to save
Application.Quit

OR if you don't want to save

ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.Quit
(c) apart from that the macro works, but it would be much simpler if I
could simply schedule a print-only task as from Explorer.
Click to expand...

Excel has no command-line "print" function.


Gord
 
H

Hershmab

Just to thank you - I have got everything working now, have found Selfcert
and VBA help.
 
A

Asif Shah

Gord,
Thanks for the code. It works great for what I need. However, I do have one
issue. I am using the below code:

Private Sub Workbook_Open()
Dim MyDate, MyWeekDay
MyDate = Date
MyWeekDay = Day(MyDate)
If MyWeekDay = 28 Then
Sheets("Sheet1").PrintOut
Else
MsgBox "No Auto Print. Not the 28th"
End If
ThisWorkbook.Saved = True
Application.Quit
End Sub

I need to print my excel file on the 28th and the above code does that. I
will place a shortcut to the excel file so scheduled tasks can print it on
the 28th. However, the issue is that the user will need to get in the file on
other days too to make some edits. If he/she opens the file it will give them
the msgbox and close the file out. Is there something we can add that tells
it not to close if its not the 28th so the user can get in and make changes
and save?
Thanks.
 
Top