Network Printing - Another One!

[davidjg]

Folks

This is my attempt at borrowing code from other posts and trying to get it to print to different network printers and looping through to find the correct NeXX number also.

Thanks to all those I found code from.

However my problem is its ignoring it!

As far as I can tell it should be printing one copy to printer "Office" and one copy to printer "Findon" but all it does is print two copies to whatever printer I have selected in Excel at the time. Im sure its a simple fix but its doing my head in!

Thanks

Dave

Const Office As String = "[URL="file://virgo/cewp0009"]\\virgo\cewp0009[/URL] on "
Const Findon As String = "[URL="file://ps1921na001/ps1921nm010"]\\ps1921na001\ps1921nm010[/URL] on "
Sub lllllllll()
Dim NetWork As Variant
Dim x As Integer
' Define NetWork Array
NetWork = Array("Ne00:", "Ne01:", "Ne02:", "Ne03:", "Ne04:", _
    "Ne05:", "Ne06:", "Ne07:", "Ne08:", "Ne09:", "Ne10:", "Ne11:", "Ne12:", _
    "Ne13:", "Ne14:", "Ne15:", "Ne16:", "Ne17:", "Ne18:", "Ne19:", "Ne20:", _
    "Ne21:", "Ne22:", "Ne23:", "Ne24:", "Ne25:", "Ne26:", "Ne27:", "Ne28:", _
    "Ne29:", "Ne30:", "Ne31:", "Ne32:", "Ne33:", "Ne3:", "Ne35:", "Ne36:", _
    "Ne37:", "Ne38:", "Ne39:", "Ne40:", "Ne41:", "Ne42:", "Ne43:", "Ne44:", _
    "Ne45:", "Ne46:", "Ne47:", "Ne48:", "Ne49:", "Ne50:", "Ne51:", "Ne52:", _
    "Ne53:", "Ne54:", "Ne55:", "Ne56:", "Ne57:", "Ne58:", "Ne59:", "Ne60:", _
    "Ne61:", "Ne62:", "Ne63:", "Ne64:", "Ne65:", "Ne66:", "Ne67:", "Ne68:", _
    "Ne69:", "Ne70:", "Ne71:", "Ne72:", "Ne73:", "Ne74:", "Ne75:", "Ne76:", _
    "Ne77:", "Ne78:", "Ne79:", "Ne80:", "Ne81:", "Ne82:", "Ne83:", "Ne84:", _
    "Ne85:", "Ne86:", "Ne87:", "Ne88:", "Ne89:", "Ne90:", "Ne91:", "Ne92:", _
    "Ne93:", "Ne94:", "Ne95:", "Ne96:", "Ne97:", "Ne98:", "Ne:99", "Ne100:", _
    "Ne101:", "Ne102:", "Ne103:", "Ne104:", "Ne105:", "Ne106:", "Ne:107", "Ne108:", _
    "Ne109:", "Ne110:", "Ne111:", "Ne112:", "Ne113:", "Ne114:", "Ne:115", "Ne116:", _
    "Ne117:", "Ne118:", "Ne119:", "Ne120:", "Ne121:", "Ne122:", "Ne:123", "Ne124:", _
    "Ne125:", "Ne126:", "Ne127:", "Ne128:", "Ne129:", "Ne130:", "Ne:131", "Ne132:")
    
'Setup printer
x = 0
TryAgain:
On Error Resume Next
    Application.ActivePrinter = Office & NetWork(x)
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        Office & NetWork(x), Collate:=True
        GoTo NextPrinter
   If Err.Number <> 0 And x < 132 Then
        x = x + 1
        GoTo TryAgain
    ElseIf Err.Number <> 0 And x > 131 Then GoTo PrtError
    End If
NextPrinter:
  Application.ActivePrinter = Findon & NetWork(x)
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        Findon & NetWork(x), Collate:=True
        Exit Sub
PrtError:
MsgBox PROMPT:="PRINT QUEUE WAS NOT FOUND"
End Sub

 

[BrianB]

Need to reset error number to zero each time with Err.Clear.

'===============================================================================
'- SET ACTIVE PRINTER WITH UNKNOWN Ne NUMBER
'===============================================================================
Dim PrinterNetworkName As String
Dim Msg As String
Dim PrinterName As String
Dim P As Integer
'---------------------------------------------------------------------------
'===============================================================================
'- MAIN ROUTINE
'===============================================================================
Sub GET_PRINTER()
    '---------------------------------------------------------------------------
    '- Office printer
    PrinterName = "\\virgo\cewp0009"
    PRINT_SHEET
    '---------------------------------------------------------------------------
    '- Findon Printer
    PrinterName = "\\ps1921na001\ps1921nm010"
    PRINT_SHEET
    '---------------------------------------------------------------------------
    MsgBox ("Done")
