Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Available Printers (XL97; SR2)

    Is it possible to generate a list in XL of the available printers one has?

    The navigation to view the printers would be: Start>Settings>Printers

    Thanks,
    John

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Available Printers (XL97; SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    I don't really understand what you are refering to, but in MS-Excel you can use:

    Application.ActivePrinter to get your printer that is at the time the active, selected, printer.

    To find a list of all printers installed, I think, AFAIK, you will need to use an API to let Windows give you this info

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Available Printers (XL97; SR2)

    Wassim,

    To help clarify my intent, I was thinking of having an EndUser select a printer from a dropdown. The dropdown list would consist of those available printers he/she would have access to as noted in START>SETTINGS>PRINTERS.

    While researching this further a found a neat topic in Help regarding "Callback Property Example" using the Balloon Assistant (the pesky paperclip). This does not resolve my question but the "Callback Property Example" code is as follows:

    This example displays a balloon that contains a selection of three printers. After the user clicks the OK button on the balloon, the ProcessPrinter procedure is run and the balloon is closed.

    Sub shar()
    Set bln = Assistant.NewBalloon
    With bln
    .Heading = "Select a Printer."
    .Text = "Click OK when you've selected a printer."
    .Labels(1).Text = "Network Printer"
    .Labels(2).Text = "Local Printer"
    .Labels(3).Text = "Local Color Printer"
    .BalloonType = msoBalloonTypeButtons
    .Mode = msoModeModeless
    .Callback = "ProcessPrinter"
    .Button = msoButtonSetOK
    .Show
    End With
    End Sub

    (Every procedure specified in the Callback property is passed three arguments: the balloon that activated the procedure, the return value of the button the user pressed, and an integer that uniquely identifies the balloon that called the procedure.)

    Sub ProcessPrinter(bln As Balloon, ibtn As Long, _
    iPriv As Long)
    Assistant.Animation = msoAnimationPrinting
    Select Case ibtn
    Case 1
    ' Insert printer-specific code
    bln.Close
    Case 2
    ' Insert printer-specific code
    bln.Close
    Case 3
    ' Insert printer-specific code
    bln.Close
    End Select
    End Sub


    Thanks,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Available Printers (XL97; SR2)

    Click here to obtain the code that will list the available printers.

    John

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Available Printers (XL97; SR2)

    We'll I thought I was done. Unfortunately I need the port the printer is on. An example of this would be "MyPrinter on LPT1:". The code in the link only provide the name of the printer such as "MyPrinter".

    Any ideas on how to get to the port? Some of the printers are network printers.

    Thanks,
    John
    And I'm not talking about the port we drink or sail into.

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Available Printers (XL97; SR2)

    John,

    See if the following works for you. It used to work for me when I was on a network.
    '--------------------------------------------
    'Printers must be on a network for this to work?
    'Lists printers/ports in a message box.
    'May require a reference to the Microsoft Scripting Runtime (scrrun.dll).
    'Windows Script Host is provided with most versions of Windows.

    Sub ListThem()
    Dim WshNetwork As Object
    Dim oPrinters As Variant
    Dim strPrinterList As String
    Dim i As Long

    Set WshNetwork = CreateObject("WScript.Network")
    Set oPrinters = WshNetwork.EnumPrinterConnections
    For i = 0 To oPrinters.Count - 1 Step 2
    strPrinterList = strPrinterList & oPrinters.Item(i + 1) & _
    " on " & oPrinters.Item(i) & vbCr
    Next 'i
    Set WshNetwork = Nothing
    MsgBox strPrinterList
    End Sub
    '------------------------------------------

    Also, why not just let the user pick the printer with this....

    Application.Dialogs(xlDialogPrinterSetup).Show

    Regards,

    Jim Cone
    San Francisco, CA

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Available Printers (XL97; SR2)

    Jim,

    In response to "Also, why not just let the user pick the printer with this....", because I did not know about it.

    This snippet of code is exactly what I'm after.

    Now how does one go about controlling a dialog box? Such as if "Ok" selected do this. If "Cancel" then End.

    Thanks,
    John

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Available Printers (XL97; SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    I see that a lot of information has been posted in regards to the printer selection, and I just wanted to answer you and also provide the answer for you last question:

    <<< Now how does one go about controlling a dialog box? Such as if "Ok" selected do this. If "Cancel" then End. >>>

    Hum!!! Did you read what you just wrote? Its an If-Then-Else statement.

    But if you are using a Userform, then put the code in the Button_Click event, and test your conditions there.

    If you are using a dialog, then the code will stop at the .Show line, waiting until the user finishes with the dialog, and thus you would test for your conditions, right after the .Show line. Usually the OK button evaluates for an integer, I think it might be 7, or you can use the more intuitive vbOK, and the Cancel is a 6, but don't hold me to these numbers test them first.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Available Printers (XL97; SR2)

    Wassim,

    I did realize that it is an "If Statement". I am not familiar with controlling the Dialog Boxes such as in : Application.Dialogs(xlDialogPrinterSetup).Show

    After experimenting with code I came up with:

    tmp = Application.Dialogs(xlDialogPrinterSetup).Show
    If tmp = True Then
    Run some code here
    Else
    End
    End If


    I just wish that MSoft's Help page would be clearer on how to control dialog boxes.
    Anyway, it was worth the exercise. <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>
    John

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Available Printers (XL97; SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    Its the best learning process when you figure things out all on your own. And if the MS-Help system was any good, we would not have such activity in the lounge. Though I think sometimes the MS-Help can get you started, it needs some more meat...

    But until we have the great help system we all are looking for, keep a bookmark for this lounge, and ask as many questions and you need.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Available Printers (XL97; SR2)

    John,

    It looks like you are squared away, but for what its worth, another way to handle the code would be...

    Dim ActPrint as String
    ActPrint = Application.ActivePrinter
    Application.Dialogs(xlDialogPrinterSetup).Show

    If ActPrint = Application.ActivePrinter then
    ' no changes were made

    Elseif Len(ActPrint) = 0 then
    'no printer is selected

    Else
    ActPrint = Application.ActivePrinter ' ActPrint now contains new printer name
    End if

    Regards,

    Jim Cone

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •