Printer Names in Combo Box 1

[SUnderwood]

Hi All,

I'm having a great time trying to figure THIS one out!

Using VBA I want to put a list of all available printers into a combo box for my users to select from. I will then set the current/active printer to that selected and print to it.

Sounds easy? Yes, and so it should be, but NO! It’s a GHASTLY HORROR!!!! HELP!!!!!

Sean

 

[zemp]

I use the windows printers collection from VB, loop through it and place the items in a list box.

Dim l_pr As Printer

For Each l_pr In Printers
List1.AddItem l_pr.DeviceName
Next


Thanks and Good Luck!

zemp

 

[SUnderwood]

Hi Zemp

Thanks, but I'm restricted to using Excel 2000 VBA (alas no VB).

Any other ideas. I'm fast losing my marbles!

sean

 

[zemp]

There are a lot of similarities, so you have nothing to lose if you try it. Windows is still windows. Look into your VBA help for the printers collection and see how it is accessed. There may be a syntax change but the basics may be the same.



Thanks and Good Luck!

zemp

 

[SUnderwood]

Thanks Zemp

I have spent the last 4 hours looking through VBA help the internet in general, MS website, this website ....

And I think I have just found an answer!

I will post the results (if there are any) and probably make an FAQ as 1) Its a GHASTLY excercise to have to repeat 2) My name will go down in history!!! (well...)

If anyone know of some easy way to determine the printer lists please let me know, the way I've found is not all that easy!

Sean

 

[CajunCenturion]

I have a solution that you may want to try, and it's not that difficult, but you do need to include a reference to the Windows Script Host Object Model in your code.

One you setup your combobox, as type ValueList, execute the following from within the form to populate the combobox.

   Dim lStr_PrinterList   As String
   
   lStr_PrinterList = ListAllPrinters
   cboPrinters.RowSource = lStr_PrinterList

And in a module, add the ListAllPrinters function. You skip every other one because the even numbered entries have the Port names, and you don't have to include those.

Public Function ListAllPrinters() As String

   Dim lObj_ScriptControl        As IWshNetwork_Class
   Dim lCol_Printers             As IWshCollection_Class
   Dim lStr_PrinterList          As String
   Dim lInt_Idx                  As Integer
   
   Set lObj_ScriptControl = New IWshNetwork_Class
   Set lCol_Printers = lObj_ScriptControl.EnumPrinterConnections

   lStr_PrinterList = vbNullString
   For lInt_Idx = 1 To lCol_Printers.Count - 1 Step 2
      lStr_PrinterList = lStr_PrinterList & lCol_Printers.Item(lInt_Idx) & ";"
   Next lInt_Idx

   If (Right(lStr_PrinterList, 1) = ";") Then
      lStr_PrinterList = Left(lStr_PrinterList, Len(lStr_PrinterList) - 1)
   End If

   Set lObj_ScriptControl = Nothing
   Set lCol_Printers = Nothing
   
   ListAllPrinters = lStr_PrinterList

End Function

Then in the AfterUpdate event of the combobox add the following:

Private Sub cboPrinters_AfterUpdate()
   SetPrinterAsDefault cboPrinters.Text
End Sub

And in the module, add the SetPrinterAsDefault function

Public Sub SetPrinterAsDefault(rStr_PrinterName As String)

   Dim lObj_ScriptControl        As IWshNetwork_Class
   
   Set lObj_ScriptControl = New IWshNetwork_Class
   lObj_ScriptControl.SetDefaultPrinter rStr_PrinterName
   Set lObj_ScriptControl = NothingEnd Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ShaneCU]

SUnderwood,

Did you ever solve this issue using VBA? I couldn't get CajunCenturion's to work (could his be VB not for Apps?). I am running into the same issues and would greatly appreciate your help.

Here is my situation:

Is there a way to retrieve the NeXX (I'm guessing this is a pointer) that Windows NT uses for a specified printer (other than the ActivePrinter) using VBA? It looks like VB6 can do this, but I can't find a way w/ VBA.

When assigning a printer to ActivePrinter in Win98 it appears that you can use = [printer name] on [port name]. With NT this will work with local printers, but not Network printers. With NT you have to use = [printer name] on NeXX.

Based on searching this forum and other resources, I have been able to deduce that I could use a loop and an "on error" statement to cycle through Ne00, Ne01, etc. until one works.

Certainly there has to be a better way to do this?

(note: It may be helpful to know that I already have code that can retrieve printer details such as name, port number, driver name, status, etc.)

 

[CajunCenturion]

When you say you couldn't get it work, exactly how is it failing?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ShaneCU]

CajunCenturion,

Thanks for following up on this.

I am not in front of my PC right now so I can't provide you with very detailed information, but if I remember correctly it gave me a compilation error on the first line in the routine ( Dim lObj_ScriptControl As IWshNetwork_Class ). I don't think it knew what IWshNetwork_Class is.

You did say that I need to include a reference to the Windows Script Host Object in my code -- this could be where I am going wrong. How do I do this?

(Note: I am using VBA in Excel)

 

[CajunCenturion]

You are correct that the compile error is caused by not having the reference included. That reference contains the object defintions for the IWshNetwork_Class.

From the VBA Editor, select "Tools" from the Menu Bar and then select "References". This will open a window in which you should scroll down very near the bottom (listed alphabetically) and check the box next to the Windows Script Host Object.

If you notice in the loop

For lInt_Idx = 1 To lCol_Printers.Count - 1 Step 2
   lStr_PrinterList = lStr_PrinterList & lCol_Printers.Item(lInt_Idx) & ";"
Next lInt_Idx

it starts at 1 (not zero) and skips every other entry. Based on what I think you may be trying to do, you may need to look at the even numbered entries as they define the ports to which the printers are assigned.
Item 0 is the port assignment for the printer in Item 1
Item 2 is the port assignment for the printer in Item 3
and so forth.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ShaneCU]

