Print each worksheet to printer of same name

zakasnak

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
I have a workbook with 150-some-odd sheets, each with a store number. I would like to loop through each sheet & print that sheet to the printer with the same name in my printers & faxes queue.

(eg. sheet 9604 would print to printer name oki9604)

Is this possible?

Thanks for any help you can give me!
 
Sort by date Sort by votes
I'm excited to try this.

A couple of questions first:

Does the RESULTS sheet need to be a sheet within the workbook being printed?

Will it matter if NE## only contains 2 numeric positions when I know I have more than 99 printer network ports?

"For PortNumber = 0 To 5" - Wouldn't this number need to be large enough to encompass ALL of my possible port numbers?

Thanks for your time, BrianB! Amazing!
 
Upvote 0
This version works Ok. The 'On Error GoTo 0' line was in the wrong place involving a couple of slight adjustments.
Rich (BB code): 
'==================================================================================================
'- VERSION 2 : PRINT WORKSHEETS TO PRINTER NUMBER IN WORKSHEET NAME
'- UNKNOWN PORT Nexx: NUMBER - SO TRY SEVERAL
'- *****  NB. I have not been able to test this fully at home ******
' =================================================================================================
'- Traps error produced by trying to print to an invalid printer and continues trying
'- In processes such as this it is usually best to concatenate strings to a single variable
'-   because often the method (eg. 'Printout' here) cannot handle it.
'--------------------------------------------------------------------------------------------------
'- RECORD RESULTS (NEEDS A SHEET CALLED "Results")
'- It may be a good idea to keep positive results in a sheet and use it next time instead of this
'---------------------------------------------------------------------------------------------------
'- Sample code from recorded macro. We do not use this exactly :
'-  ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
'-    ActivePrinter:= "\\winprint\oki9603 on Ne09:", Collate:=True
'- Brian Baulsom April 2009
'===================================================================================================
Dim OriginalPrinter As String
Dim ResultsSheet As Worksheet
Dim ToRow As Long
Dim ws As Worksheet
Dim PrinterName As String
Dim PrinterNumber As String
Dim PrinterPort As String
Dim PrinterFullName As String
Dim PortNumber As Integer
'===================================================================================================
'- MAIN ROUTINE
'===================================================================================================
Sub NETWORK_PRINT()
    OriginalPrinter = Application.ActivePrinter
    PrinterName = "\\winprint\oki"
    Application.Calculation = xlCalculationManual
    On Error Resume Next            ' ERROR TRAP - code continues
    '-----------------------------------------------------------------------------------------------
    '- RESULTS SHEET
    Set ResultsSheet = Worksheets("Results")
    ResultsSheet.Cells.ClearContents
    ToRow = 2
    ResultsSheet.Cells(ToRow, 1).Value = "No printers found"
    '-----------------------------------------------------------------------------------------------
    '- LOOP WORKSHEETS
    For Each ws In ActiveWorkbook.Worksheets
        PrinterNumber = ws.name
        If PrinterNumber <> "Results" Then
            '---------------------------------------------------------------------------------------
            '- LOOP POSSIBLE PORT NUMBERS
            For PortNumber = 0 To 300
                PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
                PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
                Application.StatusBar = "  Trying printer : " & PrinterFullName
                '-----------------------------------------------------------------------------------
                'MsgBox (PrinterFullName)  ' for testing
                '-----------------------------------------------------------------------------------
                '- TRY TO SET ACTIVEPRINTER
                Application.ActivePrinter = PrinterFullName
                '-----------------------------------------------------------------------------------
                '- SUCCESS - record result. exit the loop ... or try again
                If Err.Number = 0 Then
                    ws.PrintOut
                    With ResultsSheet
                        .Cells(ToRow, 1).Value = PrinterNumber
                        .Cells(ToRow, 2).Value = PrinterFullName
                    End With
                    Exit For
                Else
                    Err.Clear   ' clear for next error
                End If
                '-----------------------------------------------------------------------------------
            Next
             '- end of PortNumber loop
            '---------------------------------------------------------------------------------------
       End If
        '- Next worksheet
    Next
    '-----------------------------------------------------------------------------------------------
    '- FINISH
    MsgBox ("done")
    Application.ActivePrinter = OriginalPrinter
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0                 ' reset to trap any other errors
End Sub
'------------ eop ----------------------------------------------------------------------------------
 
Upvote 0
Does the RESULTS sheet need to be a sheet within the workbook being printed? -
Click to expand...
** might just as well be. otherwise need to include the external workbook name

Will it matter if NE## only contains 2 numeric positions when I know I have more than 99 printer network ports?
Click to expand...
** Try it out. See below.

"For PortNumber = 0 To 5" - Wouldn't this number need to be large enough to encompass ALL of my possible port numbers?
Click to expand...
** new code has 0 to 300. Try it out.
 
Upvote 0
OMG... O ... M ... G!!!!

That is AMAZING! It works PERFECTLY!

