• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1619
  • Last Modified:

VBA: WshShell.RegRead Root with UNC Path

Using VBA for a Excel Macro that pulls the port name from the Windows Registry. If I have a UNC in the key name it throws an "Invalid Root in Registry Key" error on the Printer1 line. (HKEY_CURRENT_USER\Software\Microsoft\WindowsNT\CurrentVersion\PrinterPorts\\\Server01\IT Printer)

As long as the printer name is not a UNC name, it works just fine as shown with the Printer2 line.

Private Sub Form_Load()
pName1 = "\\SERVER01\IT PRINTER"
pName2 = "Microsoft Office Document Image Writer"
Set WshShell = CreateObject("WScript.Shell")
Printer1 = pName1 & " on " & Mid(WshShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\" & pName1), 10, 5)
Printer2 = pName2 & " on " & Mid(WshShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\" & pName2), 10, 5)
Application.ActivePrinter = Printer1
MsgBox Application.ActivePrinter
Application.ActivePrinter = Printer2
MsgBox Application.ActivePrinter
End Sub

Anybody got any ideas on how to get WshShell.RegRead to accept a UNC Name?
Thanks,
Jason
0
Honig
Asked:
Honig
  • 4
  • 2
1 Solution
 
Rory ArchibaldCommented:
Hi Jason,
As far as I know, the RegRead method will not accept a double backslash.

Regards,

Rory
0
 
HonigAuthor Commented:
After further googling I found a site that cites that it will not accept key words begining with slashes at all. The RegRead function is a bunch of hooey for this case. But if it was programed "right" to begin with, we would not have to worry about telling Excel what port name we were trying to print to anyway. :)

For now it prompts the user a few times to select the correct printer during the macro. It is "good enough" at the moment.

Rorya gets the points cause he/she at least posted.
Thanks!
Jason
0
 
Rory ArchibaldCommented:
Jason,
Thanks for the points and grade. You can also use the Windows API to read the registry - let me know if you want pointers on that.
Regards,
Rory
PS "he/she at least posted" - that would be a 'he', not a 'she'! ;-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rory ArchibaldCommented:
Here is the API stuff to put in a module:

Const HKEY_CURRENT_USER = &H80000001
Const REG_SZ = 1

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 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, lngDataLen As Long, lngKeyType As Long, lngCurrent As Long
   Dim varReturn
   Dim strReturn As String
   RegOpenKey HKEY_CURRENT_USER, _
      "Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts", hKey
   lngCurrent = RegQueryValueEx(hKey, strKeyName, 0, lngKeyType, 0, lngDataLen)
   Debug.Print 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)
    End Select
   RegCloseKey hKey
   GetPrinterPort = varReturn
End Function

You can then adapt your code to:
Private Sub Form_Load()
pName1 = "\\SERVER01\IT PRINTER"
pName2 = "Microsoft Office Document Image Writer"
Printer1 = pName1 & " on " & getprinterport(pName1)
Printer2 = pName2 & " on " & getprinterport(pName2)
Application.ActivePrinter = Printer1
MsgBox Application.ActivePrinter
Application.ActivePrinter = Printer2
MsgBox Application.ActivePrinter
End Sub


HTH

Rory
0
 
HonigAuthor Commented:
That works perfectly!

Many thanks again Rory!
Have a great day,
Jason
0
 
Rory ArchibaldCommented:
Happy to help. I figured the first answer wasn't really worth that many points! :-)
Regards,
Rory
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now