Looks good. I'll try this tomorrow when I am back in front of the program. I'll let you know then how it works out.

 

[paulbent]

The relevant API functions are:

- GetVersionEx to determine the Windows version.
- If W9x/ME, EnumPrinters with a PRINTER_INFO5 structure.
- If NT/2000/XP, EnumPrinters with a PRINTER_INFO4 structure.

If you do a Google search for EnumPrinters you should get some good VB examples.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[ShaneCU]

Paulbent -- If I am correct, that only applies to VB5 or VB6, not VBA

CajunCenturion -- I can already do what your code does (although MUCH more inefficiently...I wish I would have found your post a week ago). It is returning a list of printers and their port names as displayed when you look at a printers properties. What I am after is the Ne# that Windows is assigning to each network printer.

The Ne# is displayed easily in VB6 using printers.port Is there any way to replicate that?

I tried to deduce the Ne# based on the order the printer names are returned and it almosted worked. I was thwarted by the local printers being listed amongst the network printers (local printers do not have Ne#s).

Any thoughts?

 

[ShaneCU]

Here is what I used to get it to work -- I had to use error handling which is not what I wanted to do because I believe that it introduces more margin for error -- if you figure out how to return the Ne# using VBA let me know.

Sub SetPrinter()
On Error GoTo Network
Application.ActivePrinter = [returned ptr name] & " on " & [returned printer port]
Exit Function

Network:
Dim blnWrongNe As Boolean
Dim i As Integer

On Error GoTo Err_Handler
Application.ActivePrinter = [returned ptr name] & " on " & "Ne00:"
If blnWrongNe Then
blnWrongNe = False
For i = 1 To 9
Application.ActivePrinter = [returned ptr name] & " on " & "Ne0" & i & ":"
If Not blnWrongNe Then Exit Function
blnWrongNe = False
Next i
End If

Err_Handler:
blnWrongNe = True
Resume Next

End Sub

 

[CajunCenturion]

If you've got it working, then that's great. I don't know right off how best to get that information, but I do know that you can call any of the API functions that paulbent mentions from VBA. I would look into those first. If that didn't work, then I would look into the WNetEnumResource API (which will need the WNetOpenEnum and WNetCloseEnum API's as well) to perhaps find that info.

In any event, I'm glad it's working for you - that's what counts.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[ShaneCU]

CajonCenturion (or paulbent), how do I call those API functions in VBA excel? I tried putting PRINTER_INFO5 in a sub and it said it was undefined. I've tried referencing a bunch of things, but none of them have given me any success.

 

[CajunCenturion]

In the declarations section of a module, add the following declarations:

Public Type PRINTER_INFO_5
  pPrinterName As String
  pPortName As String
  Attributes As Long
  DeviceNotSelectedTimeout As Long
  TransmissionRetryTimeout As Long
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

or whichever Printer Info Type corresponds to your OS. Then following the examples from the following site:

http://www.partware.com/ebooks/api/ref/e/enumprinters.html

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[strongm]

>that only applies to VB5 or VB6, not VBA

Works fine for VBA...(it's worth remembering that VB6 is built on top of VBA, just with lots of added extras, the same VBA that comes with the various MS Office applications)

 

[innov]

Hi Sean.

I know this post is old, but I'm having the SAME ISSUES retrieving the NE0 port in EXCEL VBA.

Did you ever resolve your problem? If so, I'd really appreciate it if you'd share your code.

THANKS!

Innov

 

[SUnderwood]

Alas, I don't have the code anymore. I finished the contract last year and forgot to take copies of the routines i used (hence, I have never needed them again either, but on some rare occation in the future, I'm sure I;'ll be back with the same problem)

I remeber the only way around the problem was the long way around with some delicate programming which i really did not like to use. But what it was I have no idea.

So, I've been pritty useless actually! Sorry about that old boy.

Sean


Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
seanunderwood1@hotmail.com

http://www.emergencydatabasesupport.port5.com