VBA Print Specific Sheets to Specific Network Printers

[john316swan]

I have written the following Macro to try and print specific worksheets to specific network printers, but they all print to my default printer Thank you for your help!!!!!

Sub PrintInitialOl()
'
' PrintInitialOl Macro
' Prints Initial "OL" (Print 1 letterhead to "Admin B&W Letterhead Printer on SIMEON" & Prints 1 copy on "Admin Copier on SIMEON") Prints "CW Cvr Ltr" (1 letterhead on "Admin B&W Letterhead Printer on SIMEON") Prints CW (2 copies on "Admin Copier on SIMEON")
'


'
    Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
               [B]--'Want this one to print on Admin B&W Letterhead Printer on SIMEON[/B]
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
               [B]--'Want this one to print on Admin Copier on SIMEON[/B]
    Sheets("CW Cvr Ltr").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
               [B]--'Want this one to print on Admin B&W Letterhead Printer on SIMEON[/B]
    Sheets("CW").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
               [B]--'Want these ones to print on Admin Copier on SIMEON[/B]
End Sub

 

[Perpa]

john,

After the report is ready and has been formatted etc, you need to find the names of each of the printers, which you can get from the Print dialog box.

Enter this code before the first print command to save the default printer name:

Dim strCurrentPrinter As String
strCurrentPrinter = Application.ActivePrinter ' stores the current active (default) printer

Then for each printer change

Application.ActivePrinter="Admin B&W Letterhead Printer on SIMEON"   'Change what is in quotes as per appropriate printer name in the Print Dialog Box
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

Repeat the the 'Application.ActivePrinter=' line for each printer change.

Then when printing is done enter this line of code:

Application.ActivePrinter= strCurrentPrinter  'Returns printer output to your default printer

Give this a try.
Perpa

 

[john316swan]

Thanks @Perpa

I took your advice and rewrote my code to make easier, but it is giving me Run-time error '1004' Method 'ActivePrinter' of object'_Application' failed and when I debug it highlights:
Application.ActivePrinter = "Admin B&W Letterhead Printer on SIMEON"

I simplified the process to have the first 2 items print on the USERS default printer, and then selected the letterhead printer to print the other 2 items. Here is the code I rewrote:

Sub PrintInitialOL()
'
' PrintInitialOl Macro
' Prints Initial "OL" (Print 1 letterhead to "Admin B&W Letterhead Printer on SIMEON" & Prints 1 copy on "Admin Copier on SIMEON") Prints "CW Cvr Ltr" (1 letterhead on "Admin B&W Letterhead Printer on SIMEON") Prints CW (2 copies on "Admin Copier on SIMEON")
'




'
Dim strCurrentPrinter As String
strCurrentPrinter = Application.ActivePrinter


  Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("CW").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