The Results page is supposed to show what exactly? I have one entry:

9896 \\winprint\oki9896 on Ne164:

Would this be an error? Or should it say "No Printers Found"?

Just FYI, there is a printer named oki9896.

THANK YOU!
 
Last edited:
Upvote 0
oops. missed a bit. This bit of code should read
Code: 
                    With ResultsSheet
                        .Cells(ToRow, 1).Value = PrinterNumber
                        .Cells(ToRow, 2).Value = PrinterFullName
                       ToRow=ToRow +1          ' ** added line
                    End With

You should end up with a list of valid printers which can be used instead of using this code to check each one. However, it would then need provision for checking/updating it. If this code does not take too long to run it is probably not worth the bother.

As it stands it is a check that everything has been printed.
 
Upvote 0
I'll adjust the code when I get to work & give it another try! Wow!

I was thinking.... is there a way to add the results page in the code, instead of manually?

I really appreciate the time you've put into this. So does everyone else at my office. :)
 
Upvote 0
BrianB,

another question.... is there a way we can modify the results to show what DOESN'T print?

Again, WOW! ...and thanks!
 
Upvote 0
This records all the results. You can remove the "success" code if required.
Rich (BB code): 
'==================================================================================================
'- VERSION 4 : PRINT WORKSHEETS TO PRINTER NUMBER IN WORKSHEET NAME
'- Changed method of recording results.
'- UNKNOWN PORT Nexx: NUMBER - SO TRY SEVERAL
'- *****  NB. I have not been able to test this fully at home ******
' =================================================================================================
'- Traps error produced by trying to set an invalid printer and continues trying
'- In processes such as this it is usually best to concatenate strings to a single variable
'-   because often the method (eg. 'Printout' here) cannot handle it.
'--------------------------------------------------------------------------------------------------
'- RECORD RESULTS (NEEDS A SHEET CALLED "Results")
'---------------------------------------------------------------------------------------------------
'- Sample code from recorded macro. We do not use this exactly :
'-  ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
'-    ActivePrinter:= "\\winprint\oki9603 on Ne09:", Collate:=True
'- Brian Baulsom April 2009
'===================================================================================================
Dim OriginalPrinter As String
Dim ResultsSheet As Worksheet
Dim ToRow As Long
Dim ws As Worksheet
Dim PrinterName As String
Dim PrinterNumber As String
Dim PrinterPort As String
Dim PrinterFullName As String
Dim PortNumber As Integer
Dim SuccessfulPrintout As Boolean
'===================================================================================================
'- MAIN ROUTINE
'===================================================================================================
Sub NETWORK_PRINT()
    OriginalPrinter = Application.ActivePrinter
    PrinterName = "\\winprint\oki"
   Application.Calculation = xlCalculationManual
    On Error Resume Next            ' ERROR TRAP - code continues
    '-----------------------------------------------------------------------------------------------
    '- RESULTS SHEET
    Set ResultsSheet = Worksheets("Results")
    ResultsSheet.Cells.ClearContents
    ToRow = 2
    ResultsSheet.Cells(ToRow, 1).Value = "No printers found"
    '-----------------------------------------------------------------------------------------------
    '- LOOP WORKSHEETS
    For Each ws In ActiveWorkbook.Worksheets
        PrinterNumber = ws.name
        SuccessfulPrintout = False
        If UCase(PrinterNumber) <> "RESULTS" Then
            '---------------------------------------------------------------------------------------
            '- LOOP POSSIBLE PORT NUMBERS
            For PortNumber = 0 To 300
                PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
                PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
                Application.StatusBar = "  Trying printer : " & PrinterFullName
                '-----------------------------------------------------------------------------------
                'MsgBox (PrinterFullName)  ' for testing
                '-----------------------------------------------------------------------------------
                '- TRY TO SET ACTIVEPRINTER
                Application.ActivePrinter = PrinterFullName
                '-----------------------------------------------------------------------------------
                '- SUCCESS - record result. exit the loop ... or try again
                If Err.Number = 0 Then
                    ws.PrintOut
                    SuccessfulPrintout = True
                    Exit For
                Else
                    Err.Clear   ' clear for next error
                End If
                '-----------------------------------------------------------------------------------
            Next
             '- end of PortNumber loop
            '---------------------------------------------------------------------------------------
            '- RECORD RESULTS
            '---------------------------------------------------------------------------------------
             With ResultsSheet
                 .Cells(ToRow, 1).Value = PrinterNumber
                 If SuccessfulPrintout = True Then
                     .Cells(ToRow, 2).Value = PrinterFullName
                     .Cells(ToRow, 3).Value = "SUCCESS"
                 Else
                     .Cells(ToRow, 3).Value = "**FAILURE**"
                 End If
                 ToRow = ToRow + 1
             End With
            '---------------------------------------------------------------------------------------
       End If
        '- Next worksheet
    Next
    '-----------------------------------------------------------------------------------------------
    '- FINISH
    MsgBox ("done")
    Application.ActivePrinter = OriginalPrinter
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'------------ eop ----------------------------------------------------------------------------------
 
