Listing Printers in Excel

[RobC]

Need a little help trying to list Printers in Excel. I have a sheet I
would like to print directly to Acrobat PDF Writer and on my PC it is
"Acrobat PDFWriter on LPT1" but this workbook is used by many and others
Acrobat appears to be installed differently. Currently I try to set the
active printer to "Acrobat PDFWriter on LPT1" and then test to see if it
set, if it fails, I bring up the Print dialog.


On Error Resume Next
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:" 'Set Printer

If ActivePrinter = "Acrobat PDFWriter on LPT1:" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:
="Acrobat PDFWriter on LPT1:"

Else 'PDF Writer does not appear to be installed
ActiveWindow.SelectedSheets.Application.Dialogs(xlDialogPrint).Show
End If

I was hoping to get access to the list of printers then do some
manipulation to find PDF Writer, then choose this printer.

I see MS Access has Printer Collection but nothing in Excel.

Any help would be greatly appreciated...

Thanks,
Rob

 

[Steve Yandl]

Rob,

The Word MVPs have a discussion on using the Windows API here:
http://word.mvps.org/FAQs/MacrosVBA/AvailablePrinters.htm

If you knew the users were all using WindowsXP you could use something like
this:

Sub PrinterList()
Dim R As Integer

R = 2
strComputer = "."

Cells(1, 1).Value = "Printer Name"
Cells(1, 2).Value = "Default"

Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")
Set colPrinters = objWMIService.ExecQuery _
("Select * From Win32_Printer")

For Each objPrinter In colPrinters
Cells(R, 1).Value = objPrinter.Name
Cells(R, 2).Value = objPrinter.Default
R = R + 1
Next

End Sub


Steve Yandl

 

[JMB]

Brought to you by Norman Jones

http://tinyurl.com/9cm7l

 

[RobC]

Steve,

This is what I was looking for but hard to find info on.

Are there anyother properties other than Name and Default? i.e., Port?

The link on the next response was also good but very complex.

Thanks... Rob

 

[Steve Yandl]

Copied below are lines from a generic script that should give you good
indicators on available properties providing info on printers. All
properties should be available on WinXP or Server2003, most properties
available on Win2k, WinME or systems where WMI has been downloaded.

