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?

That Sub works for me on every reg key ive tested, so you either have a bad key path or there is a permissioning issue to that registry key. — dinotom, Sep 20 '16 at 16:28
this sub doesn't work for me for any of the key paths i've tested. I've tried ... HKEY_LOCAL_MACHINE\SOFTWARE; HKEY_LOCAL_MACHINE\HARDWARE; HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Common as a few examples (in addition to the same keys omitting the HKLM prefix). So its definitely not a bad file path issue. Permissioning maybe? ... what OS are you running? I'm using Windows 10. Also any other ideas of what the issue may be? — Josh, Sep 22 '16 at 00:36

score 4 · Answer 1 · edited May 08 '18 at 21:49

4

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

edited May 08 '18 at 21:49

Community

1
1

answered Sep 13 '16 at 07:37

dinotom

4,990
16
71
139

Awesome answers thanks dinotom! No requirement to use shell. It was just the only options I could come up with at the time to read the registry from VBA. How do I pass an empty variant array to this function? Then output the results of these arrays? Can you please provide an example? Thanks again for the help! – Josh Sep 15 '16 at 01:01
Alright so i've input the code into VBA as suggest above. But when it reaches the for x = 0 to Ubound(entryArray) section of the code all that is returned is "Run Time Error 9, Subscript Out of Range" any ideas whats going on? – Josh Sep 18 '16 at 02:22
put a breakpoint on the For x =... line and run the code, what is in the array? you should post all of the code you are running in the question since I have no clue if you are implementing it correctly. – dinotom Sep 18 '16 at 20:16
It doesnt appear that there is anything in the array. Also i've updated my question with the sample code I was using. Let me know if you what you think. – Josh Sep 19 '16 at 23:49

GibaHC · Answer 2 · 2019-05-15T18:35:22.110

The path is wrong.

Set the path like this:

reg_key_location = "SOFTWARE\Microsoft\Office\15.0\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel"

The HKEY_LOCAL_MACHINE is placed when calling the object:

registryObject.EnumValues HKEY_LOCAL_MACHINE, keyPath, arrEntryNames, arrValueTypes

EDIT: Also remind that if you are running windows 64 bits and office 32 bits, the stdregprov only reads inside Wow6432Node.

Read Registry Keys from within VBA using Windows Shell

2 Answers2

Linked