Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    USB Printer Settings in Macros (Office XP / Windows XP Home)

    Hey all....

    I have a bunch of Excel macros that automatically print to 4 different USB printers on my system. Every time I install a new USB device I'm taking a chance that the USB setting will change for each printer, subsequently requiring me to rework all the macros. The is a royal pain!!!!

    In the Excel macros the printer addresses are identified as Ne01, Ne02, Ne03 etc. These addresses are generated when I use the macro wizard to automatically create a macro. This addressing doesn't seem to have any direct relationship to the USB001, USB002, USB003 etc. settings under printer port settings in CONTROL PANEL, PRINTERS.

    Has anyone ever dealt with this issue? Is there a way to force the Ne0# settings back to an earlier pattern so I don't have to edit all the macros when adding a USB device?

    Thanks,
    BH

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: USB Printer Settings in Macros (Office XP / Windows XP Home)

    Try this:
    <pre>Private Declare Function RegCloseKey Lib "advapi32.dll" _
    (ByVal hKey As Long) As Long

    Private Declare Function RegOpenKey Lib "advapi32.dll" _
    Alias "RegOpenKeyA" (ByVal hKey As Long, _
    ByVal lpSubKey As String, phkResult As Long) As Long

    Private Declare Function RegSetValueEx Lib "advapi32.dll" _
    Alias "RegSetValueExA" (ByVal hKey As Long, _
    ByVal lpValueName As String, ByVal Reserved As Long, _
    ByVal dwType As Long, lpData As Any, _
    ByVal cbData As Long) As Long

    Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
    Alias "RegQueryValueExA" (ByVal hKey As Long, _
    ByVal lpValueName As String, ByVal lpReserved As Long, _
    ByRef lpType As Long, lpData As Any, _
    ByRef lpcbData As Long) As Long

    Public Function GetPrinterPort(strKeyName As String)
    Dim hKey As Long, lngReturn As Long, lngDataLen As Long, lngKeyType As Long, lngCurrent As Long
    Dim varValue, varReturn
    Dim strReturn As String
    RegOpenKey HKEY_CURRENT_USER, _
    "SoftwareMicrosoftWindows NTCurrentVersionPrinterPorts", hKey
    lngCurrent = RegQueryValueEx(hKey, strKeyName, 0, lngKeyType, 0, lngDataLen)
    Select Case lngKeyType
    Case REG_SZ
    strReturn = String$(lngDataLen, 0)
    lngCurrent = RegQueryValueEx(hKey, strKeyName, _
    0, lngKeyType, ByVal strReturn, lngDataLen)
    varReturn = Mid$(strReturn, 10, 5)
    Case REG_DWORD
    lngCurrent = RegQueryValueEx(hKey, strKeyName, _
    0, lngKeyType, lngReturn, lngDataLen)
    varReturn = lngReturn
    Case REG_BINARY
    lngCurrent = RegQueryValueEx(hKey, strKeyName, _
    0, lngKeyType, ByVal strReturn, lngDataLen)
    varReturn = Left(strReturn, lngDataLen)
    End Select
    RegCloseKey hKey
    GetPrinterPort = varReturn
    End Function
    </pre>


    use it like:
    <code>
    strPrintername = "xxxxxxx"
    strPrinterPort = getprinterport(strPrinterName)
    </code>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: USB Printer Settings in Macros (Office XP / Wi

    Rory,

    Thanks for the post. A bit over my head though I'm afraid. Can you tell me where to look to learn more about this Ne0# printer assignment scheme?

    Thanks,
    BH

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: USB Printer Settings in Macros (Office XP / Wi

    What did you want to know?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: USB Printer Settings in Macros (Office XP / Wi

    Does Excel assign these Ne0#s or is it a Windows thing? Excel macros are the only place I see them. In Control Panel, Printers USB port assignments are listed as USB001, USB002 etc. Why don't they show up that way when I create Excel macros. Is there a tech site that explains this system? That sort of thing is what I'd like to know about......just more about how and why the system works that way. I've posted this Ne0# question on a few tech forums over the past few years and never been able to get any kind of explanation. Quite frankly, your detailed coding is the only repsonse I've ever gotten!!!

    I've tried replacing the Ne0# with the USB00# in a macro and it doesn't work the way it would if it was a parallel port assignment listed.

    Thanks,
    BH

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: USB Printer Settings in Macros (Office XP / Wi

    As far as I know they are assigned by Windows. All network printers get given an NExx port (USB printers count as network printers) and macros in Excel and Word use this system; Access, as usual, is different - it seems to use the USB001 notation. If you use the code I posted, you only need to know the printer name - it will retrieve the port form the registry so you can append it for use in the macro. If I get a chance I will see if I can locate a proper technical explanation of the port assignments as I have a vague recollection of seeing one before.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: USB Printer Settings in Macros (Office XP / Wi

    Rory,

    Yes, that is exactly the type of explanation I had hoped to find. I really appreciate your taking a few minutes to post it for me!

    BH

Posting Permissions

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