For Each objItem In colItems
WScript.Echo "Attributes: " & objItem.Attributes
WScript.Echo "Availability: " & objItem.Availability
strAvailableJobSheets = Join(objItem.AvailableJobSheets, ",")
WScript.Echo "AvailableJobSheets: " & strAvailableJobSheets
WScript.Echo "AveragePagesPerMinute: " & objItem.AveragePagesPerMinute
strCapabilities = Join(objItem.Capabilities, ",")
WScript.Echo "Capabilities: " & strCapabilities
strCapabilityDescriptions = Join(objItem.CapabilityDescriptions, ",")
WScript.Echo "CapabilityDescriptions: " & strCapabilityDescriptions
WScript.Echo "Caption: " & objItem.Caption
strCharSetsSupported = Join(objItem.CharSetsSupported, ",")
WScript.Echo "CharSetsSupported: " & strCharSetsSupported
WScript.Echo "Comment: " & objItem.Comment
WScript.Echo "ConfigManagerErrorCode: " &
objItem.ConfigManagerErrorCode
WScript.Echo "ConfigManagerUserConfig: " &
objItem.ConfigManagerUserConfig
WScript.Echo "CreationClassName: " & objItem.CreationClassName
strCurrentCapabilities = Join(objItem.CurrentCapabilities, ",")
WScript.Echo "CurrentCapabilities: " & strCurrentCapabilities
WScript.Echo "CurrentCharSet: " & objItem.CurrentCharSet
WScript.Echo "CurrentLanguage: " & objItem.CurrentLanguage
WScript.Echo "CurrentMimeType: " & objItem.CurrentMimeType
WScript.Echo "CurrentNaturalLanguage: " &
objItem.CurrentNaturalLanguage
WScript.Echo "CurrentPaperType: " & objItem.CurrentPaperType
WScript.Echo "Default: " & objItem.Default
strDefaultCapabilities = Join(objItem.DefaultCapabilities, ",")
WScript.Echo "DefaultCapabilities: " & strDefaultCapabilities
WScript.Echo "DefaultCopies: " & objItem.DefaultCopies
WScript.Echo "DefaultLanguage: " & objItem.DefaultLanguage
WScript.Echo "DefaultMimeType: " & objItem.DefaultMimeType
WScript.Echo "DefaultNumberUp: " & objItem.DefaultNumberUp
WScript.Echo "DefaultPaperType: " & objItem.DefaultPaperType
WScript.Echo "DefaultPriority: " & objItem.DefaultPriority
WScript.Echo "Description: " & objItem.Description
WScript.Echo "DetectedErrorState: " & objItem.DetectedErrorState
WScript.Echo "DeviceID: " & objItem.DeviceID
WScript.Echo "Direct: " & objItem.Direct
WScript.Echo "DoCompleteFirst: " & objItem.DoCompleteFirst
WScript.Echo "DriverName: " & objItem.DriverName
WScript.Echo "EnableBIDI: " & objItem.EnableBIDI
WScript.Echo "EnableDevQueryPrint: " & objItem.EnableDevQueryPrint
WScript.Echo "ErrorCleared: " & objItem.ErrorCleared
WScript.Echo "ErrorDescription: " & objItem.ErrorDescription
strErrorInformation = Join(objItem.ErrorInformation, ",")
WScript.Echo "ErrorInformation: " & strErrorInformation
WScript.Echo "ExtendedDetectedErrorState: " &
objItem.ExtendedDetectedErrorState
WScript.Echo "ExtendedPrinterStatus: " & objItem.ExtendedPrinterStatus
WScript.Echo "Hidden: " & objItem.Hidden
WScript.Echo "HorizontalResolution: " & objItem.HorizontalResolution
WScript.Echo "InstallDate: " &
WMIDateStringToDate(objItem.InstallDate)
WScript.Echo "JobCountSinceLastReset: " &
objItem.JobCountSinceLastReset
WScript.Echo "KeepPrintedJobs: " & objItem.KeepPrintedJobs
strLanguagesSupported = Join(objItem.LanguagesSupported, ",")
WScript.Echo "LanguagesSupported: " & strLanguagesSupported
WScript.Echo "LastErrorCode: " & objItem.LastErrorCode
WScript.Echo "Local: " & objItem.Local
WScript.Echo "Location: " & objItem.Location
WScript.Echo "MarkingTechnology: " & objItem.MarkingTechnology
WScript.Echo "MaxCopies: " & objItem.MaxCopies
WScript.Echo "MaxNumberUp: " & objItem.MaxNumberUp
WScript.Echo "MaxSizeSupported: " & objItem.MaxSizeSupported
strMimeTypesSupported = Join(objItem.MimeTypesSupported, ",")
WScript.Echo "MimeTypesSupported: " & strMimeTypesSupported
WScript.Echo "Name: " & objItem.Name
strNaturalLanguagesSupported = Join(objItem.NaturalLanguagesSupported,
",")
WScript.Echo "NaturalLanguagesSupported: " &
strNaturalLanguagesSupported
WScript.Echo "Network: " & objItem.Network
strPaperSizesSupported = Join(objItem.PaperSizesSupported, ",")
WScript.Echo "PaperSizesSupported: " & strPaperSizesSupported
strPaperTypesAvailable = Join(objItem.PaperTypesAvailable, ",")
WScript.Echo "PaperTypesAvailable: " & strPaperTypesAvailable
WScript.Echo "Parameters: " & objItem.Parameters
WScript.Echo "PNPDeviceID: " & objItem.PNPDeviceID
WScript.Echo "PortName: " & objItem.PortName
strPowerManagementCapabilities =
Join(objItem.PowerManagementCapabilities, ",")
WScript.Echo "PowerManagementCapabilities: " &
strPowerManagementCapabilities
WScript.Echo "PowerManagementSupported: " &
objItem.PowerManagementSupported
strPrinterPaperNames = Join(objItem.PrinterPaperNames, ",")
WScript.Echo "PrinterPaperNames: " & strPrinterPaperNames
WScript.Echo "PrinterState: " & objItem.PrinterState
WScript.Echo "PrinterStatus: " & objItem.PrinterStatus
WScript.Echo "PrintJobDataType: " & objItem.PrintJobDataType
WScript.Echo "PrintProcessor: " & objItem.PrintProcessor
WScript.Echo "Priority: " & objItem.Priority
WScript.Echo "Published: " & objItem.Published
WScript.Echo "Queued: " & objItem.Queued
WScript.Echo "RawOnly: " & objItem.RawOnly
WScript.Echo "SeparatorFile: " & objItem.SeparatorFile
WScript.Echo "ServerName: " & objItem.ServerName
WScript.Echo "Shared: " & objItem.Shared
WScript.Echo "ShareName: " & objItem.ShareName
WScript.Echo "SpoolEnabled: " & objItem.SpoolEnabled
WScript.Echo "StartTime: " & WMIDateStringToDate(objItem.StartTime)
WScript.Echo "Status: " & objItem.Status
WScript.Echo "StatusInfo: " & objItem.StatusInfo
WScript.Echo "SystemCreationClassName: " &
objItem.SystemCreationClassName
WScript.Echo "SystemName: " & objItem.SystemName
WScript.Echo "TimeOfLastReset: " &
WMIDateStringToDate(objItem.TimeOfLastReset)
WScript.Echo "UntilTime: " & WMIDateStringToDate(objItem.UntilTime)
WScript.Echo "VerticalResolution: " & objItem.VerticalResolution
WScript.Echo "WorkOffline: " & objItem.WorkOffline
WScript.Echo
Next
Next

