Batch printing

  • Thread starter Thread starter Phil Berkhof
  • Start date Start date
P

Phil Berkhof

Sorry about the previous post! I guess my mouse got a mind
of it's own. I am doing the following in a loop
Open Workbook
Activate proper worksheet
Set Print Area
Print worksheet
Close Workbook
This will be done on approx 150 to 200 workbooks. My
question: does the PrintOut method 1)simply queue the print
job, in which case I'm wondering: could I run out of memory
during the process and if so, are there workarounds or
2)does it wait for the print job to complete (I don't see
any flag parameter such as Wait/No Wait to request that it
wait to continue coding). I would assume it does the former
but am not sure about whether this could be troublesome. I
don't easily have access to the data it'll be used with so
it's hard to do a test run. I am not sure how much memory a
print queue can allocate or if it caches disk space, etc.
All the innards of printing are foreign to me. Thanks in
advance! Sincerely, Phil
 
If you are on a network, the print queue is managed by the print server.
The print job is cached on a disk somewhere and sent to the printer as it
becomes available. Doing a printout initiates this caching. I believe the
code waits until the job is "printed" meaning cached - stored as a file, and
then moves on. Since it is on a disk, you should not have a problem with
printing away.
 
Phil,

I have a master workbook that opens and prints from many workbooks.
This job would overload our printer so I inserted this into the code.
The pause time is arbitrary.

' Pause macro to prevent print que from overload
Dim PauseTime, Start, Finish, TotalTime
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
 
Thanks Tom! Thanks Steve! I've asked my testers to test >
100 on a local connection and if we run into problems I
will use your pause code Steve. I also wasn't sure how to
pause without being preemptive. DoEvents sounds great!
Steve, do you have any thoughts about where to put your
code? Phil
 
Phil,

You can do it one of two way (maybe more).
Assuming that you are using some kind of loop:
1. Change the pause time to something very small (1 second or less) and
put it after each print in your code.
2. Set up some kind of counter and put it after the print in your code.
Have the counter activate if it is equal to a multiple of 5, or 10, or
.....

My workbooks have many sheets to print. Here's my code. It first opens
a workbook and than prints each sheet. Closes the workbook. Than
pauses. Than goes on to the next.

Sub PrintAllDivisions()
'upgraded 6/2/99

Static ccc, chrt, rank As String, wws As Single
Application.ScreenUpdating = False
Application.EnableEvents = False
nkpi = Sheets("sheet1").Cells(2, 9)
nws = ActiveWorkbook.Worksheets.Count

Application.Dialogs(xlDialogPrinterSetup).Show

' Select division to Print
For x = 1 To WorksheetFunction.Max(Sheets("sheet1").Columns(7))
divname = WorksheetFunction.Index(Sheets("sheet1").Columns(5),
WorksheetFunction.Match(x, Sheets("sheet1").Columns(7), 0), 1)

Workbooks.Open FileName:="\\Buenapark02\kpi\Distribution_KPI\" & divname
& "\" & divname
For z = 3 To ActiveWorkbook.Worksheets.Count

Sheets(z).Select

Application.StatusBar = divname & " in Print Que"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1").Select
linenext:
Next
ActiveWorkbook.Close False

' Pause macro to prevent print que from overload - added 3/7/02
Dim PauseTime, Start, Finish, TotalTime
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

line1:
Next

line2:
Worksheets("buttons").Activate
Cells(3, 7).Activate

Application.StatusBar = ""

Worksheets("rankings").Visible = True
Worksheets("consolidated").Visible = True
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 
Back
Top