Get Available Printers w/VBA

[Chris Picklesimer]

I want to populate a combo box with all printers that
would exist in the Excel Print dialog box. (Excel 2000
VBA) How can I do this?

Thanks.

Chris

[Vasant Nanavati]

Hi Chris:

AFAIK, this is not available through VBA since VBA does not have a Printer
object; just an ActivePrinter property. I'm sure there's a way of doing it
if you want to plow through theWindows API.
--
Regards,

Vasant.

**No direct emails please--keep discussion in newsgroup.**

"Chris Picklesimer" <cpickl...@palmharbor.com> wrote in message
news:e46401c1ab38$e7e5a630$a4e62ecf@tkmsftngxa06...

[Chris Picklesimer]

Hi Vasant,

Thanks for the tip. I was able to find some Windows API
code that retrieves the system printers. The problem I
have now is that it appears the retrieved system printer
names are different than those used in Excel. For example
with API, I retrive a system printer named "\\NTPRINT\ENG
4050N". If I manually change the printer in excel to that
printer and then place Msgbox ActiverPrinter in some code,
I get "\\NTPRINT\ENG 4050N on Ne4". On another PC, I do
the same thing and the name is "\\NTPRINT\ENG 4050N on
Ne2". What is the " on Ne?" that Excel tags on the the
name of the Windows system printers?

Thanks for any additional help.

Chris

>.
>

[David McRitchie]

Hi Chris,
Here's part of it, I think.

Q166008 - ACC: Enumerating Local and Network Printers
http://support.microsoft.com/support/kb/articles/q166/0/08.asp

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> "Chris Picklesimer" <cpickl...@palmharbor.com> wrote ...

[Tom Ogilvy]

Not sure why you can't popup the built in printer selection dialog.

Sub TestDialog()
Application.Dialogs(xlDialogPrinterSetup).Show
End Sub

But here is some information:

http://support.microsoft.com/support/kb/articles/q166/0/08.asp

ACC: Enumerating Local and Network Printers

Athough the above article is for Access, the code works in Excel as well.

Enumerating Windows' Available Ports
http://www.mvps.org/vbnet/code/enums/enumports.htm
=======================

This posting by Jim Rech may be useful as well - certainly simpler:

========< J Rech Post >=======
From: "Jim Rech" <jar...@kpmg.com>
Subject: Re: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you may be
able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub

--
Jim Rech
Excel MVP

=========< J Rech Post >=====

--
Regards,
Tom Ogilvy

"Vasant Nanavati" <vas...@aol.com> wrote in message
news:eKykjwzqBHA.2080@tkmsftngp03...

[Vasant Nanavati]

Hi Tom:

I just assumed that he wanted to do something other than just select a
printer to print to. But I have a tendency to read too much into questions.
Quite likely your straighforward answer is the right one! <g>

Actually, I guess he could use your solution and then use the ActivePrinter
property to identify the selection.
--
Regards,

Vasant.

**No direct emails please--keep discussion in newsgroup.**

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:#lRIKT0qBHA.2516@tkmsftngp03...

[Tom Ogilvy]

Normally, that is the port, such as
on LPT1

But when Windows 2K came out the Ne started to appear. I believe it has
something to do with Print Servers, but I haven't really had time to figure
it all out.

Regards,
Tom Ogilvy

"Chris Picklesimer" <cpickl...@palmharbor.com> wrote in message

news:112f601c1ab42$b17d61a0$19ef2ecf@tkmsftngxa01...

[Chris Picklesimer]

Hi Vasant,

Thanks for the tip. I was able to get some API code that
retrieved the Windows system printers. I still have a
problem. The names of the Windows system printers are
different than the printers Excel uses. For example, in
the immediate window if type ?ActivePrinter, I get:
\\NTPRINT\ENGINEERING 4050 on Ne05

The name retrieved from thr API code is:
\\NTPRINT\ENGINEERING 4050

What does the " on Ne05" mean? I noticed that on another
PC in the office the ?ActivePrinter was:
\\NTPRINT\ENGINEERING 4050 on Ne02

How can I determine what the " on Ne0?" is?

Thanks.

Chris

>.
>

[Chris Picklesimer]

Hi Tom

I want to be able to change the ActivePrinter to one of
the printers returned from your two examples. I added a
line to fill my combobox on sheet1.

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives

Set oprinters = WshNetwork.EnumPrinterConnections
For i = 0 To oprinters.Count - 1 Step 2
Debug.Print "Port " & oprinters.Item(i) & " = " & _
oprinters.Item(i + 1)

Sheet1.cboPrinter.AddItem oprinters.Item(i + 1)'****
Next
End Sub

I want to then be able to go to the combobox and change
the ActivePrinter by selecting one of the listed printers
(see below). How can I do that?

Private Sub cboPrinter_Change()
ActivePrinter = cboPrinter.Text
End Sub

Thanks

Chris

>.
>