Finding/Changing Default Printer

[Dave]

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 opened by VB6 andis not visible....only XL userforms are shown. So having the a worksheetshown following the dialog is bad... not having it shown (as with XL2016) isgood(okay). Anyways, I’ve tried the following code to cancel the print previewbut it errors (XL2016)with 1004 unable to get the show property of the dialog box….


Prt =Application.Dialogs(xlDialogPrinterSetup).Show(Arg6:=False)
Any suggestions/help will be appreciated as I can’tseem to Google a solution. Dave

ps. Here’s some relevant link….
https://www.excelbanter.com/excel-programming/439559-xldialogprint-arguments.html
https://www.pcreview.co.uk/threads/xldialogprint-printwhat-selection-option.2843962/

[SamT]

I don't know the version string for your systems, so run this on both machines to discover the right Strings
Option Explicit

Sub t()
MsgBox (Application.Version)
End Sub

Then, in your code use

#If Application.Version = "97" Then
'Place 97 style code to open printer setup here
#ElseIf Application.Version = "2016" Then
'Place 2016 style code to open printer setup here
#End If

Nb: the # symbol indicates a Compiler Directive statement.

From Help Files:
Conditionally compiles selected blocks of Visual Basic code.
Syntax
#If expression Then
statements
[#ElseIf expression-n Then
[elseifstatements]]
[#Else
[elsestatements]]
#End If



Works okay with XL2016 however with XL97 the sheet with print previewscreen is displayed after the dialog. This XL application is opened by VB6 andis not visible....only XL userforms are shown. So having the a worksheetshown following the dialog is bad... not having it shown (as with XL2016) isgood(okay). Anyways, I’ve tried the following code to cancel the print previewbut it errors (XL2016)with 1004 unable to get the show property of the dialog box….I Don't understand, please say again, differently.

[Dave]

Thanks SamT for your interest. I could specifically addressthe code to be Version specific. However, I don’t know what versions create theerror… I just know that the code that works for XL 2016 doesn’t produce thesame result for XL97. In XL97, the dialog code alone without any argumentscauses the sheet to be visible (and apparently in print preview) even thoughthe application itself is not visible. I will try to restate my circumstance. Ihave a VB6 program that creates an XL Application that is set to Visible = False.This XL application opens a wb which opens a userform. On this userform thereis a print button for whatever range that has been pre-entered. So, I wouldlike for the user to be able to set the default printer before printing (onlyonce per session which I have already addressed). Everything worked great in myXL2016 Version opening an XL97 (.xls) file but not so much, as described, whenI trialled it using the same file and code for XL97. I believe the error hassomething to do with print preview which I also believe could be addressed if Icould figure out the arguments code for the dialog. Again, thanks for your assistance.Dave

[SamT]

PrintOut Method See Also Applies To Example (http://www.vbaexpress.com/forum/#example) Specifics
Prints the object.
expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

expression Required. An expression that returns an object in the Applies To list.
From Optional Variant. The number of the page at which to start printing. If this argument is omitted, printing starts at the beginning.
To Optional Variant. The number of the last page to print. If this argument is omitted, printing ends with the last page.
Copies Optional Variant. The number of copies to print. If this argument is omitted, one copy is printed.
Preview Optional Variant. True to have Microsoft Excel invoke print preview before printing the object. False (or omitted) to print the object immediately.
ActivePrinter Optional Variant. Sets the name of the active printer.
PrintToFile Optional Variant. True to print to a file. If PrToFileName is not specified, Microsoft Excel prompts the user to enter the name of the output file.
Collate Optional Variant. True to collate multiple copies.
PrToFileName Optional Variant. If PrintToFile is set to True, this argument specifies the name of the file you want to print to.
Remarks "Pages" in the descriptions of From and To refers to printed pages — not overall pages in the sheet or workbook.
Example This example prints the active sheet.
ActiveSheet.PrintOut

[Dave]

Thanks SamT but this doesn't seem helpful in addressing my printer set up dialog woes. Dave

[Dave]

Well, I've given up on the use of the xlBuiltinDialog PrinterSetup as it doesn't work across XL versions and/or Windows OS's? It also doesn't accept arguments even though a dialog should? It seems that my only resolution is to build my own special purpose userform for setting the default printer. I'll post the code in case it's useful for others. Most of it comes from Juan Pablo Gonzalez at this thread with some adaptation.. https://www.mrexcel.com/forum/excel-questions/20392-setting-specific-printer-excel.html?highlight=xlDialogPrinterSetup+error
Module code...

Option Explicit
'https://www.mrexcel.com/forum/excel-questions/20392-setting-specific-printer-excel.html?highlight=xlDialogPrinterSetup+error
'Thanks to Juan Pablo Gonzalez
Public Arr As Variant
Const PRINTER_ENUM_CONNECTIONS = &H4
Const PRINTER_ENUM_LOCAL = &H2
Type PRINTER_INFO_1
Flags As Long
pDescription As String
pName As String
pComment As String
End Type
Private Declare Function EnumPrinters Lib "winspool.drv" Alias "EnumPrintersA" _
(ByVal Flags As Long, ByVal Name As String, ByVal Level As Long, _
pPrinterEnum As Long, ByVal cdBuf As Long, pcbNeeded As Long, _
pcReturned As Long) As Long
Private Declare Function PtrToStr Lib "Kernel32" Alias "lstrcpyA" _
(ByVal RetVal As String, ByVal Ptr As Long) As Long
Private Declare Function StrLen Lib "Kernel32" Alias "lstrlenA" _
(ByVal Ptr As Long) As Long
Declare Function GetProfileString& Lib "Kernel32" Alias "GetProfileStringA" _
(ByVal lpApplicationName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Integer)



Private Function ListPrinters() As Variant
Dim bSuccess As Boolean
Dim iBufferRequired As Long
Dim iBufferSize As Long
Dim iBuffer() As Long
Dim iEntries As Long
Dim iIndex As Long
Dim strPrinterName As String
Dim iDummy As Long
Dim iDriverBuffer() As Long
Dim strPrinters() As String

iBufferSize = 3072
ReDim iBuffer((iBufferSize * 4) - 1) As Long
bSuccess = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or PRINTER_ENUM_LOCAL, vbNullString, _
1, iBuffer(0), iBufferSize, iBufferRequired, iEntries)

