Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
I seem to have this really weird problem and I am not sure how to fix. Let me explain via an example: 1. Open a new workbook. 2. Open VBE and create a new module 3. In the module simply type a comment: This is file 1 4. Save As Add-In c:\test.xla 5. In the module edit the line to read: This is file 2 6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but different directory 7. In any module: With AddIns.Add(Filename:="c:\test.xla") .Installed = True End With Edit the module and confirm the comment : This is file 1 With AddIns.Add(Filename:="c:\test.xla") .Installed = False End With With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = True End With 8. Edit the module and confirm the comment IT IS STILL: This is file 1 i.e. the original file! 9. Close the file and exit from Excel, then double click on "c:\temp\test.xla" and I get an error message saying the file is already open. 10. Excel Tools Add-Ins and uncheck test and exit excel With AddIns.Add(Filename:=" c:\temp\test.xla") .Installed = True End With 11. Edit the module and confirm the comment IT IS STILL: This is file 1 i.e. the original file! 12. Excel Tools Add-Ins and uncheck test and exit excel 13. From explorer double-click on "c:\temp\test.xla" 14. Edit the module and confirm the comment: This is file 2 In summary, once a file has been opened from a particular location, even if you: With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = False End With Any attempt to load the file from another location using AddIns.Add simply opens the file from the original path. In VBA code how can I update to add the Add-In the new location? -- Trefor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Hi Trefor,
The behaviour you described is predictable, it's the behaviour Addins collection that's weird!. When you uninstalled addin-1 you didn't remove it from the Addins collection. When you tried to addin-2 with the same title (in absence of a title it defaults to name) it simply reinstalled the similarly 'titled' addin that already exists in the addins collection, ie addin-1. Possible solutions - 1 - remove addin-1 from the collection. 1a) Manually, after uninstalling the addin remove it from the current folder. Try & re-check it in Tools Addins. Follow the prompts to allow removal. Note - If the addin is not in one of the default addins locations need to do this in same session as it was uninstalled. Otherwise in next session it won't be visible in the drop down (but it still exists in the registry) 1b) Remove from the registry if it's not in a default location (manually or programmatically). If it's in a default addins location and uninstalled it won't be in the registry, so after uninstalling move it to another folder 2 Give addin-2 a unique title Load it from file wb.title = "new title" wb.save Now you should be able to install addin-2 Also note if your addins had same titles but different names located in different folders the same scenario you described would occur. Regards, Peter T PS, you didn't need to go to the trouble of modifying code in respective addins to demonstrate. Select the file in Project Explorer (ctrl-R) and in the intermediate window ?thisworkbook.fullname hit enter "Trefor" wrote in message ... I seem to have this really weird problem and I am not sure how to fix. Let me explain via an example: 1. Open a new workbook. 2. Open VBE and create a new module 3. In the module simply type a comment: ' This is file 1 4. Save As Add-In c:\test.xla 5. In the module edit the line to read: ' This is file 2 6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but different directory 7. In any module: With AddIns.Add(Filename:="c:\test.xla") .Installed = True End With Edit the module and confirm the comment : ' This is file 1 With AddIns.Add(Filename:="c:\test.xla") .Installed = False End With With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = True End With 8. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 9. Close the file and exit from Excel, then double click on "c:\temp\test.xla" and I get an error message saying the file is already open. 10. Excel Tools Add-Ins and uncheck test and exit excel With AddIns.Add(Filename:=" c:\temp\test.xla") .Installed = True End With 11. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 12. Excel Tools Add-Ins and uncheck test and exit excel 13. From explorer double-click on "c:\temp\test.xla" 14. Edit the module and confirm the comment: ' This is file 2 In summary, once a file has been opened from a particular location, even if you: With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = False End With Any attempt to load the file from another location using AddIns.Add simply opens the file from the original path. In VBA code how can I "update" to add the Add-In the new location? -- Trefor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Trefor, Excel records add-in information in the registry and there is a
design flaw (a flaw, from this perspective, anyway) that doesn't update the key if the add-in is of the same name. The code must reside outside of Excel / VBA because anything you do to the registry will be overwritten when you exit XL (XL reads the registry when it opens and writes to the registry when it closes). I have handled this by building a small deployment tool, originally in VB6. The code is somewhat cumbersome but I'd be happy to share if needed. Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? The essence is that is wipes out any reg strings in the following keys that contain the add-in name. You can then re-add properly once the registry is wiped. Keys of interest: HKCU\Software\Microsoft\Office\xx.x\Excel\Options\ HKCU\Software\Microsoft\Office\xx.x\Excel\Add-in Manager\ where xx.x is the following 8.0, 9.0, 10.0, 11.0, 12.0 Regards, Bill "Trefor" wrote: I seem to have this really weird problem and I am not sure how to fix. Let me explain via an example: 1. Open a new workbook. 2. Open VBE and create a new module 3. In the module simply type a comment: This is file 1 4. Save As Add-In c:\test.xla 5. In the module edit the line to read: This is file 2 6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but different directory 7. In any module: With AddIns.Add(Filename:="c:\test.xla") .Installed = True End With Edit the module and confirm the comment : This is file 1 With AddIns.Add(Filename:="c:\test.xla") .Installed = False End With With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = True End With 8. Edit the module and confirm the comment IT IS STILL: This is file 1 i.e. the original file! 9. Close the file and exit from Excel, then double click on "c:\temp\test.xla" and I get an error message saying the file is already open. 10. Excel Tools Add-Ins and uncheck test and exit excel With AddIns.Add(Filename:=" c:\temp\test.xla") .Installed = True End With 11. Edit the module and confirm the comment IT IS STILL: This is file 1 i.e. the original file! 12. Excel Tools Add-Ins and uncheck test and exit excel 13. From explorer double-click on "c:\temp\test.xla" 14. Edit the module and confirm the comment: This is file 2 In summary, once a file has been opened from a particular location, even if you: With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = False End With Any attempt to load the file from another location using AddIns.Add simply opens the file from the original path. In VBA code how can I update to add the Add-In the new location? -- Trefor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Unfortunately, I am new to the discussion groups, so I don't know
the best method for sharing code. Any suggestions? Why not post it here. If long it's helpful if line-wrapping can be removed with continuations, though not necessary (check end of line col position in the VBE). You might also be interested to see KeepItCool's method for removing addins from the registry. Also addins in default addin folders should also be moved elsewhere, or they will persist in the collection even though not installed and not in the registry. http://tinyurl.com/euct6 Regards, Peter T "Bill Pfister" wrote in message ... Trefor, Excel records add-in information in the registry and there is a design flaw (a flaw, from this perspective, anyway) that doesn't update the key if the add-in is of the same name. The code must reside outside of Excel / VBA because anything you do to the registry will be overwritten when you exit XL (XL reads the registry when it opens and writes to the registry when it closes). I have handled this by building a small deployment tool, originally in VB6. The code is somewhat cumbersome but I'd be happy to share if needed. Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? The essence is that is wipes out any reg strings in the following keys that contain the add-in name. You can then re-add properly once the registry is wiped. Keys of interest: HKCU\Software\Microsoft\Office\xx.x\Excel\Options\ HKCU\Software\Microsoft\Office\xx.x\Excel\Add-in Manager\ where xx.x is the following 8.0, 9.0, 10.0, 11.0, 12.0 Regards, Bill "Trefor" wrote: I seem to have this really weird problem and I am not sure how to fix. Let me explain via an example: 1. Open a new workbook. 2. Open VBE and create a new module 3. In the module simply type a comment: ' This is file 1 4. Save As Add-In c:\test.xla 5. In the module edit the line to read: ' This is file 2 6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but different directory 7. In any module: With AddIns.Add(Filename:="c:\test.xla") .Installed = True End With Edit the module and confirm the comment : ' This is file 1 With AddIns.Add(Filename:="c:\test.xla") .Installed = False End With With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = True End With 8. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 9. Close the file and exit from Excel, then double click on "c:\temp\test.xla" and I get an error message saying the file is already open. 10. Excel Tools Add-Ins and uncheck test and exit excel With AddIns.Add(Filename:=" c:\temp\test.xla") .Installed = True End With 11. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 12. Excel Tools Add-Ins and uncheck test and exit excel 13. From explorer double-click on "c:\temp\test.xla" 14. Edit the module and confirm the comment: ' This is file 2 In summary, once a file has been opened from a particular location, even if you: With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = False End With Any attempt to load the file from another location using AddIns.Add simply opens the file from the original path. In VBA code how can I "update" to add the Add-In the new location? -- Trefor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
(check end of line col position in the VBE).
less than say 78 should be OK "Peter T" <peter_t@discussions wrote in message ... Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? Why not post it here. If long it's helpful if line-wrapping can be removed with continuations, though not necessary (check end of line col position in the VBE). You might also be interested to see KeepItCool's method for removing addins from the registry. Also addins in default addin folders should also be moved elsewhere, or they will persist in the collection even though not installed and not in the registry. http://tinyurl.com/euct6 Regards, Peter T <snip |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
As a personal preference, I limit the use of line continuations. Let's see
how this looks. I'm assuming you can just copy paste from here without unwanted characters being added... Here's the example (which belongs in a separate module): Option Explicit ' Registry value type definitions Private Const REG_NONE As Long = 0 Private Const REG_SZ As Long = 1 Private Const REG_EXPAND_SZ As Long = 2 Private Const REG_BINARY As Long = 3 Private Const REG_DWORD As Long = 4 Private Const REG_LINK As Long = 6 Private Const REG_MULTI_SZ As Long = 7 Private Const REG_RESOURCE_LIST As Long = 8 Public Const REG_OPTION_NON_VOLATILE = 0 Private Const KEY_ALL_ACCESS As Long = &H3F ' Registry section definitions Public Const HKEY_CURRENT_USER As Long = &H80000001 Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Options\" Private Const c_strKey_8b As String = "Software\Microsoft\Office\8.0\Excel\Add-in Manager\" Private Const c_strKey_9a As String = "Software\Microsoft\Office\9.0\Excel\Options\" Private Const c_strKey_9b As String = "Software\Microsoft\Office\9.0\Excel\Add-in Manager\" Private Const c_strKey_10a As String = "Software\Microsoft\Office\10.0\Excel\Options\ " Private Const c_strKey_10b As String = "Software\Microsoft\Office\10.0\Excel\Add-in Manager\" Private Const c_strKey_11a As String = "Software\Microsoft\Office\11.0\Excel\Options\ " Private Const c_strKey_11b As String = "Software\Microsoft\Office\11.0\Excel\Add-in Manager\" ' Registry API functions Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, phkResult As Long, lpdwDisposition As Long) As Long Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long Private Declare Function RegSetValueExString Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As String, ByVal cbData As Long) As Long Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long) As Long Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long Private Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long Private Declare Function RegDeleteValue Lib "advapi32.dll" Alias "RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String) As Long ' Delete all registry keys that contain the named add-in Public Function GetAllValues(strAddInName As String) As Single Dim varTemp As Variant Dim strKey As String Dim strValue As String Dim strValueData As String Dim strSection As String Dim strDelVal As String Dim i As Long Dim j As Long Dim sngErrorCode As Single On Error GoTo ErrHandler For i = 1 To 8 Select Case i Case Is = 1: strSection = c_strKey_8a Case Is = 2: strSection = c_strKey_8b Case Is = 3: strSection = c_strKey_9a Case Is = 4: strSection = c_strKey_9b Case Is = 5: strSection = c_strKey_10a Case Is = 6: strSection = c_strKey_10b Case Is = 7: strSection = c_strKey_11a Case Is = 8: strSection = c_strKey_11b End Select j = 0 sngErrorCode = 10 ' Search through specified registry key 'Searches through the add-in key and locates the Map add-in if it exists, the value is then deleted Do While Not (strValue = "Not Found") varTemp = ReadRegistryGetAll(HKEY_CURRENT_USER, strSection, j) j = j + 1 sngErrorCode = 20 ' Find which values are strings If varTemp(0) = REG_SZ Then strValue = varTemp(1) strValueData = varTemp(2) sngErrorCode = 30 ' Check if add-in is in the specified strings If (InStr(strValue, strAddInName) 0) Or (InStr(strValueData, strAddInName) 0) Then strDelVal = DeleteValue(HKEY_CURRENT_USER, strSection, strValue) End If End If sngErrorCode = 40 ' Check if loop needs to be ended If varTemp(0) = 0 Then Exit Do Loop Next i sngErrorCode = 0 GetAllValues = sngErrorCode Exit Function ErrHandler: GetAllValues = sngErrorCode End Function ' Retrieves all the values from anywhere in the Registry under a given subkey, currently only returns string and double word values Public Function ReadRegistryGetAll(ByVal lngGroup As Long, ByVal strSection As String, lngIndex As Long) As Variant Dim lngResult As Long Dim lngKeyValue As Long Dim lngDataTypeValue As Long Dim lngValueLength As Long Dim lngValueNameLength As Long Dim strValueName As String Dim strValue As String Dim dblTemp As Double On Error Resume Next lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue) strValue = Space$(2048) strValueName = Space$(2048) lngValueLength = Len(strValue) lngValueNameLength = Len(strValueName) lngResult = RegEnumValue(lngKeyValue, lngIndex, strValueName, lngValueNameLength, 0&, lngDataTypeValue, strValue, lngValueLength) If (lngResult = 0) And (Err.Number = 0) Then If lngDataTypeValue = REG_DWORD Then dblTemp = Asc(Mid$(strValue, 1, 1)) + &H100& * Asc(Mid$(strValue, 2, 1)) + &H10000 * Asc(Mid$(strValue, 3, 1)) + &H1000000 * CDbl(Asc(Mid$(strValue, 4, 1))) strValue = Format$(dblTemp, "000") End If strValue = Left$(strValue, lngValueLength - 1) strValueName = Left$(strValueName, lngValueNameLength) Else strValue = "Not Found" End If lngResult = RegCloseKey(lngKeyValue) ReadRegistryGetAll = Array(lngDataTypeValue, strValueName, strValue) End Function ' Deletes a specified key (and all its subkeys and values if on Win95) from the registry Public Function DeleteValue(ByVal lngGroup As Long, ByVal strSection As String, ByVal strKey As String) As String Dim lngResult As Long Dim lngKeyValue As Long On Error Resume Next lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue) lngResult = RegDeleteValue(lngKeyValue, strKey) lngResult = RegCloseKey(lngKeyValue) End Function ' Set the data field of a value Public Function SetKeyValue(lngPredefinedKey As Long, strKeyName As String, strValueName As String, varValueSetting As Variant, lngValueType As Long) Dim lngRetVal As Long ' result of the SetValueEx function Dim hKey As Long ' handle of open key 'open the specified key lngRetVal = RegCreateKeyEx(lngPredefinedKey, strKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, 0&, hKey, lngRetVal) lngRetVal = SetValueEx(hKey, strValueName, lngValueType, varValueSetting) Call RegCloseKey(hKey) End Function ' Set the data field of a value Public Function SetValueEx(ByVal hKey As Long, strValueName As String, lngType As Long, varValue As Variant) As Long Dim lngValue As Long Dim strValue As String Select Case lngType Case REG_SZ strValue = varValue SetValueEx = RegSetValueExString(hKey, strValueName, 0&, lngType, strValue, Len(strValue)) Case REG_DWORD lngValue = varValue SetValueEx = RegSetValueExLong(hKey, strValueName, 0&, lngType, lngValue, 4) End Select End Function "Peter T" wrote: Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? Why not post it here. If long it's helpful if line-wrapping can be removed with continuations, though not necessary (check end of line col position in the VBE). You might also be interested to see KeepItCool's method for removing addins from the registry. Also addins in default addin folders should also be moved elsewhere, or they will persist in the collection even though not installed and not in the registry. http://tinyurl.com/euct6 Regards, Peter T "Bill Pfister" wrote in message ... Trefor, Excel records add-in information in the registry and there is a design flaw (a flaw, from this perspective, anyway) that doesn't update the key if the add-in is of the same name. The code must reside outside of Excel / VBA because anything you do to the registry will be overwritten when you exit XL (XL reads the registry when it opens and writes to the registry when it closes). I have handled this by building a small deployment tool, originally in VB6. The code is somewhat cumbersome but I'd be happy to share if needed. Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? The essence is that is wipes out any reg strings in the following keys that contain the add-in name. You can then re-add properly once the registry is wiped. Keys of interest: HKCU\Software\Microsoft\Office\xx.x\Excel\Options\ HKCU\Software\Microsoft\Office\xx.x\Excel\Add-in Manager\ where xx.x is the following 8.0, 9.0, 10.0, 11.0, 12.0 Regards, Bill "Trefor" wrote: I seem to have this really weird problem and I am not sure how to fix. Let me explain via an example: 1. Open a new workbook. 2. Open VBE and create a new module 3. In the module simply type a comment: ' This is file 1 4. Save As Add-In c:\test.xla 5. In the module edit the line to read: ' This is file 2 6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but different directory 7. In any module: With AddIns.Add(Filename:="c:\test.xla") .Installed = True End With Edit the module and confirm the comment : ' This is file 1 With AddIns.Add(Filename:="c:\test.xla") .Installed = False End With With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = True End With 8. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 9. Close the file and exit from Excel, then double click on "c:\temp\test.xla" and I get an error message saying the file is already open. 10. Excel Tools Add-Ins and uncheck test and exit excel With AddIns.Add(Filename:=" c:\temp\test.xla") .Installed = True End With 11. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 12. Excel Tools Add-Ins and uncheck test and exit excel 13. From explorer double-click on "c:\temp\test.xla" 14. Edit the module and confirm the comment: ' This is file 2 In summary, once a file has been opened from a particular location, even if you: With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = False End With Any attempt to load the file from another location using AddIns.Add simply opens the file from the original path. In VBA code how can I "update" to add the Add-In the new location? -- Trefor |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Thanks Bill for sharing this. I agree with your use of continuations, hope
everyone can un-wrap OK Indeed this removes the entries, comments - This key - Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Options\" should be changed to - Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Microsoft Excel\" Concerning removing entries from *\Excel\Options\ (& *Excel\Microsoft Excel\ for xl97) - The installed addins are in this section, listed under Name : Data. The Name is OPEN, OPEN1, OPEN2 etc, data is the addin's fullname. I don't know why but sometimes Excel will automatically increment down any OPENx to the missing OPENx, eg delete OPEN1 then next time Excel opens OPEN2 will be renamed to OPEN1. But sometimes that doesn't happen, perhaps could include KeepItCool's example to reorder the OPEN's If deleting an installed addin while a given version of Excel is open, it will be re-written when Excel closes. Thanks again for posting, Peter T "Bill Pfister" wrote in message ... As a personal preference, I limit the use of line continuations. Let's see how this looks. I'm assuming you can just copy paste from here without unwanted characters being added... Here's the example (which belongs in a separate module): Option Explicit ' Registry value type definitions Private Const REG_NONE As Long = 0 Private Const REG_SZ As Long = 1 Private Const REG_EXPAND_SZ As Long = 2 Private Const REG_BINARY As Long = 3 Private Const REG_DWORD As Long = 4 Private Const REG_LINK As Long = 6 Private Const REG_MULTI_SZ As Long = 7 Private Const REG_RESOURCE_LIST As Long = 8 Public Const REG_OPTION_NON_VOLATILE = 0 Private Const KEY_ALL_ACCESS As Long = &H3F ' Registry section definitions Public Const HKEY_CURRENT_USER As Long = &H80000001 Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Options\" Private Const c_strKey_8b As String = "Software\Microsoft\Office\8.0\Excel\Add-in Manager\" Private Const c_strKey_9a As String = "Software\Microsoft\Office\9.0\Excel\Options\" Private Const c_strKey_9b As String = "Software\Microsoft\Office\9.0\Excel\Add-in Manager\" Private Const c_strKey_10a As String = "Software\Microsoft\Office\10.0\Excel\Options\ " Private Const c_strKey_10b As String = "Software\Microsoft\Office\10.0\Excel\Add-in Manager\" Private Const c_strKey_11a As String = "Software\Microsoft\Office\11.0\Excel\Options\ " Private Const c_strKey_11b As String = "Software\Microsoft\Office\11.0\Excel\Add-in Manager\" ' Registry API functions Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, phkResult As Long, lpdwDisposition As Long) As Long Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long Private Declare Function RegSetValueExString Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As String, ByVal cbData As Long) As Long Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long) As Long Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long Private Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long Private Declare Function RegDeleteValue Lib "advapi32.dll" Alias "RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String) As Long ' Delete all registry keys that contain the named add-in Public Function GetAllValues(strAddInName As String) As Single Dim varTemp As Variant Dim strKey As String Dim strValue As String Dim strValueData As String Dim strSection As String Dim strDelVal As String Dim i As Long Dim j As Long Dim sngErrorCode As Single On Error GoTo ErrHandler For i = 1 To 8 Select Case i Case Is = 1: strSection = c_strKey_8a Case Is = 2: strSection = c_strKey_8b Case Is = 3: strSection = c_strKey_9a Case Is = 4: strSection = c_strKey_9b Case Is = 5: strSection = c_strKey_10a Case Is = 6: strSection = c_strKey_10b Case Is = 7: strSection = c_strKey_11a Case Is = 8: strSection = c_strKey_11b End Select j = 0 sngErrorCode = 10 ' Search through specified registry key 'Searches through the add-in key and locates the Map add-in if it exists, the value is then deleted Do While Not (strValue = "Not Found") varTemp = ReadRegistryGetAll(HKEY_CURRENT_USER, strSection, j) j = j + 1 sngErrorCode = 20 ' Find which values are strings If varTemp(0) = REG_SZ Then strValue = varTemp(1) strValueData = varTemp(2) sngErrorCode = 30 ' Check if add-in is in the specified strings If (InStr(strValue, strAddInName) 0) Or (InStr(strValueData, strAddInName) 0) Then strDelVal = DeleteValue(HKEY_CURRENT_USER, strSection, strValue) End If End If sngErrorCode = 40 ' Check if loop needs to be ended If varTemp(0) = 0 Then Exit Do Loop Next i sngErrorCode = 0 GetAllValues = sngErrorCode Exit Function ErrHandler: GetAllValues = sngErrorCode End Function ' Retrieves all the values from anywhere in the Registry under a given subkey, currently only returns string and double word values Public Function ReadRegistryGetAll(ByVal lngGroup As Long, ByVal strSection As String, lngIndex As Long) As Variant Dim lngResult As Long Dim lngKeyValue As Long Dim lngDataTypeValue As Long Dim lngValueLength As Long Dim lngValueNameLength As Long Dim strValueName As String Dim strValue As String Dim dblTemp As Double On Error Resume Next lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue) strValue = Space$(2048) strValueName = Space$(2048) lngValueLength = Len(strValue) lngValueNameLength = Len(strValueName) lngResult = RegEnumValue(lngKeyValue, lngIndex, strValueName, lngValueNameLength, 0&, lngDataTypeValue, strValue, lngValueLength) If (lngResult = 0) And (Err.Number = 0) Then If lngDataTypeValue = REG_DWORD Then dblTemp = Asc(Mid$(strValue, 1, 1)) + &H100& * Asc(Mid$(strValue, 2, 1)) + &H10000 * Asc(Mid$(strValue, 3, 1)) + &H1000000 * CDbl(Asc(Mid$(strValue, 4, 1))) strValue = Format$(dblTemp, "000") End If strValue = Left$(strValue, lngValueLength - 1) strValueName = Left$(strValueName, lngValueNameLength) Else strValue = "Not Found" End If lngResult = RegCloseKey(lngKeyValue) ReadRegistryGetAll = Array(lngDataTypeValue, strValueName, strValue) End Function ' Deletes a specified key (and all its subkeys and values if on Win95) from the registry Public Function DeleteValue(ByVal lngGroup As Long, ByVal strSection As String, ByVal strKey As String) As String Dim lngResult As Long Dim lngKeyValue As Long On Error Resume Next lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue) lngResult = RegDeleteValue(lngKeyValue, strKey) lngResult = RegCloseKey(lngKeyValue) End Function ' Set the data field of a value Public Function SetKeyValue(lngPredefinedKey As Long, strKeyName As String, strValueName As String, varValueSetting As Variant, lngValueType As Long) Dim lngRetVal As Long ' result of the SetValueEx function Dim hKey As Long ' handle of open key 'open the specified key lngRetVal = RegCreateKeyEx(lngPredefinedKey, strKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, 0&, hKey, lngRetVal) lngRetVal = SetValueEx(hKey, strValueName, lngValueType, varValueSetting) Call RegCloseKey(hKey) End Function ' Set the data field of a value Public Function SetValueEx(ByVal hKey As Long, strValueName As String, lngType As Long, varValue As Variant) As Long Dim lngValue As Long Dim strValue As String Select Case lngType Case REG_SZ strValue = varValue SetValueEx = RegSetValueExString(hKey, strValueName, 0&, lngType, strValue, Len(strValue)) Case REG_DWORD lngValue = varValue SetValueEx = RegSetValueExLong(hKey, strValueName, 0&, lngType, lngValue, 4) End Select End Function "Peter T" wrote: Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? Why not post it here. If long it's helpful if line-wrapping can be removed with continuations, though not necessary (check end of line col position in the VBE). You might also be interested to see KeepItCool's method for removing addins from the registry. Also addins in default addin folders should also be moved elsewhere, or they will persist in the collection even though not installed and not in the registry. http://tinyurl.com/euct6 Regards, Peter T "Bill Pfister" wrote in message ... Trefor, Excel records add-in information in the registry and there is a design flaw (a flaw, from this perspective, anyway) that doesn't update the key if the add-in is of the same name. The code must reside outside of Excel / VBA because anything you do to the registry will be overwritten when you exit XL (XL reads the registry when it opens and writes to the registry when it closes). I have handled this by building a small deployment tool, originally in VB6. The code is somewhat cumbersome but I'd be happy to share if needed. Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? The essence is that is wipes out any reg strings in the following keys that contain the add-in name. You can then re-add properly once the registry is wiped. Keys of interest: HKCU\Software\Microsoft\Office\xx.x\Excel\Options\ HKCU\Software\Microsoft\Office\xx.x\Excel\Add-in Manager\ where xx.x is the following 8.0, 9.0, 10.0, 11.0, 12.0 Regards, Bill "Trefor" wrote: I seem to have this really weird problem and I am not sure how to fix. Let me explain via an example: 1. Open a new workbook. 2. Open VBE and create a new module 3. In the module simply type a comment: ' This is file 1 4. Save As Add-In c:\test.xla 5. In the module edit the line to read: ' This is file 2 6. Save As Add-In c:\temp\test.xla <<__ Must be same filename but different directory 7. In any module: With AddIns.Add(Filename:="c:\test.xla") .Installed = True End With Edit the module and confirm the comment : ' This is file 1 With AddIns.Add(Filename:="c:\test.xla") .Installed = False End With With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = True End With 8. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 9. Close the file and exit from Excel, then double click on "c:\temp\test.xla" and I get an error message saying the file is already open. 10. Excel Tools Add-Ins and uncheck test and exit excel With AddIns.Add(Filename:=" c:\temp\test.xla") .Installed = True End With 11. Edit the module and confirm the comment IT IS STILL: ' This is file 1 i.e. the original file! 12. Excel Tools Add-Ins and uncheck test and exit excel 13. From explorer double-click on "c:\temp\test.xla" 14. Edit the module and confirm the comment: ' This is file 2 In summary, once a file has been opened from a particular location, even if you: With AddIns.Add(Filename:="c:\temp\test.xla") .Installed = False End With Any attempt to load the file from another location using AddIns.Add simply opens the file from the original path. In VBA code how can I "update" to add the Add-In the new location? -- Trefor |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Bill/Peter,
Thankyou for you replies I have not had a chance to go through all the code yet, but I thought I would ask another question first. Am I going about this the right way in the first place? What I have is "Main.xls" which checks the network for a newer "Macro.xla", if one exists it copies the "Macro.xla" to Application.UserLibraryPath and and then tries to use the later version. Should I do this differently? "Macro.xla" has the same name, but I could easily give each updated addin a new name, but then I notice I start collecting heaps of addin in the list and in the directory. I don't want the user to have to do this manually, this all has to work like magic behind the scenes. -- Trefor "Peter T" wrote: Thanks Bill for sharing this. I agree with your use of continuations, hope everyone can un-wrap OK Indeed this removes the entries, comments - This key - Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Options\" should be changed to - Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Microsoft Excel\" Concerning removing entries from *\Excel\Options\ (& *Excel\Microsoft Excel\ for xl97) - The installed addins are in this section, listed under Name : Data. The Name is OPEN, OPEN1, OPEN2 etc, data is the addin's fullname. I don't know why but sometimes Excel will automatically increment down any OPENx to the missing OPENx, eg delete OPEN1 then next time Excel opens OPEN2 will be renamed to OPEN1. But sometimes that doesn't happen, perhaps could include KeepItCool's example to reorder the OPEN's If deleting an installed addin while a given version of Excel is open, it will be re-written when Excel closes. Thanks again for posting, Peter T "Bill Pfister" wrote in message ... As a personal preference, I limit the use of line continuations. Let's see how this looks. I'm assuming you can just copy paste from here without unwanted characters being added... Here's the example (which belongs in a separate module): Option Explicit ' Registry value type definitions Private Const REG_NONE As Long = 0 Private Const REG_SZ As Long = 1 Private Const REG_EXPAND_SZ As Long = 2 Private Const REG_BINARY As Long = 3 Private Const REG_DWORD As Long = 4 Private Const REG_LINK As Long = 6 Private Const REG_MULTI_SZ As Long = 7 Private Const REG_RESOURCE_LIST As Long = 8 Public Const REG_OPTION_NON_VOLATILE = 0 Private Const KEY_ALL_ACCESS As Long = &H3F ' Registry section definitions Public Const HKEY_CURRENT_USER As Long = &H80000001 Private Const c_strKey_8a As String = "Software\Microsoft\Office\8.0\Excel\Options\" Private Const c_strKey_8b As String = "Software\Microsoft\Office\8.0\Excel\Add-in Manager\" Private Const c_strKey_9a As String = "Software\Microsoft\Office\9.0\Excel\Options\" Private Const c_strKey_9b As String = "Software\Microsoft\Office\9.0\Excel\Add-in Manager\" Private Const c_strKey_10a As String = "Software\Microsoft\Office\10.0\Excel\Options\ " Private Const c_strKey_10b As String = "Software\Microsoft\Office\10.0\Excel\Add-in Manager\" Private Const c_strKey_11a As String = "Software\Microsoft\Office\11.0\Excel\Options\ " Private Const c_strKey_11b As String = "Software\Microsoft\Office\11.0\Excel\Add-in Manager\" ' Registry API functions Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, phkResult As Long, lpdwDisposition As Long) As Long Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long Private Declare Function RegSetValueExString Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As String, ByVal cbData As Long) As Long Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long) As Long Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long Private Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long Private Declare Function RegDeleteValue Lib "advapi32.dll" Alias "RegDeleteValueA" (ByVal hKey As Long, ByVal lpValueName As String) As Long ' Delete all registry keys that contain the named add-in Public Function GetAllValues(strAddInName As String) As Single Dim varTemp As Variant Dim strKey As String Dim strValue As String Dim strValueData As String Dim strSection As String Dim strDelVal As String Dim i As Long Dim j As Long Dim sngErrorCode As Single On Error GoTo ErrHandler For i = 1 To 8 Select Case i Case Is = 1: strSection = c_strKey_8a Case Is = 2: strSection = c_strKey_8b Case Is = 3: strSection = c_strKey_9a Case Is = 4: strSection = c_strKey_9b Case Is = 5: strSection = c_strKey_10a Case Is = 6: strSection = c_strKey_10b Case Is = 7: strSection = c_strKey_11a Case Is = 8: strSection = c_strKey_11b End Select j = 0 sngErrorCode = 10 ' Search through specified registry key 'Searches through the add-in key and locates the Map add-in if it exists, the value is then deleted Do While Not (strValue = "Not Found") varTemp = ReadRegistryGetAll(HKEY_CURRENT_USER, strSection, j) j = j + 1 sngErrorCode = 20 ' Find which values are strings If varTemp(0) = REG_SZ Then strValue = varTemp(1) strValueData = varTemp(2) sngErrorCode = 30 ' Check if add-in is in the specified strings If (InStr(strValue, strAddInName) 0) Or (InStr(strValueData, strAddInName) 0) Then strDelVal = DeleteValue(HKEY_CURRENT_USER, strSection, strValue) End If End If sngErrorCode = 40 ' Check if loop needs to be ended If varTemp(0) = 0 Then Exit Do Loop Next i sngErrorCode = 0 GetAllValues = sngErrorCode Exit Function ErrHandler: GetAllValues = sngErrorCode End Function ' Retrieves all the values from anywhere in the Registry under a given subkey, currently only returns string and double word values Public Function ReadRegistryGetAll(ByVal lngGroup As Long, ByVal strSection As String, lngIndex As Long) As Variant Dim lngResult As Long Dim lngKeyValue As Long Dim lngDataTypeValue As Long Dim lngValueLength As Long Dim lngValueNameLength As Long Dim strValueName As String Dim strValue As String Dim dblTemp As Double On Error Resume Next lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue) strValue = Space$(2048) strValueName = Space$(2048) lngValueLength = Len(strValue) lngValueNameLength = Len(strValueName) lngResult = RegEnumValue(lngKeyValue, lngIndex, strValueName, lngValueNameLength, 0&, lngDataTypeValue, strValue, lngValueLength) If (lngResult = 0) And (Err.Number = 0) Then If lngDataTypeValue = REG_DWORD Then dblTemp = Asc(Mid$(strValue, 1, 1)) + &H100& * Asc(Mid$(strValue, 2, 1)) + &H10000 * Asc(Mid$(strValue, 3, 1)) + &H1000000 * CDbl(Asc(Mid$(strValue, 4, 1))) strValue = Format$(dblTemp, "000") End If strValue = Left$(strValue, lngValueLength - 1) strValueName = Left$(strValueName, lngValueNameLength) Else strValue = "Not Found" End If lngResult = RegCloseKey(lngKeyValue) ReadRegistryGetAll = Array(lngDataTypeValue, strValueName, strValue) End Function ' Deletes a specified key (and all its subkeys and values if on Win95) from the registry Public Function DeleteValue(ByVal lngGroup As Long, ByVal strSection As String, ByVal strKey As String) As String Dim lngResult As Long Dim lngKeyValue As Long On Error Resume Next lngResult = RegOpenKey(lngGroup, strSection, lngKeyValue) lngResult = RegDeleteValue(lngKeyValue, strKey) lngResult = RegCloseKey(lngKeyValue) End Function ' Set the data field of a value Public Function SetKeyValue(lngPredefinedKey As Long, strKeyName As String, strValueName As String, varValueSetting As Variant, lngValueType As Long) Dim lngRetVal As Long ' result of the SetValueEx function Dim hKey As Long ' handle of open key 'open the specified key lngRetVal = RegCreateKeyEx(lngPredefinedKey, strKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, 0&, hKey, lngRetVal) lngRetVal = SetValueEx(hKey, strValueName, lngValueType, varValueSetting) Call RegCloseKey(hKey) End Function ' Set the data field of a value Public Function SetValueEx(ByVal hKey As Long, strValueName As String, lngType As Long, varValue As Variant) As Long Dim lngValue As Long Dim strValue As String Select Case lngType Case REG_SZ strValue = varValue SetValueEx = RegSetValueExString(hKey, strValueName, 0&, lngType, strValue, Len(strValue)) Case REG_DWORD lngValue = varValue SetValueEx = RegSetValueExLong(hKey, strValueName, 0&, lngType, lngValue, 4) End Select End Function "Peter T" wrote: Unfortunately, I am new to the discussion groups, so I don't know the best method for sharing code. Any suggestions? Why not post it here. If long it's helpful if line-wrapping can be removed with continuations, though not necessary (check end of line col position in the VBE). You might also be interested to see KeepItCool's method for removing addins |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird VBA Behaviour | Excel Worksheet Functions | |||
Weird WindowsMediaPlayer behaviour | Excel Discussion (Misc queries) | |||
Excel2000: Weird behaviour in VBA | Excel Discussion (Misc queries) | |||
Weird Cell Behaviour | Excel Programming | |||
Weird range property behaviour | Excel Programming |