Upvote 0
BrianB,

This works great. However, it didn't put in the RESULTS page... Is that something I'll have to do manually?

Thank you, again & again :D
 
Upvote 0
I figured it out - to add the RESULTS sheet within the code:

Rich (BB code): 
'==================================================================================================
'- VERSION 4 : PRINT WORKSHEETS TO PRINTER NUMBER IN WORKSHEET NAME
'- Changed method of recording results.
'- UNKNOWN PORT Nexx: NUMBER - SO TRY SEVERAL
'- *****  NB. I have not been able to test this fully at home ******
' =================================================================================================
'- Traps error produced by trying to set an invalid printer and continues trying
'- In processes such as this it is usually best to concatenate strings to a single variable
'-   because often the method (eg. 'Printout' here) cannot handle it.
'--------------------------------------------------------------------------------------------------
'- RECORD RESULTS (NEEDS A SHEET CALLED "Results")
'---------------------------------------------------------------------------------------------------
'- Sample code from recorded macro. We do not use this exactly :
'-  ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
'-    ActivePrinter:= "\\winprint\oki9603 on Ne09:", Collate:=True
'- Brian Baulsom April 2009
'===================================================================================================
Dim OriginalPrinter As String
Dim ResultsSheet As Worksheet
Dim ToRow As Long
Dim ws As Worksheet
Dim PrinterName As String
Dim PrinterNumber As String
Dim PrinterPort As String
Dim PrinterFullName As String
Dim PortNumber As Integer
Dim SuccessfulPrintout As Boolean
'===================================================================================================
'- MAIN ROUTINE
'===================================================================================================
Sub NETWORK_PRINT_results()
    OriginalPrinter = Application.ActivePrinter
    PrinterName = "\\winprint\oki"
   Application.Calculation = xlCalculationManual
    On Error Resume Next            ' ERROR TRAP - code continues
    '-----------------------------------------------------------------------------------------------
    '- RESULTS SHEET
    Sheets.Add
    ActiveSheet.Name = “Results”
    Set ResultsSheet = Worksheets("Results")
    ResultsSheet.Cells.ClearContents
    ToRow = 2
    ResultsSheet.Cells(ToRow, 1).Value = "No printers found"
    '-----------------------------------------------------------------------------------------------
    '- LOOP WORKSHEETS
    For Each ws In ActiveWorkbook.Worksheets
        PrinterNumber = ws.name
        SuccessfulPrintout = False
        If UCase(PrinterNumber) <> "RESULTS" Then
            '---------------------------------------------------------------------------------------
            '- LOOP POSSIBLE PORT NUMBERS
            For PortNumber = 0 To 300
                PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
                PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
                Application.StatusBar = "  Trying printer : " & PrinterFullName
                '-----------------------------------------------------------------------------------
                'MsgBox (PrinterFullName)  ' for testing
                '-----------------------------------------------------------------------------------
                '- TRY TO SET ACTIVEPRINTER
                Application.ActivePrinter = PrinterFullName
                '-----------------------------------------------------------------------------------
                '- SUCCESS - record result. exit the loop ... or try again
                If Err.Number = 0 Then
                    ws.PrintOut
                    SuccessfulPrintout = True
                    Exit For
                Else
                    Err.Clear   ' clear for next error
                End If
                '-----------------------------------------------------------------------------------
            Next
             '- end of PortNumber loop
            '---------------------------------------------------------------------------------------
            '- RECORD RESULTS
            '---------------------------------------------------------------------------------------
             With ResultsSheet
                 .Cells(ToRow, 1).Value = PrinterNumber
                 If SuccessfulPrintout = True Then
                     .Cells(ToRow, 2).Value = PrinterFullName
                     .Cells(ToRow, 3).Value = "SUCCESS"
                 Else
                     .Cells(ToRow, 3).Value = "**FAILURE**"
                 End If
                 ToRow = ToRow + 1
             End With
            '---------------------------------------------------------------------------------------
       End If
        '- Next worksheet
    Next
    '-----------------------------------------------------------------------------------------------
    '- FINISH
    MsgBox ("done")
    Application.ActivePrinter = OriginalPrinter
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'------------ eop ----------------------------------------------------------------------------------

Thanks so much for all of your help, BrianB! I never could've done it without you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Printing to Zebra label printer
Replies
2
Views
477
Jeffrey Mahoney
Jeffrey Mahoney
4
  • Question Question
VBA to Print Labels but Issues Selecting Printer
Replies
1
Views
571
rpaulson
rpaulson
D
  • Question Question
List Network Printers
Replies
1
Views
399
RoryA
RoryA
L
  • Solved
VBA code to loop through all options within a single slicer and print individual reports for each
Replies
2
Views
254
lorbieckit
L
J
  • Question Question
Macro to assign to print button with specific criteria
2
Replies
11
Views
952
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top