If bSuccess Then
If iBufferRequired > iBufferSize Then
iBufferSize = iBufferRequired
MsgBox "iBuffer too small. Trying again with " & iBufferSize & " bytes."
ReDim iBuffer(iBufferSize * 4) As Long
bSuccess = EnumPrinters(PRINTER_ENUM_CONNECTIONS Or PRINTER_ENUM_LOCAL, vbNullString, _
1, iBuffer(0), iBufferSize, iBufferRequired, iEntries)

If Not bSuccess Then
MsgBox "Error enumerating printers."
Exit Function
End If
End If

ReDim strPrinters(iEntries - 1)

For iIndex = 0 To iEntries - 1
strPrinterName = Space$(StrLen(iBuffer(iIndex * 4 + 2)))
iDummy = PtrToStr(strPrinterName, iBuffer(iIndex * 4 + 2))
strPrinters(iIndex) = strPrinterName
Next iIndex
End If

ListPrinters = strPrinters
End Function


Public Sub SetActivePrinter(strPrinterName As String)
Dim strBuffer As String
Dim lngRetValue As Long
Dim strDriverName As String
Dim strPrinterPort As String

strBuffer = Space(1024)
lngRetValue = GetProfileString("PrinterPorts", strPrinterName, "", _
strBuffer, Len(strBuffer))

' Parse the driver name and port name out of the buffer
GetDriverAndPort strBuffer, strDriverName, strPrinterPort

If strDriverName <> "" And strPrinterPort <> "" Then
' Changed the " on " to " en " to suit Spanish needs...
Application.ActivePrinter = strPrinterName & " on " & strPrinterPort
' Application.ActivePrinter = strPrinterName & " en " & strPrinterPort
End If
End Sub


Private Sub GetDriverAndPort(ByVal buffer As String, DriverName As String, PrinterPort As String)
Dim iDriver As Integer
Dim iPort As Integer

DriverName = ""
PrinterPort = ""

' The driver name is first in the string terminated by a comma
iDriver = InStr(buffer, ",")

If iDriver > 0 Then
' Strip out the driver name
DriverName = Left(buffer, iDriver - 1)
' The port name is the second entry after the driver name
' separated by commas.
iPort = InStr(iDriver + 1, buffer, ",")

If iPort > 0 Then
' Strip out the port name
PrinterPort = Mid(buffer, iDriver + 1, _
iPort - iDriver - 1)
End If
End If
End Sub



Sub ChangePrinter()
Dim Tmpstr As String, LastOn As Long, PrintName As String

LastOn = InStrRev(Application.ActivePrinter, "on") - 1
Tmpstr = Right(Application.ActivePrinter, Len(Application.ActivePrinter) - LastOn)
PrintName = Left(Application.ActivePrinter, Len(Application.ActivePrinter) - Len(Tmpstr) - 1)

If MsgBox(prompt:="Current default printer is: " & PrintName & vbCrLf _
& "Do you want to change the Default Printer?", Buttons:=vbYesNo, Title:="Change Default Printer") = vbYes Then
Arr = ListPrinters
UserForm1.Show
End If
End Sub
Userform1 that has a Listbox1 code...

Option Explicit
Private Sub ListBox1_Click()
Dim i As Integer

For i = LBound(Arr) To UBound(Arr)
If InStr(1, Arr(i), UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex), 1) > 0 Then
SetActivePrinter CStr(Arr(i))
Exit For
End If
Next i

UserForm1.Caption = "Default Printer: " & CStr(Arr(i))
End Sub



Private Sub UserForm_Initialize()
Dim i As Integer, Tmpstr As String, LastOn As Long, PrintName As String

For i = LBound(Arr) To UBound(Arr)
UserForm1.ListBox1.AddItem Arr(i)
Next i

LastOn = InStrRev(Application.ActivePrinter, "on") - 1
Tmpstr = Right(Application.ActivePrinter, Len(Application.ActivePrinter) - LastOn)
PrintName = Left(Application.ActivePrinter, Len(Application.ActivePrinter) - Len(Tmpstr) - 1)
UserForm1.Caption = "Default Printer: " & PrintName
End Sub
To operate...

Call ChangePrinter
Solved. Dave

[SamT]

Dave,
Thank you for posting this solution. Why don't you submit it as a Potential KB Entry? I think it would be a good'un.

[Dave]

Thanks for your comments SamT but as stated most of the code is from the genius of Juan Pablo Gonzales. There was an unexpected glitch to the posted code and should be corrected by changing the code as follows. Dave

Private Sub ListBox1_Click()
Dim i As Integer
For i = LBound(Arr) To UBound(Arr)
'If InStr(1, Arr(i), UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex), 1) > 0 Then
If Arr(i) = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex) Then
SetActivePrinter CStr(Arr(i))
Exit For
End If
Next i
UserForm1.Caption = "Default Printer: " & CStr(Arr(i))
End Sub