Reading Windows Registry with WScript or Win API from VBA app

I'm trying to do something incredibly simple, read one string value from the Windows Registry and am failing miserably.

I am trying to find the installation path of the ImageMagick software suite which is installed on my test PC.

I can read and write to the corresponding registry setting in regedit.exe here:

HKLM\SOFTWARE\ImageMagick\Current\BinPath

So, I decided to use the simplest method in VBA to read a registry value, WScript. Here is the code:

Function GetImageMagickInstallPath() As String
  Dim oWScript As Object
  Set oWScript = CreateObject("WScript.Shell")
  GetImageMagickInstallPath = oWScript.RegRead("HKLM\SOFTWARE\ImageMagick\Current\BinPath")
End Function

Open in new window


But this fails with the following error message:

Run-time error ‘-2147024894 (80070002)’:
Invalid root in registry key
"HKLM\SOFTWARE\ImageMagick\Current\BinPath”.

This error appears to occur when the key path is misspelt but it isn't.

I then went on to try the more complex WinAPI method as documented here https://support.microsoft.com/en-us/kb/145679 in VBA and keep getting returned the error ERROR_BADKEY

With both methods, I can read settings in the HKCU hive, for example under HKEY_CURRENT_USER\Software\VB and VBA Program Settings

Why can't I read from the HKLM hive?
LVL 14
Jamie GarrochPowerPoint Consultant & DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
I'm willing to bet that you're on a 64bit system, and your Office is 32bit. In this case, your 32bit application sees "HKLM\Software\Wow6432Node" as "HKLM\Software".
There seem to be ways to enable access to the 64bit registry from 32bit applications in VBA (try http://www.vbforums.com/showthread.php?705261-access-64-bit-registry-from-32-bit-app), or you can use the brute-force approach and parse the output of reg.exe, which has an option to allow access to the 64bit registry.
Here's a sample which queries for the 64bit installation path of 7-zip from a 32bit VBA.
You'd probably need "reg.exe query HKLM\SOFTWARE\ImageMagick\Current -v binPath /reg:64"
Set objShell = CreateObject("WScript.Shell")
Set objShellExec = objShell.Exec("reg.exe query HKLM\Software\7-Zip -v Path /reg:64")
Set objStdOut = objShellExec.StdOut
strPath = ""
While Not objStdOut.AtEndOfStream
    strLine = Trim(objStdOut.ReadLine)
    If InStr(strLine, "REG_SZ") Then
        Do While InStr(1, strLine, "  ")
            strLine = Replace(strLine, "  ", " ")
        Loop
        arrLine = Split(strLine, " ", 3)
        strPath = arrLine(2)
    End If
Wend
If strPath = "" Then
    MsgBox ("Not found!")
Else
    MsgBox ("[" & strPath & "]")
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jamie GarrochPowerPoint Consultant & DeveloperAuthor Commented:
Wow. Thank you so much oBdA! Such a lot learned from a single answer! I am a novice when it comes to understanding how the registry changed to support 64 bit apps and the repercussions for 32 bit apps.

Yes, you win the bet that my current test environment is MSO 32 bit +  Win x64 and of course ImageMagick installed as 64 bit in "C:\Program Files". So I think I now understand that if a 32bit app probes HKLM\Software that it is redirected to HKLM\Software\Wow6432Node. I never knew that!

The brute force code sample works as expected with the addition of the declarations below. One small question, how can I hide the shell window that flashes up briefly? I'll give the linked API-based solution a go too.

  Dim objShell As Object
  Dim objShellExec As Object
  Dim objStdOut As Object
  Dim strPath As String
  Dim strLine As String
  Dim arrLine() As String

Open in new window


I will now have to test on MSO 64 bit too as the VBA app has to be compatible on both versions.
0
Jamie GarrochPowerPoint Consultant & DeveloperAuthor Commented:
Several nice snippets of knowledge imparted plus two possible solutions. Excellent!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.