Using API calls in VBA to query and write to the registry

[brettdj]

I've written an addin that for some reason does not load up the next time I start it even though it is selected under Tools - Addins. I've tracked it down to a Registry issue.

The addin value is written as HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options Name 'Open7' Data '/F "C:\Documents and Settings\Dave\My Documents\BEE 200503.xla"'.

If I delete the /F prefix the addin always works.

Two questions:
(1) Why does the addin have a /F prefix
(2) I know I can use Windows API calls to query and write to the registry. But after searching the web I haven't found much help outside the standard GetSetting procedure or .ini profiles. Can someone please either point me to a good source of info on writing the API calls that I need or give me a hand with the functions.

The addin uses modeless forms so I need to detect whether the version(s) of excel on the PC is Excel 2000 or above. I plan on using the registry API calls for this as well.

Cheers

Dave

 

[Ivan F Moala]

Re: Using API calls in VBA to query and write to the registr

I've written an addin that for some reason does not load up the next time I start it even though it is selected under Tools - Addins. I've tracked it down to a Registry issue.

The addin value is written as HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options Name 'Open7' Data '/F "C:\Documents and Settings\Dave\My Documents\BEE 200503.xla"'.

If I delete the /F prefix the addin always works.

Two questions:
(1) Why does the addin have a /F prefix
(2) I know I can use Windows API calls to query and write to the registry. But after searching the web I haven't found much help outside the standard GetSetting procedure or .ini profiles. Can someone please either point me to a good source of info on writing the API calls that I need or give me a hand with the functions.

The addin uses modeless forms so I need to detect whether the version(s) of excel on the PC is Excel 2000 or above. I plan on using the registry API calls for this as well.

Cheers

Dave

Your Addins shoule really be located in

HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Add-in Manager ??

1) That prefix is usually used for loading custom functions into the Insert Function list. What is your Addin doing ??
2) Adding/Changing the registry can be done Via Scripting or API. It's not something that can be done lightly....what exactly are you trying to change

To test for Versions then use the Comple Constants

Something like

<font face=Courier New>Sub OpenUserform()
    #If VBA6 <SPAN style="color:darkblue">Then</SPAN>
        MyUserform.Show vbModeless
    #Else
        MyUserform.Show
    #End <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

 

[Andrew Poulsom]

Re: Using API calls in VBA to query and write to the registr

Don't know about the /F setting, but here are the API calls for reading and writing to the Registry (courtesy John Walkenbach):

' 32-bit declarations
Private Declare Function RegOpenKeyA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sSubKey As String, _
    ByRef hkeyResult As Long) As Long

Private Declare Function RegCloseKey Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long) As Long

Private Declare Function RegSetValueExA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sValueName As String, _
    ByVal dwReserved As Long, ByVal dwType As Long, _
    ByVal sValue As String, ByVal dwSize As Long) As Long

Private Declare Function RegCreateKeyA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sSubKey As String, _
    ByRef hkeyResult As Long) As Long

Private Declare Function RegQueryValueExA Lib "ADVAPI32.DLL" _
    (ByVal hKey As Long, ByVal sValueName As String, _
    ByVal dwReserved As Long, ByRef lValueType As Long, _
    ByVal sValue As String, ByRef lResultLen As Long) As Long

Private Function GetRegistry(Key, Path, ByVal ValueName As String)
'  Reads a value from the Windows Registry

    Dim hKey As Long
    Dim lValueType As Long
    Dim sResult As String
    Dim lResultLen As Long
    Dim ResultLen As Long
    Dim x, TheKey As Long

    TheKey = -99
    Select Case UCase(Key)
        Case "HKEY_CLASSES_ROOT": TheKey = &H80000000
        Case "HKEY_CURRENT_USER": TheKey = &H80000001
        Case "HKEY_LOCAL_MACHINE": TheKey = &H80000002
        Case "HKEY_USERS": TheKey = &H80000003
        Case "HKEY_CURRENT_CONFIG": TheKey = &H80000004
        Case "HKEY_DYN_DATA": TheKey = &H80000005
    End Select
    
'   Exit if key is not found
    If TheKey = -99 Then
        GetRegistry = "Not Found"
        Exit Function
    End If

    If RegOpenKeyA(TheKey, Path, hKey) <> 0 Then _
        x = RegCreateKeyA(TheKey, Path, hKey)
    
    sResult = Space(100)
    lResultLen = 100
    
    x = RegQueryValueExA(hKey, ValueName, 0, lValueType, _
    sResult, lResultLen)
        
    Select Case x
        Case 0: GetRegistry = Left(sResult, lResultLen - 1)
        Case Else: GetRegistry = "Not Found"
    End Select
    
    RegCloseKey hKey
