CODEWITHSUNDEEP
excelvbaprinting
Leah
Leah

Reputation: 147

Batch Printing in Excel VBA as One Job

I am using Excel to create and print shipping tags. The user fills out the tag (customer name, quantity, etc.). Then, they choose how many units require tags and how many tags must be printed per unit. An example input may be 8 total units with 2 tags per unit. It would then print the tags with unit numbers 1, 1, 2, 2, 3, 3, ... 8, 8. I also included a feature that allows them to change the unit start number such that they could begin part way through a set of units. Using the same example as before but beginning at unit 3, this would print unit numbers 3, 3, 4, 4, 5, 5, ... 8, 8. Lastly, sometimes the final unit will have a different quantity than the rest of the units. To account for this, users can check "Print Partial Unit" and enter a separate unit quantity that will replace the existing unit quantity for only the final unit. This does not affect how the unit numbers print but rather the content of the page when it is printed.

This is the current code:

Sub BatchPrint_Click()

'Set the size of the page numbering variable
Dim PageNumbering() As Integer
ReDim PageNumbering(Range("TotalNo").Value - Range("StartNo").Value)


'Fill the page numbering variable with the appropriate page numbers
Dim i As Integer
For i = 0 To Range("TotalNo").Value - Range("StartNo").Value
    PageNumbering(i) = Range("StartNo").Value + i
Next i

'Save the quantity for reset
Dim Qty As Variant
Qty = Range("Quantity")

'Batch Print Sheets
Dim ii As Integer
For ii = 0 To Range("TotalNo").Value - Range("StartNo").Value
    Range("CurrentPage") = PageNumbering(ii)
    Range("CurrentUnitOf") = PageNumbering(ii)
    If PageNumbering(ii) = Range("TotalNo").Value And Partial.Value = True Then
        Range("Quantity").MergeArea = Range("PartialQuantity")
    End If
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, _
        Copies:=Range("SetNo").Value, Collate:=True, IgnorePrintAreas:=False
Next ii

'Reset the quantity
Range("Quantity") = Qty

End Sub

This brings me to my question: This sends the tags for each individual unit as a separate print job. This isn't typically a problem, but sometimes the user may be printing 200+ tags and, if they notice a mistake in the tag while it is printing, there is no real way to stop all printing because it has been sent to the printer as 200+ little jobs. Is there a way to alter the code such that it is all sent as one print job?

Thank you!

Upvotes: 1

Views: 892

Answers (1)

Craig Lebakken
Craig Lebakken

Reputation: 1943

It's not possible to change Excel's separate print job behavior, but you can save to an intermediate file and print the file as a single print job.

Win2PDF supports an "auto-merge" feature that automatically merges separate Excel print jobs into a single PDF file. It also supports an "Auto-name" feature that can be configured to automatically name and print the PDF file to a paper or label printer. The end result is a single print job being sent to the printer, but the intermediate PDF file will remain in the folder configured in the Auto-name configuration. Your script may be able to delete this file.

Disclosure: I work for the publisher of Win2PDF.

Upvotes: 0

Related Questions