Printing from Excel VBA

Question

Trying to print the same excel sheet a number of times (e.g 100) with a cell incremented each time (e.g cell 4F).

I tried using

Workbook_BeforePrint

to increment the cell, but it requires interaction with the "select printer" dialog for each printed sheet.

Would it be possible to make something like:

a = getIntegerUserInput()
for i in 1..a
   increment 4F with one
   print the sheet suppressing the "select printer" dialog
end for

Cheers

score 4 · Accepted Answer · answered Oct 06 '11 at 06:31

Have you selected a default printer?

I used this:

Sub printinc()
For i = 0 To 3
Range("A1").Value = Range("A1").Value + 1
Sheets("Sheet1").PrintOut
Next
End Sub

It printed 4 copies incrementing the value in cell A1 each time without prompting me for settings or printer selection.

score 3 · Answer 2 · edited May 23 '17 at 12:09

To print a sheet, you can use this kind of code (assuming you know on which printer you want to print) using PrintOut:

Sub PrintFile()
    Dim curPrinter As String
    curPrinter = Application.ActivePrinter
    Application.ActivePrinter = "Myprinter"
    ActiveWindow.SelectedSheets.PrintOut 
    Application.ActivePrinter = curPrinter
End Sub

Hence, you can create a loop to increase a cell and print your worksheet with the increment.

By the way, you could do it using Before_print and if you don't want to display the print dialog, you can set Cancel to False while calling the procedure Private Sub Workbook_BeforePrint( Cancel As Boolean) (ref on MSDN)

You can also read this SO thread to prevent displaying the printing dialog: How do you prevent printing dialog when using Excel PrintOut method.

[EDIT] see Seyren's answer for a working solution on what you want. Yet, take care about the performance if you really wanted to loop 100 times.

score 2 · Answer 3 · answered Oct 06 '11 at 10:33

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    '//supress recursion into this event when we print
    Application.EnableEvents = False
    '//increment
    If Not IsNumeric(ActiveSheet.Range("A1").Value) Then ActiveSheet.Range("A1").Value = 0
    ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + 1
    '//do a default print
    ActiveSheet.PrintOut
    Application.EnableEvents = True
    '//prevent the default print
    Cancel = True
End Sub

Printing from Excel VBA

3 Answers3