VBA to change active printer ?

[JumboCactuar]

Hi,
Can it be done with VBA? Set to a specific printer, print then set back to default?

I have a template which prints labels with a macro, though I have to currently go:

File > Print (Change to "Label Printer")
Back out and Run Maceo

File > Print (Change to Default)

Thanks for any help

 

[footoo]

https://www.exceltip.com/printing-i...ult-printer-using-vba-in-microsoft-excel.html

 

[strooman]

Option Explicit
'On 64-bit system use the word ptrSafe in the declaration section like:
'Private Declare PtrSafe Function etc.
'On 32-bit systems remove this word, PtrSafe

Private 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 test()
Dim vaList

'Get all printers
vaList = PrinterFind

'Show me
MsgBox Join(vaList, vbLf), , "List of printers"

'Get all HP Photosmart printers
vaList = PrinterFind(Match:="Photosmart") '<--- Change this to your printer name

'Switch to the first Photosmart found
If UBound(vaList) = -1 Then
    MsgBox "Printer not found"
ElseIf MsgBox( _
    "from " & vbTab & ": " & ActivePrinter & vbLf & "to " & _
    vbTab & ": " & vaList(0), vbOKCancel, _
    "Switch Printers") = vbOK Then
    Application.ActivePrinter = vaList(0)
End If

End Sub

Public Function PrinterFind(Optional Match As String) As Variant
Dim n%, lRet&, sBuf$, sCon$, aPrn
Const lLen& = 1024, sKey$ = "devices"

'******************************************************************
'Written by keepITcool
'Requires xl2000 or newer.
'Result is a zerobased array of installed printers
'Results are filtered on "Match" as string,
'******************************************************************

'Split ActivePrinter string to get localized word for "on"
aPrn = Split(Excel.ActivePrinter)
sCon = " " & aPrn(UBound(aPrn) - 1) & " "

'Read all installed printers (1k bytes s/b enough)
sBuf = Space(lLen)
lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lLen)

If lRet = 0 Then
    Err.Raise vbObjectError + 513, , "Can't read Profile"
    Exit Function
End If

'Split buffer string to a zero based array
aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)

'Optionally Filter the array on Match
If Match <> vbNullString Then aPrn = Filter(aPrn, Match, -1, 1)

'Append localized "on" and 16bit portname for each Printer
For n = LBound(aPrn) To UBound(aPrn)
    sBuf = Space(lLen)
    lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lLen)
    aPrn(n) = aPrn(n) & sCon & _
    Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
Next

'Return the result
PrinterFind = aPrn

End Function

 

[welshgasman]

You could just record a macro?

 

[JumboCactuar]

Thanks all, though this does the trick

default1 = Application.activeprinter

Application.activeprinter = "Label Printer on Ne25:"

'print code

Application.activeprinter = default1

 

[itjohan73]

Thanks all, though this does the trick

default1 = Application.activeprinter

Application.activeprinter = "Label Printer on Ne25:"

'print code

Application.activeprinter = default1

this works. but that NE number keeps changing. And it could be different NE number for each user..
I'm using this atm, it looks through all the NE numbers until it hit the correct printer.
somehow this script doesn't print the first time I press print, but the second time.. so there is something missing somewhere.

oldprinter = Application.ActivePrinter
On Error Resume Next
For i = 0 To 99
Err.Clear
curNePrint = Format(i, "00")
Application.ActivePrinter = "Printer2 på Ne" & curNePrint & ":"
If Err.Number = 0 Then Exit For
Next i