Set ActivePrinter using VBA

[ccordner]

Hi

I'm trying to set the active printer using VBA.

First I tried this:

Sheets(strLocation).PrintOut ActivePrinter:="YRK_Printer on yrkprnt02:", Copies:=1, Collate:=True, _
from:=1, to:=1, IgnorePrintAreas:=False

However, when I ran the code, it kept reverting to the default printer. So, I tried adding this line first:

Application.ActivePrinter:="YRK_Printer on yrkprnt02:"

But, it still kept reverting to the default printer. Any ideas why?

Thanks
Chris

 

[NdNoviceHlp]

Hi Chris, unfortunately it's not that easy to change the default printer. Also note that the code in the the following link only changes the default printer for XL & Word and not your pc's default printer. HTH. Dave

[SOLVED:] Finding/Changing Default Printer

I'm trying to code for the user to set thedefault printer only once per session. Using the dialog code... Prt = Application.Dialogs(xlDialogPrinterSetup).Show Works okay with XL2016 however with XL97 the sheet with print previewscreen is displayed after the dialog. This XL application is...

www.vbaexpress.com

 

[ccordner]

Good afternoon

I don't want to change the default printer - I just want that particular sheet to print out with a specific printer.

Thanks
Chris

 

[NdNoviceHlp]

Chris, I could be wrong, but I think changing the default printer to the "specific" printer that U want to use is one and the same. Dave

 

[ccordner]

Interesting. I was looking here and I thought that you could set the ActivePrinter for that operation.

Apologies if I've misunderstood.

 

[NdNoviceHlp]

Sure seems like U can. Here's another link... Changing the active printer in a macro
Dave

 

[ccordner]

Sure seems like U can. Here's another link... Changing the active printer in a macro
Dave

Thanks - I'll have a play with that.

 

[John_w]

I don't want to change the default printer - I just want that particular sheet to print out with a specific printer.

Try this macro:

Public Sub Print_On_Specific_Printer()

    Dim CurrentPrinterNameNet As String
    Dim WindowsPrinterName As String
    Dim PrinterNameNet As String
    Dim strLocation As String
    
    strLocation = "Sheet1"  'CHANGE TO YOUR SHEET NAME
    WindowsPrinterName = "YRK_Printer"
    
    CurrentPrinterNameNet = Application.ActivePrinter
    
    'Get full name of specified Windows printer, including its network port
    
    PrinterNameNet = FindPrinter(WindowsPrinterName)
    
    'Print sheet on specific printer.  This makes it the active printer
    
    Sheets(strLocation).PrintOut ActivePrinter:=PrinterNameNet, Copies:=1, Collate:=True, _
       From:=1, To:=1, IgnorePrintAreas:=False
    
    'Restore current printer
    
    Application.ActivePrinter = CurrentPrinterNameNet

End Sub


'Written: November 28, 2009
'Author:  Leith Ross
'Summary: Finds a printer by name and returns the printer name and port number.

Public Function FindPrinter(ByVal PrinterName As String) As String

    'This works with Windows 2000 and up
    
    Dim Arr As Variant
    Dim Device As Variant
    Dim Devices As Variant
    Dim printer As String
    Dim RegObj As Object
    Dim RegValue As String
    Const HKEY_CURRENT_USER = &H80000001
    
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumValues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
    For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        printer = Device & " on " & Split(RegValue, ",")(1)
        'If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then  'original code
        If StrComp(Device, PrinterName, vbTextCompare) = 0 Then
            FindPrinter = printer
            Exit Function
        End If
    Next
      
End Function

 

[Jagat Pavasia]

Did it work ,?