End Sub
'-------- END OF MAIN ----------------------------------------------------------
'===============================================================================
'- SUBROUTINE TO GET THE PRINTER & PRINT SHEET
'===============================================================================
Private Sub PRINT_SHEET()
    Msg = "PRINTER NOT FOUND" & vbCr & PrinterName
    On Error Resume Next
    '---------------------------------------------------------------------------
    '- LOOP POSSIBLE Ne NUMBERS
    For P = 0 To 6
        Err.Clear       ' NB ***********
        PrinterNetworkName = PrinterName & " on Ne" & Format(P, "00") & ":"
        Application.ActivePrinter = PrinterNetworkName
        '=======================================================================
        '- FOUND PRINTER - CODE TO PRINT
        '=======================================================================
        If Err.Number = 0 Then
            Msg = "FOUND PRINTER " & vbCr & PrinterNetworkName
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
                ActivePrinter:=PrinterNetworkName, Collate:=True
            Exit For
        End If
        '=======================================================================
    Next
    '---------------------------------------------------------------------------
    MsgBox (Msg)
End Sub
'----------------------- END OF ROUTINE ----------------------------------------

 

[davidjg]

Brian your a genius!

Thanks

Had to change the line
For P = 0 To 6
to
For P = 0 To 140
to cope with all my network printers,

Is there a way of getting it to store the errors and show them all at the end. ie if I want to print to 100 printers I can hit print go away and have a coffee then come back and see the errors rather than have to click ok and set it going again?

Again, my many thanks, this alone has saved a world of pain

DG

 

[BrianB]

This is the amended version to show successes in a worksheet called "Results". If you really want to see failures then change accordingly.

Not being "blessed" with as many printers as yourself I have not been able to test it fully.

Also note that it only checks for a successful Activeprinter set - not if the actual printout occurs eg. if the printer is switched off. This is because the printout goes to a spooler first (no error is generated)

'===============================================================================
'- SET ACTIVE PRINTER WITH UNKNOWN Ne NUMBER
'- requires worksheet called'Results' to record successes
'===============================================================================
Dim PrinterNetworkName As String
Dim Msg As String
Dim PrinterName As String
Dim P As Integer
'- results sheet
Dim ResultsSheet As Worksheet
Dim ToRow As Long
Dim ToColumn As Integer
'---------------------------------------------------------------------------
'===============================================================================
'- MAIN ROUTINE
'===============================================================================
Sub GET_PRINTER2()
    '- results sheet
    Set ResultsSheet = Worksheets("Results")
    ResultsSheet.Cells.ClearContents
    ResultsSheet.Range("A1:B1").Value = Array("Office", "Findon")
    '---------------------------------------------------------------------------
    '- Office printer
    PrinterName = "\\virgo\cewp0009"
    ToRow = 2
    ToColumn = 1
    PRINT_SHEET2
    '---------------------------------------------------------------------------
    '- Findon Printer
    PrinterName = "\\ps1921na001\ps1921nm010"
    ToRow = 2
    ToColumn = 2
    PRINT_SHEET2
    '---------------------------------------------------------------------------
    MsgBox ("Done")
End Sub
'-------- END OF MAIN ----------------------------------------------------------
'===============================================================================
'- SUBROUTINE TO GET THE PRINTER & PRINT SHEET
'===============================================================================
Private Sub PRINT_SHEET2()
    Msg = "PRINTER NOT FOUND" & vbCr & PrinterName
    On Error Resume Next
    '---------------------------------------------------------------------------
    '- LOOP POSSIBLE Ne NUMBERS
    For P = 0 To 6
        Err.Clear       ' NB ***********
        PrinterNetworkName = PrinterName & " on Ne" & Format(P, "00") & ":"
        Application.ActivePrinter = PrinterNetworkName
        '=======================================================================
        '- FOUND PRINTER - CODE TO PRINT
        '=======================================================================
        If Err.Number = 0 Then
            ResultsSheet.Cells(ToRow, ToColumn).Value = PrinterNetworkName
            ToRow = ToRow + 1
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
                ActivePrinter:=PrinterNetworkName, Collate:=True
            Exit For
        End If
        '=======================================================================
    Next
    '---------------------------------------------------------------------------
End Sub
'----------------------- END OF ROUTINE ----------------------------------------

 

[zakasnak]

Brian B,

would you please take a look at my thread here: http://www.mrexcel.com/forum/showthread.php?p=1911138#post1911138

I think I can make your code on this thread work for me, but would like input from you about what I currently have.

I'm going to play with your code & see if I can get it to work! Like davidjg, I have 150-some odd printers on my network, so this would save me LOTS of time.

I appreciate any input you can share!

Carole