Macro to print to network printer

[gnrboyd]

I worked on a project some time ago and never really got it to work properly so I'd like to take another stab at it.

Basically, I want a macro to capture the current printer selected and then print to a specified network printer. The problem is that Excel sees the printers with different network numbers (i.e. Ne01, Ne02, etc.) I need the macro to find the printer I'm looking for and then loop through about 16 options (or more) to find the correct printer with that network extension.

Several months ago, I copied some code from multiple places and modified as needed. (I am a record and tweak user and don't really know how to write VB code from scratch.) I have another part of the macro that is used to select which sheets to print so I will just post the part of the code in question that I am having a problem with.

Maybe it is a syntax problem? I've tried to walk through the code and it seems to jump around more than I'd like or maybe it needs to. Again, I don't know how to write the code from scratch but I want it to do the following:

1. Capture current printer setting and record for later use.
2. Change printer to selected network printer. It will need to loop through until it finds a valid Nexx matching that printer.
3. Print page
4. Change printer setting back to original users printer setting.
5. Done

Here is the code I had come up with several months ago and never got it to work. If you could review and let me know if you see any problems, I would appreciate it. The exact error I get is "Sorry could not print recap sheet to \\mfprime\COLOR_3000 on ".

I would appreciate any help you can provide.

Thank you!

Sub PrintRecap()
'
'Option Explicit

Dim strCurrentPrinter As String

'// Change printer names here
Const COLORBC As String = "\\mfprime\COLOR_3000 on "
Const RPTACCT As String = "\\mfprime\RPT_ACCT on "

'// Store Default Printer Info
strCurrentPrinter = Application.ActivePrinter
  
'// 1st Print Job
Sheets("recap").Select
On Error Resume Next
Application.ActivePrinter = NetWorkPrinter(COLORBC)
If Err Then
    MsgBox "Sorry could not print " & ActiveSheet.Name & " sheet to " & COLORBC, vbCritical, "Error printing"
    Err.Clear
Else
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

'// Reset to users default printer
Application.ActivePrinter = strCurrentPrinter

End Sub

Function NetWorkPrinter(ByVal strPrinter As String) As String
'// Select Network Printer
    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:")
    
    '// Setup printer to Print
    X = 0

    On Error Resume Next
TryAgain:
    'Printer
    Application.ActivePrinter = strPrinter & NetWork(X)
    If Err.Number <> 0 And X < 16 Then
        X = X + 1
        GoTo TryAgain
    ElseIf Err.Number <> 0 And X > 15 Then
        GoTo PrtError
    End If
    On Error GoTo 0
    NetWorkPrinter = strPrinter & NetWork(X)
errorExit:
Exit Function

PrtError:
    'no printer found
    NetWorkPrinter = ""
    Resume errorExit


End Function

 

[gnrboyd]

anyone?