Print each worksheet to printer of same name

zakasnak · Apr 14, 2009

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!

zakasnak · Apr 15, 2009

Bump ?

zakasnak said:
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!

zakasnak · Apr 16, 2009

No takers? This would help me out a lot if there are any suggestions out there, even where to start.....

Last bump, I promise.

zakasnak said:
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!

zakasnak · Apr 20, 2009

I recorded this part to see if I could figure out a way to set this up.

Question: Is the Ne## a constant number? Is it only constant on my pc, would be a different number on another's pc?

I would like to have the printers auto-selected based on the name of the sheet. As you can see, the printers on the network are named "okiSTORENUMBER which would be the name of each sheet.

Code:

Sub network_print()
'
' network_print Macro
' Macro recorded 4/20/2009 by Carole James
'

    Sheets("9603").Select
    Application.ActivePrinter = "\\winprint\oki9603 on Ne09:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\winprint\oki9603 on Ne09:", Collate:=True
    Sheets("9604").Select
    Application.ActivePrinter = "\\winprint\oki9604 on Ne10:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\winprint\oki9604 on Ne10:", Collate:=True
End Sub

I could copy/paste each section then manually change the sheet & printer name(s).....

If I do that, could I assume each printers Ne## would be consecutive?

Thanks for the help.

zakasnak · Apr 20, 2009

When I ran this short code (2 stores) it worked great; so I thot I'd add the remaining stores & it hung up on 9602. This makes me wonder if the Ne## is a variable number depending on the availability of the network at the time of printing.

Any thoughts?

zakasnak said:
I recorded this part to see if I could figure out a way to set this up.

Question: Is the Ne## a constant number? Is it only constant on my pc, would be a different number on another's pc?

I would like to have the printers auto-selected based on the name of the sheet. As you can see, the printers on the network are named "okiSTORENUMBER which would be the name of each sheet.

Code:

Sub network_print() ' ' network_print Macro ' Macro recorded 4/20/2009 by Carole James ' Sheets("9603").Select Application.ActivePrinter = "\\winprint\oki9603 on Ne09:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "\\winprint\oki9603 on Ne09:", Collate:=True Sheets("9604").Select Application.ActivePrinter = "\\winprint\oki9604 on Ne10:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "\\winprint\oki9604 on Ne10:", Collate:=True End Sub

I could copy/paste each section then manually change the sheet & printer name(s).....

If I do that, could I assume each printers Ne## would be consecutive?

Thanks for the help.

BrianB · Apr 21, 2009

Question: Is the Ne## a constant number? Is it only constant on my pc, would be a different number on another's pc?

The Nexx: number is not constant (you could have discovered this yourself with a simple check). I have even discovered that the same printer can get renumbered on my home pc (I think I updated the driver or something like that). (also see registry exercise below)

You might like to try changing the line of code to match possible numbers. As written it only looks for Ne00: to Ne06:

Code:

'- partial code *****
For P = 0 To 6
            PrinterNetworkName = PrinterName & " on Ne" & Format(P, "00") & ":"
'- etc. ..................

Here is some code to get details of the host's installed printers from the registry which may be of use :-

Code:

'===================================================================================
'- GET PRINTER NAMES & PORT CONNECTIONS OF HOST MACHINE'S INSTALLED PRINTERS
'- FROM THE REGISTRY
'- *** NOTE THAT Application.ActivePrinter PORT CAN DIFFER WITH THE REGISTRY ****
'- ALSO A PRINTER ON LPT1 NEEDS SEPARATE CHECK
'- Brian Baulsom January 2008
'===================================================================================
Sub GET_PRINTERS()
    Dim WshNetwork As Object
    Dim PrintersArray As Variant
    Dim PrinterName As String       ' name only
    Dim PrinterPort As Variant
    Dim PrinterFullName As String   ' name and port
    Dim ComputerName As String
    Dim UserName As String
    '-------------------------------------------------------------------------------
    Set WshNetwork = CreateObject("WScript.Network")
    Set PrintersArray = WshNetwork.EnumPrinterConnections
    '-------------------------------------------------------------------------------
    '- some other properties
    ComputerName = WshNetwork.ComputerName
    UserName = WshNetwork.UserName
    '---------------------------------------------------------------------------------
    '- loop
    For p = 0 To PrintersArray.Count - 1 Step 2
        PrinterName = PrintersArray.Item(p + 1)
        PrinterPort = "Ne" & Format(p / 2, "00") & ":"
        PrinterFullName = PrinterName & " on " & PrinterPort
        rsp = MsgBox("Registry : " & PrinterFullName & vbCr & vbCr _
        & "ActiveP : " & Application.ActivePrinter & vbCr & "User         : " & UserName _
        & vbCr & "Computer : " & ComputerName, vbOKCancel, "PRINTERS IN REGISTRY")
        If rsp = vbCancel Then End
    Next
    '---------------------------------------------------------------------------------