[COLOR=#0000cd]    Application.ActivePrinter = "Admin B&W Letterhead Printer on SIMEON"[/COLOR]
    Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("CW Cvr Ltr").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Application.ActivePrinter = strCurrentPrinter
End Sub

Any ideas? Thank you!

 

[john316swan]

I just tried another approach but still get error message.

Sub PrintInitialOL()
'
' PrintInitialOl Macro
' Prints Initial "OL" (Print 1 letterhead to "Admin B&W Letterhead Printer on SIMEON" & Prints 1 copy on "Admin Copier on SIMEON") Prints "CW Cvr Ltr" (1 letterhead on "Admin B&W Letterhead Printer on SIMEON") Prints CW (2 copies on "Admin Copier on SIMEON")
'




'
Dim strCurrentPrinter As String
strCurrentPrinter = Application.ActivePrinter
Dim actPrinter$
For i = 0 To 99
On Error Resume Next
actPrinter = "Admin B&W Letterhead Printer on Ne" & Format$(i, "00") & ":"
'exit if we succeed
If Err.Number = 0 Then Exit For
Next i


If Not Application.ActivePrinter Like "Admin B&W*" Then
MsgBox "Unable to set the printer", vbCritical
Exit Sub
End If




  Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("CW").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=actPrinter, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("CW Cvr Ltr").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=actPrinter, Collate:=True, _
        IgnorePrintAreas:=False
    Application.ActivePrinter = strCurrentPrinter
End Sub

 

[strooman]

Perhaps you can try this routine.

(Not tested because I'm at home.)

Option Explicit

Declare PtrSafe Function GetProfileString Lib "kernel32" _
        Alias "GetProfileStringA" _
        (ByVal lpAppName As String, ByVal lpKeyName As String, _
        ByVal lpDefault As String, ByVal lpReturnedString As String, _
        ByVal nSize As Long) As Long


Sub ChooseYourPrinter()
Dim i%, v

'Get your available printers on the network
v = PrinterList

'Loop through your Printerlist and pick one
For i = LBound(v) To UBound(v)
    If (v(i)) Like "*Admin B&W Letterhead*" Then
        Application.ActivePrinter = v(i)
        'Fill in YOUWORKBOOKNAME beneath
        Workbooks("YOURWORKBOOKNAME").Worksheets("OL").PrintOut Copies:=1, _
        ActivePrinter:=v(i), Collate:=True, IgnorePrintAreas:=False
        'Fill in YOUWORKBOOKNAME beneath
        Workbooks("YOURWORKBOOKNAME").Worksheets("CW Cvr Ltr").PrintOut Copies:=1, _
        ActivePrinter:=v(i), Collate:=True, IgnorePrintAreas:=False
    ElseIf (v(i)) Like "*Admin Copier*" Then
        Application.ActivePrinter = v(i)
        'Fill in YOUWORKBOOKNAME beneath
        Workbooks("YOURWORKBOOKNAME").Worksheets("CW").PrintOut Copies:=1, _
        ActivePrinter:=v(i), Collate:=True, IgnorePrintAreas:=False
    End If
Next
End Sub

Function PrinterList(Optional PrinterNr As Integer = -1)
  Dim i%, n%, lRet&, sBuf$, sOn$, sPort$, aPrn
  Const lSize& = 1024, sKey$ = "devices"
  
  'Get localized Connection string
  aPrn = Split(Excel.ActivePrinter)
  sOn = " " & aPrn(UBound(aPrn) - 1) & " "
  
  'Read Printers
  sBuf = Space(lSize)
  lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lSize)
  If lRet = 0 Then Exit Function
  
  'Make Array from String
  aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)
  
  'Add Port for each Printer
    For n = LBound(aPrn) To UBound(aPrn)
        sBuf = Space(lSize)
        lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lSize)
        sPort = Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
        aPrn(n) = aPrn(n) & sOn & sPort
    Next
  
  'Return the results
  If PrinterNr = -1 Then PrinterList = aPrn Else PrinterList = aPrn(PrinterNr)
End Function

 

[Perpa]

Thanks @Perpa

I took your advice and rewrote my code to make easier, but it is giving me Run-time error '1004' Method 'ActivePrinter' of object'_Application' failed and when I debug it highlights:
Application.ActivePrinter = "Admin B&W Letterhead Printer on SIMEON"


Any ideas? Thank you!

John,
I suspect the printer name you are using is not the same as shown in the Print Dialog Box.
If you have not done so already, open your workbook to one of the worksheets you are trying to print.
Then select 'Print'...that should bring up the Print Dialog Box. There should be a list of available printers shown in that window with a scroll bar.

You need to use the names as shown there. The printer names are probably not the same as the ones you have shown us previously.
Let me know how it goes. Good Luck!
Perpa

 

[john316swan]

So i was able to find the exact name of my printer by going to the VBE screen, pressing CTRL + G and then typing in the following prompt:

?Application.ActivePrinter

This gave me my printer name & port number

I then modified my code to:

Sub PrintInitialOL()
'
' PrintInitialOl Macro
' Prints Initial "OL" (Print 1 letterhead to "Admin B&W Letterhead Printer on SIMEON" & Prints 1 copy on "Admin Copier on SIMEON") Prints "CW Cvr Ltr" (1 letterhead on "Admin B&W Letterhead Printer on SIMEON") Prints CW (2 copies on "Admin Copier on SIMEON")
'
'\\SIMEON\Admin B&W Letterhead Printer on Ne03:




'
Application.ActivePrinter = "\\SIMEON\Admin Copier on Ne05:"
Dim strCurrentPrinter As String
strCurrentPrinter = Application.ActivePrinter


    Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("CW").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
    Application.ActivePrinter = "\\SIMEON\Admin B&W Letterhead Printer on Ne03:"
    Sheets("OL").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("CW Cvr Ltr").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("TUG-E").Select
    Range("A1:B1").Select
    Application.ActivePrinter = strCurrentPrinter
    
  
End Sub

Thank you for your help!!!

 

[Perpa]

So i was able to find the exact name of my printer by going to the VBE screen, pressing CTRL + G and then typing in the following prompt:

?Application.ActivePrinter

This gave me my printer name & port number


Thank you for your help!!!

You are welcome. Glad I was able to help.
Perpa