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!
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!
Code:
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