none
Excel active printer port

    Question

  • Hello,

    I am trying to determine if active printer supports A3 page format. For that I am using some API's. For those API's I need to have printer name and printer port, so I use ActivePrinter function and extract the information I need. Problems begin when users use my application in localized Excel versions. This is how the same printer is defined in English and Lithuanian versions of Excel: "Brother DCP-7010 USB Printer on Ne02:", "Brother DCP-7010 USB Printer, esanti Ne02:". So there is no clear way to get printer name and port using ActivePrinter function. I would like to know if there is any API that could be a substitute for ActivePrinter function and provide Excel's active printer name and port.

    By the way, this problem is also described here: http://stackoverflow.com/questions/2148959/excel-how-to-get-a-locale-independent-printer-name.

    Thursday, April 07, 2011 5:00 PM

All replies

  • A heretical approach - let the user decide...

    Sub PrinterSelectionDemo()
     Dim bChoice As Boolean
     bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(ActivePrinter)
     If Not bChoice Then
        MsgBox "User cancelled"
     Else
       MsgBox ActivePrinter
     End If
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Special Print add-in:  long columns printed side by side)

    Friday, April 08, 2011 2:57 PM
  • I am using something like that in order to let user to select the appropriate printer. However, there still should be an additional check done to see if that printer really supports that feature. Is there really no API that could provide active printer name and port?
    Friday, April 08, 2011 6:13 PM
  • The following is the only way I know of to get a list of network printers/ports.
    You should be able to parse/extract the port from it.
    How this will look in the Lithuanian version I can't say...

      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
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

    Friday, April 08, 2011 8:05 PM
  • hi,

    another way to do it with an object winmgmts

    .

    Sub Properties_Printers()
    Dim objWMIService As Object, colItems As Object
    Dim objItem As Object
    Dim strComputer As String
    Dim i As Byte

    On Error Resume Next
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = _
        objWMIService.ExecQuery("Select * from Win32_PrinterConfiguration", , 48) 'adapt
    MyPrinter = ActivePrinter
    For Each objItem In colItems
        i = i + 1
        Cells(1, i) = "BitsPerPel: " & objItem.BitsPerPel
        Cells(2, i) = "Caption: " & objItem.Caption
        Cells(3, i) = "Collate: " & objItem.Collate
        Cells(4, i) = "Color: " & objItem.Color
        Cells(5, i) = "Copies: " & objItem.Copies
        Cells(6, i) = "Description: " & objItem.Description
        Cells(7, i) = "DeviceName: " & objItem.DeviceName
        Cells(8, i) = "DisplayFlags: " & objItem.DisplayFlags
        Cells(9, i) = "DisplayFrequency: " & objItem.DisplayFrequency
        Cells(10, i) = "DitherType: " & objItem.DitherType
        Cells(11, i) = "DriverVersion: " & objItem.DriverVersion
        Cells(12, i) = "Duplex: " & objItem.Duplex
        Cells(13, i) = "FormName: " & objItem.FormName
        Cells(14, i) = "HorizontalResolution: " & objItem.HorizontalResolution
        Cells(15, i) = "ICMIntent: " & objItem.ICMIntent
        Cells(16, i) = "ICMMethod: " & objItem.ICMMethod
        Cells(17, i) = "LogPixels: " & objItem.LogPixels
        Cells(18, i) = "MediaType: " & objItem.MediaType
        Cells(19, i) = "Name: " & objItem.Name
        Cells(20, i) = "Orientation: " & objItem.Orientation
        Cells(21, i) = "PaperLength: " & objItem.PaperLength
        Cells(22, i) = "PaperSize: " & objItem.PaperSize
        Cells(23, i) = "PaperWidth: " & objItem.PaperWidth
        Cells(24, i) = "PelsHeight: " & objItem.PelsHeight
        Cells(25, i) = "PelsWidth: " & objItem.PelsWidth
        Cells(26, i) = "PrintQuality: " & objItem.PrintQuality
        Cells(27, i) = "Scale: " & objItem.Scale
        Cells(28, i) = "SettingID: " & objItem.SettingID
        Cells(29, i) = "SpecificationVersion: " & objItem.SpecificationVersion
        Cells(30, i) = "TTOption: " & objItem.TTOption
        Cells(31, i) = "VerticalResolution: " & objItem.VerticalResolution
        Cells(32, i) = "XResolution: " & objItem.XResolution
        Cells(33, i) = "YResolution: " & objItem.YResolution
         Columns(i).AutoFit
    Next
    End Sub
      isabelle
    ------------------------------------------------------------------

    Le 2011-04-08 14:13, fifi5 a écrit :

    I am using something like that in order to let user to select the appropriate printer. However, there still should be an additional check done to see if that printer really supports that feature. Is there really no API that could provide active printer name and port?

    Friday, April 08, 2011 8:39 PM
  • Problems with Jim's suggestion: 1) I do not see a way to find out which printer is currently an active one; 2) code is not working correctly, i.e. it lists "Adobe PDF on Documents\*.pdf" when it should be "Adobe PDF on Ne03:".

    Problems with Isabelle's suggestion: 1) I do not see a way to find out which printer is currently an active one; 2) code is not providing printer port.

    Friday, April 08, 2011 9:22 PM
  • why not keeping only the parts that interest you in the three suggestions you have

    .

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-08 17:22, fifi5 a écrit :

    Problems with Jim's suggestion: 1) I do not see a way to find out which printer is currently an active one; 2) code is not working correctly, i.e. it lists "Adobe PDF on Documents\*.pdf" when it should be "Adobe PDF on Ne03:".

    Problems with Isabelle's suggestion: 1) I do not see a way to find out which printer is currently an active one; 2) code is not providing printer port.

    Friday, April 08, 2011 10:16 PM
  • 1. First code lets user to choose the printer. But there is still no way to know what is its port.

    2. Second code provides a list of available printers. But which one is currently the active one? But what is its port?

    3. Third code does something similar as the second one does. So questions remain the same.

    Maybe I am missing something?

     

    Friday, April 08, 2011 11:16 PM
  • hi,

    If I refer to the link suggest in your first question
    i understand that what you want to do is separate ActivePrinter value

    Sub test()
    Dim Info, MyPrinter As String, MyPrinter As String, x As String
    MyPrinter = ActivePrinter
    Info = Split(MyPrinter, " ")
    port = Left(Info(UBound(Info)), Len(Info(UBound(Info))) - 1)
      For i = 1 To UBound(Info) - 2
         x = x & Info(i) & " "
      Next
    MyActivePrinter = Left(x, Len(x) - 1)
    MsgBox "ActivePrinter : " & MyActivePrinter & Chr(10) & Chr(10) & "Port : " & port
    End Sub

    .
     isabelle
    ------------------------------------------------------------------

    Le 2011-04-08 19:16, fifi5 a écrit :

    1. First code lets user to choose the printer. But there is still no way to know what is its port.

    2. Second code provides a list of available printers. But which one is currently the active one? But what is its port?

    3. Third code does something similar as the second one does. So questions remain the same.

    Maybe I am missing something?


    Friday, April 08, 2011 11:54 PM
  • sorry here's the correction

    Sub test()
    Dim Info, MyPrinter As String, MyActivePrinter As String, x As String, i As Integer
    MyPrinter = ActivePrinter
    Info = Split(MyPrinter, " ")
    port = Left(Info(UBound(Info)), Len(Info(UBound(Info))) - 1)
      For i = 1 To UBound(Info) - 2
         x = x & Info(i) & " "
      Next
    MyActivePrinter = Left(x, Len(x) - 1)
    MsgBox "ActivePrinter : " & MyActivePrinter & Chr(10) & Chr(10) & "Port : " & port
    End Sub

    .

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-08 19:54, isabelleV a écrit :

    hi,

    If I refer to the link suggest in your first question
    i understand that what you want to do is separate ActivePrinter value

    Sub test()
    Dim Info, MyPrinter As String, MyPrinter As String, x As String
    MyPrinter = ActivePrinter
    Info = Split(MyPrinter, " ")
    port = Left(Info(UBound(Info)), Len(Info(UBound(Info))) - 1)
       For i = 1 To UBound(Info) - 2
          x = x&  Info(i)&  ""
       Next
    MyActivePrinter = Left(x, Len(x) - 1)
    MsgBox "ActivePrinter : "&  MyActivePrinter&  Chr(10)&  Chr(10)&  "Port : "&  port
    End Sub

    .
      isabelle
    ------------------------------------------------------------------

    Le 2011-04-08 19:16, fifi5 a écrit :

    1. First code lets user to choose the printer. But there is still no way to know what is its port.

    2. Second code provides a list of available printers. But which one is currently the active one? But what is its port?

    3. Third code does something similar as the second one does. So questions remain the same.

    Maybe I am missing something?


        

    Saturday, April 09, 2011 12:25 AM
  • Isabelle, I do appreciate your help. But your code is not working correctly. ActivePrinter = "Adobe PDF, esanti NeO3:" and your code tells that "ActivePrinter: PDF,    Port: Ne03". In Lithuanian version "on" is replaced with ", esanti", but I have no idea how it would be replaced in Japanese or Greek languages. That's why I was looking for an API. I believe that ActivePrinter function is not a solution when someone is trying to find out active printer's port and name under localized Excel version.

    Sunday, April 10, 2011 12:07 AM
  • ok i see what you mean the other possibility that i think is this :

    Sub PrinterAndPort()
    Dim WshNetwork As Object
    Dim oPrinter As Object
    Dim i As Integer
    Dim PrinterInfo
    Dim MyPrinter As String
    
    Set WshNetwork = CreateObject("WScript.Network")
    Set oPrinter = WshNetwork.EnumPrinterConnections
    
    PrinterInfo = Split(ActivePrinter, " ")
      For i = 0 To UBound(PrinterInfo) - 2
         MyPrinter = MyPrinter & PrinterInfo(i) & " "
      Next
    MyPrinter = Application.Trim(MyPrinter)
    
    For i = 1 To oPrinter.Count - 1 Step 2
     If oPrinter.Item(i) = MyPrinter Then
       MsgBox "Printer =  " & oPrinter.Item(i) & vbCrLf & "Port     =  " &
    oPrinter.Item(i - 1)
     End If
    Next
    End Sub

    .
    isabelle
    ------------------------------------------------------------------

    Le 2011-04-09 20:07, fifi5 a écrit :

    Isabelle, I do appreciate your help. But your code is not working correctly. ActivePrinter = "Adobe PDF, esanti NeO3:" and your code tells that "ActivePrinter: PDF,    Port: Ne03". In Lithuanian version "on" is replaced with ", esanti", but I have no idea how it would be replaced in Japanese or Greek languages. That's why I was looking for an API. I believe that ActivePrinter function is not a solution when someone is trying to find out active printer's port and name under localized Excel version.

    Sunday, April 10, 2011 2:27 PM