CODEWITHSUNDEEP
vbaexcel
abcde123483
abcde123483

Reputation: 3905

Printing from Excel VBA

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

Upvotes: 1

Views: 20642

Answers (3)

Alex K.
Alex K.

Reputation: 175976

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

Upvotes: 2

JMax
JMax

Reputation: 26611

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.

Upvotes: 3

Seyren
Seyren

Reputation: 340

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.

Upvotes: 4

Related Questions