Steve

 

[Lee Meadowcroft]

You dont need any of that complicated rubbish, try:

strOldActivePrinter = Application.ActivePrinter
Application.Dialogs(9).Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.ActivePrinter = strOldActivePrinter

It saves the current printer name, prompts you to select the installed
PDF Printer, Prints and then restores the old printer.

Lee

 

[Steve Yandl]

I agree that WMI is a bit complicated, as is using the Windows API, but
"rubbish" is an opinion that depends on what information you're actually
after.

The original post suggests the goal is to switch to the appropriate printer
and back to default without forcing the user to intercede. A difference
between your code and what Rob is currently using is that your code presents
the user with the printers dialog 100% of the time, the original code only
did so if some other printer was the default.


Steve

 

[JMB]

According to the OP

He already knows how to bring up the print dialog boxes as evidenced in his
original posts.

 

[Tom Ogilvy]

OP was using

xlDialogPrint

Lee is suggesting

xlDialogPrinterSetup

Although I agree with Steve on Intent.

Here is something similar to what Michel Pierron suggested in JM\B's link

Sub b()
Dim sConn as String, WshNetwork as Object, i as long
Dim avTmp as String
#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
End Sub


Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

The avTmp part is to get the local version of the word "on" to cater to
international differences and was suggested by KeepItCool

 

[Tom Ogilvy]

Added the variable declarations after the fact and screwed them up. Here is
the corrected version:

Sub b()
Dim avTmp as Variant, i as Long, sConn as String
Dim WshNetwork as Object
#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Next
End Sub


Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

 

[JMB]

Yes, I knew that, but when Intellisense brings up the listing of dialogs the
two are right next to each other (I figured if he found one, he knows about
the other). I just don't see how the PrinterSetup will offer any significant
advantage over the regular Print dialog box if it is to the point that the
user has to select the printer (unless maybe you don't want to present the
user w/the option to cancel the print -but I don't think that was the
problem).

Thanks for posting the code, Tom. I will add it to my growing collection.