Read Registry Keys from within VBA using Windows Shell
<p>I have been searching around on the internet and am having problems finding a solution to this issue.</p>

<p>Basically I am trying to execute a registry query with administrator privileges using Shell.Application from within VBA to read the value of <code>TypeGuessRows</code> (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:</p>

<pre><code>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 " &amp; Chr(34) &amp; reg_key_location &amp; Chr(34) &amp; " /v " &amp; reg_key_name, _
        "", _
        "runas", _
        1
End Sub
</code></pre>

<p>All that is returned from this routine is:</p>

<p>ERROR: </p>

<blockquote>
  <p>The system was unable to find the specified registry key or value.</p>
</blockquote>

<p>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.</p>

<p>Registry Key Screenshot:
<a href="https://i.stack.imgur.com/fFGKJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fFGKJ.png" alt="enter image description here"></a></p>

<p>Furthermore executing the command...</p>

<pre><code>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
</code></pre>

<p>Directly in command prompt works without any Administrator Rights.</p>

<p>REG EDIT Manually in CMD:
<a href="https://i.stack.imgur.com/3jcbF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3jcbF.png" alt="enter image description here"></a></p>

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

<p>**** UPDATE ****</p>

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

<pre><code>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}!\\" &amp; _
 rootDirectory &amp; "\root\default:StdRegProv")

registryObject.EnumValues HKEY_LOCAL_MACHINE, keyPath, arrEntryNames, arrValueTypes

End Sub
</code></pre>

<p>However the following error is returned on the For x = 0 ... line...</p>

<p>ERROR: </p>

<blockquote>
  <p>Run-time error '9' Subscript out of range.</p>
</blockquote>

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