End Sub

BrianB · Apr 21, 2009

Sorry folks, although the registry lookup worked before it does not now.
Here is a better version :-

Code:

'===============================================================================
'- GET PRINTERS AND PORTS FROM THE REGISTRY
'- Brian Baulsom April 2009
'===============================================================================
Sub GET_PRINTERS()
    Dim WSH, RegObject, ValueSet, SubKeyPath
    Dim PrinterName, PrinterPort,KeyValue
    Const HKEY_CURRENT_USER = &H80000001
    '---------------------------------------------------------------------------
    Set WSH = CreateObject("WScript.Shell")
    Set RegObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    SubKeyPath = "Software\Microsoft\Windows NT\CurrentVersion\Devices\"
    RegObject.enumvalues HKEY_CURRENT_USER, SubKeyPath, ValueSet
    '---------------------------------------------------------------------------
    '- GET VALUES FROM THE REGISTRY
    For Each PrinterName In ValueSet
       KeyValue = "HKEY_CURRENT_USER\" & SubKeyPath & "\" & PrinterName
       PrinterPort = Replace(WSH.RegRead(KeyValue), "winspool,", "")
       MsgBox (PrinterName & vbCr & PrinterPort)
    Next
    '---------------------------------------------------------------------------
End Sub

zakasnak · Apr 21, 2009

To use this:

Code:

For P = 0 To 6
            PrinterNetworkName = PrinterName & " on Ne" & Format(P, "00") & ":"

Do I add the first line to the beginning of my code? Do I need to know the exact number of possible NE##? I will have over a hundred (154 stores + all the printers within the corporate office).

Where do I use the 2nd line in this code?

Code:

    Sheets("9602").Select
    Application.ActivePrinter = "\\winprint\oki9602 on Ne09:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\winprint\oki9602 on Ne09:", Collate:=True

Can I have the code look at the worksheet name, add "oki" to the beginning & use it for the printer name? If I can, I believe I can just loop thru each sheet & print, right?

zakasnak · Apr 21, 2009

I'm still working on this:

Rich (BB code):

Dim wb As Workbook
Dim ws As Worksheet
For Each ws In Worksheets
        ws.Activate
For P = 0 To 300
            PrinterNetworkName = “oki” & ws.Name & " on Ne" & Format(P, "000") & ":"

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\winprint\oki9603 on Ne09:", Collate:=True

    Next ws
End Sub

And I'm stuck. I also don't know where to designate that the server name is winprint in line 6.

Help?

BrianB · Apr 22, 2009

Try this CAREFULLY !. I have not been able to test properly because the printout works here at home no matter what Ne number. ie. with Ne00: despite showing as Ne07: normally. If there is a setting "error" the printout goes to the default ActivePrinter - so the code does not generate an error.

Rich (BB code):

'==================================================================================================
'- 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 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()
    PrinterName = "\\winprint\oki"
    Application.Calculation = xlCalculationManual
    '- RESULTS SHEET
    Set ResultsSheet = Worksheets("Results")
    ResultsSheet.Cells.ClearContents
    ToRow = 2
    '-----------------------------------------------------------------------------------------------
    '- LOOP WORKSHEETS
    For Each ws In ActiveWorkbook.Worksheets
        PrinterNumber = ws.name
        If PrinterNumber <> "Results" Then
            '------------------------------------------------------------------------------------------
            '- LOOP POSSIBLE PORT NUMBERS
            For PortNumber = 0 To 5
                PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
                PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
                Application.StatusBar = "  Trying printer : " & PrinterFullName
                '--------------------------------------------------------------------------------------
                'MsgBox (PrinterFullName)  ' for testing
                '--------------------------------------------------------------------------------------
                '- TRY TO PRINT
                On Error Resume Next            ' ERROR TRAP - code continues
                ws.PrintOut ActivePrinter:=PrinterFullName
                On Error GoTo 0                 ' reset to trap any other errors
                '--------------------------------------------------------------------------------------
                '- SUCCESS - record result. exit the loop ... or try again
                If Err.Number = 0 Then
                    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.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'------------ eop ----------------------------------------------------------------------------------

Print each worksheet to printer of same name

zakasnak

Active Member

zakasnak

Active Member

zakasnak

Active Member

zakasnak

Active Member

zakasnak

Active Member

BrianB

Well-known Member

BrianB

Well-known Member

zakasnak

Active Member

zakasnak

Active Member

BrianB

Well-known Member

Similar threads

Share this page

Print each worksheet to printer of same name

Active Member

Active Member

Active Member

Active Member

Active Member

Well-known Member

Well-known Member

Active Member

Active Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock