asked on

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

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Honig

ASKER

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

Rory Archibald

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'! ;-)

Rory Archibald

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

Honig

ASKER

That works perfectly!

Many thanks again Rory!
Have a great day,
Jason

Rory Archibald

Happy to help. I figured the first answer wasn't really worth that many points! :-)
Regards,
Rory