Read Registry Keys from within VBA using Windows Shell

Question

I have been searching around on the internet and am having problems finding a solution to this issue.

Basically I am trying to execute a registry query with administrator privileges using Shell.Application from within VBA to read the value of TypeGuessRows (and eventually modify it to 0 aswell so that excel data can be correctly queried using ADOdb). I have come up with the following sub routine:

Sub Read_Registry_Value()

'Declare variables
Dim reg_key_location As String
Dim reg_key_name As String
Dim wsh As Object

'Define registry key path and name
reg_key_location = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel"
reg_key_name = "TypeGuessRows"

'Create instance of windows shell
Set wsh = VBA.CreateObject("Shell.Application")

'Execute registry query with administrative privileges
wsh.ShellExecute "cmd", _
        "/K REG QUERY " & Chr(34) & reg_key_location & Chr(34) & " /v " & reg_key_name, _
        "", _
        "runas", _
        1
End Sub

All that is returned from this routine is:

ERROR:

The system was unable to find the specified registry key or value.

However the registry key most definitely exists. Refer to screenshot below. Additionally the command prompt should also be running with admin rights according to my code above.

Registry Key Screenshot:

Furthermore executing the command...

REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel" /v TypeGuessRows

Directly in command prompt works without any Administrator Rights.

REG EDIT Manually in CMD:

So I'm lost on how to get this function working correctly and any help on this issue would be much appreciated!

**** UPDATE ****

Ok so i've implemented the code suggested by Dinotom in the first answer. See extract of code below.

Sub Read_Registry()

Dim entryArray() As Variant
Dim valueArray() As Variant
Dim reg_key_location As String
Dim x As Integer

reg_key_location = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel"
Call EnumerateRegEntries(reg_key_location, entryArray, valueArray)

For x = 0 To UBound(entryArray)
    'Do something here
Next x

End Sub

Public Sub EnumerateRegEntries(keyPath As String, arrEntryNames As Variant, arrValueTypes As Variant)

Dim registryObject As Object
Dim rootDirectory As String

 rootDirectory = "."
Set registryObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
 rootDirectory & "\root\default:StdRegProv")

registryObject.EnumValues HKEY_LOCAL_MACHINE, keyPath, arrEntryNames, arrValueTypes

End Sub

However the following error is returned on the For x = 0 ... line...

ERROR:

Run-time error '9' Subscript out of range.

It doesn't look like the arrays are being populated with the registry data as suggested below. Any more ideas?

Answer 1

Do you have to use Shell?

This will enumerate your registry entries, manipulate as you need. Set up empty arrays to pass as the parameters, and the keypath is the local file path to your registry to enumerate. the sub will fill the arrays.

Dim entryArray() As Variant, valueArray() As Variant
Call EnumerateRegEntries("pathtokey",entryArray, valueArray)

The sub below will run and entryArray and valueArray will be populated. Then you can iterate over the arrays

For x = 0 to UBound(yourarrayhere)
    'Do something here
Next x

Enumerate method:

Public Sub EnumerateRegEntries(keyPath As String, arrEntryNames As Variant, arrValueTypes As Variant)

    Dim registryObject As Object
    Dim rootDirectory As String

     rootDirectory = "."
    Set registryObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
     rootDirectory & "\root\default:StdRegProv")

    registryObject.EnumValues HKEY_LOCAL_MACHINE, keyPath, arrEntryNames, arrValueTypes

End Sub

if you are unable to alter or use this sub, then look here Chip Pearsons registry page

or, if you have some requirement to use Shell, then look here for how to run as Admin run shell as admin