End Function

Private Function WriteRegistry(ByVal Key As String, _
    ByVal Path As String, ByVal entry As String, _
    ByVal value As String)
    
    Dim hKey As Long
    Dim lValueType As Long
    Dim sResult As String
    Dim lResultLen As Long
   
    TheKey = -99
    Select Case UCase(Key)
        Case "HKEY_CLASSES_ROOT": TheKey = &H80000000
        Case "HKEY_CURRENT_USER": TheKey = &H80000001
        Case "HKEY_LOCAL_MACHINE": TheKey = &H80000002
        Case "HKEY_USERS": TheKey = &H80000003
        Case "HKEY_CURRENT_CONFIG": TheKey = &H80000004
        Case "HKEY_DYN_DATA": TheKey = &H80000005
    End Select
    
'   Exit if key is not found
    If TheKey = -99 Then
        WriteRegistry = False
        Exit Function
    End If

'   Make sure  key exists
    If RegOpenKeyA(TheKey, Path, hKey) <> 0 Then
        x = RegCreateKeyA(TheKey, Path, hKey)
    End If

    x = RegSetValueExA(hKey, entry, 0, 1, value, Len(value) + 1)
    If x = 0 Then WriteRegistry = True Else WriteRegistry = False
End Function

By the way you can use Val(Application.Version) to get the version number of Excel.

 

[brettdj]

Re: Using API calls in VBA to query and write to the registr

Thanks for the reply and code Andrew. I didn't find that code earlier when I browsed Walkenbach's site.

Thanks to you for your suggestions as well Ivan.

I intend to use the API calls to either install the addin without the /F prefix
or remove the /F prefix

Its an interesting point re the addin location, 5 of my 7 addins are located in the Options path, the remaining two are in the Add-in-Manager.

Regards

Dave

 

[Andrew Poulsom]

Re: Using API calls in VBA to query and write to the registr

I got the code from the CD-ROM that came with John Walkenbach's book - it's not on his site.

In the book he says:

1. The Add-in Manager section of the registry lists add-ins that appear in the list box when you choose the Tools|Add-Ins command. Add-ins that are included with Excel do not appear in this list.

2. The Options section includes, inter alia, paths to files that are opened automatically when Excel starts (such as add-ins checked under Tools|Add-Ins).

So the 2 sections serve different purposes.

I think the Installed property of the AddIns object writes to the Options section.

 

[brettdj]

Re: Using API calls in VBA to query and write to the registr

You are correct Andrew, I just ran a test with my addins.

I deselected them all, exited Excel & ran RegEdit. All the addins appeared in the Add-in Manager key.

I then reran Excel and installed two of the addins, shut Excel down and ran RegEdit again. The two addins that I installed had been shifted from the Add-in Manager Key to the Options key.

Last question: Is there a way that I loop through all the values in the Options key with GetRegistry to look for the "/F" prefix and remove it?

Cheers

Dave

 

[brettdj]

Re: Using API calls in VBA to query and write to the registr

I just found this link which will let me loop through the Open values under the Registry OPTIONS key.

http://cwashington.netreach.net/depo/view.asp?Index=704&ScriptType=vbscript

Cheers

Dave

 

[Andrew Poulsom]

Re: Using API calls in VBA to query and write to the registr

The path is Openx where x is a number appended after the first Open. So it is Open, Open1, Open2 etc. I suppose you could set up a loop and check the hKey entry, existing if the path isn't found.

But it may be easier to establish what is creating the /f setting. According to this:

http://msdn.microsoft.com/library/en-us/office97/html/SFA96.asp

Option Switches for the OPEN Entry

You can use the /r and /f option switches, alone or together, to modify the behavior of the OPEN entry. The /r option switch opens the file as read-only. The /f (fast load) option switch places template documents in the New dialog box (accessed from the File menu). When used to open a macro sheet containing custom functions, the /f option switch places those functions in the Function Wizard dialog box. When you use one of the custom functions in a specified document, the macro sheet containing the function is opened automatically.

So which is it? Template or macro sheet?

 

[brettdj]

Re: Using API calls in VBA to query and write to the registr

The addin colour formats cell depending on whether they are constants, formulas, derived from other worksheets or derived from other workbooks.

There are are number of UDF's in the addin.

I've checked through the addin and there is a name __DemandLoad as referred to by the link you posted. I've no idea how it originated.

Thanks Andrew

Cheers

Dave