Find registry key using Excel VBA

2
votes

I want to check if a user has Microsoft SQL Native Client 2008 (sqlncli10) or Native Client 2012 (sqlncli11) installed.

Dim key As String
Dim myShell As Object
Set myShell = CreateObject("WScript.Shell")

key = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI11\CurrentVersion\Version"
'key = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\CurrentVersion"

Dim strKey As String
strKey = myShell.RegReadkey(key)

When looking for the native client I receive the error:

Invalid root in registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\SQLNCLI11\CurrentVersion\Version"

but when trying the Windows NT version it works fine.

I replaced HKEY_LOCAL_MACHINE with HKLM: but that doesn't work either.

EDIT

I just figured it out I think. When I checked in the registry it exists in the 64-bit section. However VBA checks in 32-bit and the root for SOFTWARE becomes Wow6432Node. So it checks in Wow6432Node\Microsoft\Microsoft SQL Server and the key does not exist there. But I have found another path SOFTWARE\Microsoft\Microsoft SQL Server Native Client 10 alternatively 11 which exist in both 32-bit and 64-bit.

2
excelvbaregistry
This has already been answered: stackoverflow.com/questions/9604276/… - Davy C
Same result. I can find Windows NT but when doing exactly the same thing for SQL Native Client it can't find it. - Smurfa

2 Answers

1
votes

This code works for me:

Dim key As String
Dim objShell As Object

Set objShell = CreateObject("WScript.Shell")
key = objShell.RegRead _
    ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI11\CurrentVersion\Version")

I just did this in the Excel 2010 VBA editor.

0
votes

Instead of WScript use below function. This will work for both 32 and 64 bits.

Function ReadRegistry(RootKey, Key As String, Value As String, Optional RegType As Integer = 32) As String


    ' Reads a REG_SZ value from the local computer's registry using WMI.
    ' Parameters:
    '   RootKey - The registry hive (see http://msdn.microsoft.com/en-us/library/aa390788(VS.85).aspx for a list of possible values).
    '   Key - The key that contains the desired value.
    '   Value - The value that you want to get.
    '   RegType - The registry bitness: 32 or 64.


    Dim oCtx, oLocator, oReg, oInParams, oOutParams

    Set oCtx = CreateObject("WbemScripting.SWbemNamedValueSet")
    oCtx.Add "__ProviderArchitecture", RegType

    Set oLocator = CreateObject("Wbemscripting.SWbemLocator")
    Set oReg = oLocator.ConnectServer("", "root\default", "", "", , , , oCtx).Get("StdRegProv")

    Set oInParams = oReg.Methods_("GetStringValue").InParameters
    oInParams.hDefKey = RootKey
    oInParams.sSubKeyName = Key
    oInParams.sValueName = Value

    Set oOutParams = oReg.ExecMethod_("GetStringValue", oInParams, , oCtx)

    ReadRegistry = oOutParams.sValue
End Function

Sub test()
  Const HKEY_CURRENT_USER = &H80000001
  MsgBox ReadRegistry(HKEY_CURRENT_USER, "Software\Microsoft\Office\15.0\Outlook\Security", "OutlookSecureTempFolder")
End Sub