VBA to change active printer ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
Code:
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
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
727
Office Version
  1. 365
Platform
  1. Windows
Thanks all, though this does the trick

Code:
default1 = Application.activeprinter

Application.activeprinter = "Label Printer on Ne25:"

'print code

Application.activeprinter = default1
 

itjohan73

New Member
Joined
Aug 6, 2019
Messages
2
Thanks all, though this does the trick

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

Watch MrExcel Video

Forum statistics

Threads
1,130,336
Messages
5,641,565
Members
417,222
Latest member
Broflovski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top