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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
I created the previously mentioned deployment utility to essentially handle the same issue. I have not found a more effective solution if you want/have to keep your add-in in the realm of VBA (as opposed to a solution withi VSTO or similar platforms). You can also bring code modules into workbooks (add-ins) to "update the engine" but it is a relatively complex operation and it requires more technical expertise in your maintenance staff. John Walkenbach's PUP add-in has a unique and ingenious approach to modular/updateable functionality within an add-in. He licenses the source, but again, it is a more complicated undertaking. I would appreciate hearing anyone else's thoughts on the issue. "Trefor" wrote: 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&, |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Trefor, from what you describe below I'd keep it simple.
Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Peter,
Many thanks for your thoughts. Your final line "do this while no other workbooks are open referencing the addin" is an issue because I would like to contain this issue witin excel. For now I will go with Bill's code. -- Trefor "Peter T" wrote: Trefor, from what you describe below I'd keep it simple. Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Bill,
Many thanks for all your comments and code. I have added you code to my startup code and so far so good. I am also going to try and keep the xla filename the same so hopefully this will help. -- Trefor "Bill Pfister" wrote: I created the previously mentioned deployment utility to essentially handle the same issue. I have not found a more effective solution if you want/have to keep your add-in in the realm of VBA (as opposed to a solution withi VSTO or similar platforms). You can also bring code modules into workbooks (add-ins) to "update the engine" but it is a relatively complex operation and it requires more technical expertise in your maintenance staff. John Walkenbach's PUP add-in has a unique and ingenious approach to modular/updateable functionality within an add-in. He licenses the source, but again, it is a more complicated undertaking. I would appreciate hearing anyone else's thoughts on the issue. "Trefor" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Trefor, either I didn't follow your earlier objective or you missed my
point, or possibly both! I thought you want simply to replace xla1 with xla2, where each have the same name and title (if there is a title), and xla1 is currently loaded as an installed addin (though not necessarily). Try this in the Immediate window - first select your addin in Project explorer ?thisworkbook.FullName [hit enter] (returns fullname here) thisworkbook.Close [hit enter] The addin unloads but it remains an installed addin even though currently not loaded. Manually move xla1 out of its current folder and replace with the new identically named xla2. Back in the immediate window copy the addin's fullname as returned above and paste into the following workbooks.open "addin-fullname" [hit enter] For completeness you might also want to do - workbooks("addin-fullname).RunAutoMacros xlAutoOpen [hit enter]] Assuming name, title, and if relevant addin's project name are identical in both versions that's all you need to do. As I mentioned last time no other workbooks should be reference the addin during this process, ie udf's in formulas or a ref' in toolsreferences (probably not applicable). But same holds for whichever way you are updating your addin. In your real code you'd probably want to start by attempting to set object references both to the xla as a workbook (is it loaded) and to its identity in the addins collection (does it exist and if so is it installed). Retain these settings for possible use after replacing the addin or installing for the first time. The registry code posted by Bill here, or by KeepItCool in the earlier link, is very useful when updated addins' name/title are slightly different and the folder is not the default addin folder, replacing in different folders, or to remove all trace in the registry. But for your particular scenario I don't think necessary. Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks for your thoughts. Your final line "do this while no other workbooks are open referencing the addin" is an issue because I would like to contain this issue witin excel. For now I will go with Bill's code. -- Trefor "Peter T" wrote: Trefor, from what you describe below I'd keep it simple. Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Peter,
Many thanks again for the detailed reply. Perhaps I will summarize what I was trying to do: Start.xls - (contains basic checking code and all my worksheets) (1) Check to see if "<networkdrive\macro.xla" is different to "<localdrive\Application Directory". If different copy off the server. (2) Check to see if "<localdrive\Application Directory" is different to Application.UserLibraryPath. If different copy from "<localdrive\Application Directory" (3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename") and all code for the duration session is then run from macro.xla. (Auto Open/Close are in Start.xls) When I originally posted this thread, the addin was named macro v1.0.xla and each upgrade was then distributed as macro v1.0.xla for small changes and then macro v1.1.xla for more significant changes. I had/have three problems: (1) Although I also load the .xla from Application.UserLibraryPath occasionally the addin loaded said it path was "<localdrive\Application Directory". I am not sure why this was happening, but it just seemed to complicate what I was trying to do. (2) When I distributed the update with no name change I often was not seeing the new file, but the old file. (3) When I distributed the update with a name change this seemed fix the (2) problem, but I would then end up with a ever growing list in my ToolAdd-In. I am trying to keep this simple, but also flexible. Given that I distribute the code I don't want the user to have to do anything manually and I would prefer that everything is contained within Start.xla and macro.xla -- Trefor "Peter T" wrote: Trefor, either I didn't follow your earlier objective or you missed my point, or possibly both! I thought you want simply to replace xla1 with xla2, where each have the same name and title (if there is a title), and xla1 is currently loaded as an installed addin (though not necessarily). Try this in the Immediate window - first select your addin in Project explorer ?thisworkbook.FullName [hit enter] (returns fullname here) thisworkbook.Close [hit enter] The addin unloads but it remains an installed addin even though currently not loaded. Manually move xla1 out of its current folder and replace with the new identically named xla2. Back in the immediate window copy the addin's fullname as returned above and paste into the following workbooks.open "addin-fullname" [hit enter] For completeness you might also want to do - workbooks("addin-fullname).RunAutoMacros xlAutoOpen [hit enter]] Assuming name, title, and if relevant addin's project name are identical in both versions that's all you need to do. As I mentioned last time no other workbooks should be reference the addin during this process, ie udf's in formulas or a ref' in toolsreferences (probably not applicable). But same holds for whichever way you are updating your addin. In your real code you'd probably want to start by attempting to set object references both to the xla as a workbook (is it loaded) and to its identity in the addins collection (does it exist and if so is it installed). Retain these settings for possible use after replacing the addin or installing for the first time. The registry code posted by Bill here, or by KeepItCool in the earlier link, is very useful when updated addins' name/title are slightly different and the folder is not the default addin folder, replacing in different folders, or to remove all trace in the registry. But for your particular scenario I don't think necessary. Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks for your thoughts. Your final line "do this while no other workbooks are open referencing the addin" is an issue because I would like to contain this issue witin excel. For now I will go with Bill's code. -- Trefor "Peter T" wrote: Trefor, from what you describe below I'd keep it simple. Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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) |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
I don't entirely follow what you detail in your summary, eg.
- What do you mean by "Application Directory", the Excel.exe folder ? - Start.xls, how is this loaded, in the start-up path? You say "contains...and all my worksheets" Do other workbooks have any links whatsoever to the addin. Apart from the .Run in Start.xls any other links to the addin. I well understand the points/problems you list under "When I originally posted this thread". Confusion abounds about the Addins collection so I'll try and list how Excel populates the collection with some further comments. 1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail installed addins which will load on startup. 2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward compatibility with xl97) 3. xla fullnames in Registry *Excel\Add-in Manager\ These are any other non-installed addins not located in either of the default addin paths. (4. non-installed system addins found by other means) Addin collection names are defined by .Title which defaults. to addin.name if a title does not exist. The Addins collection & Workbooks collection are not directly related. IOW if you unload an installed addin its status in the addins collection does not change (it will load on next Excel startup). If you uninstall an addin, try and install a same name/title addin in a different folder, Excel will look in its addins collection for a same name/title addin. It will find the addin listed in either 2 & 3 above and re-install that. This I think is the original problem you had (if you had not deleted or moved old addin). Even more confusing, if the old & new addin's titles are defined and both same, yet new addin's name & folder location are different, if you programmatically try and install the new addin the old addin may get re-installed (ie if the entry exists in 3 above and the old file is found). Uninstalled addins that are not in a default addin folder may not be visible in Tools Addins in next Excel session (yet remain in the addins collection due to the entry in 3 above). If you uninstall an addin that's not in a default folder, then delete the file, its entry will persist in 3 and in the addins collection. Ideally there ought to be a method to 'Remove' non-installed addins from the addins collection and hence the list in 3. above. But there isn't and this is why problems & confusion occurs. Back to your issue, apart from code in Start.xls to update the addin I don't follow the rest of the set up. Also how does code know that new & old addins are not same (file size/date ?). To summarize, if you are updating addins with slightly different version names and/or folder location, the old addin's details will remain in the addins collection. If the old addin is in a default addin folder it should be removed (not merely renamed). If in a non-default folder you could use the registry code to delete the entry in 3.above. But if everything is identical concerning the addins, ie name, title & intended folder (other than internal code) all you need to do is unload it, remove from folder or Kill it, replace new addin in the same folder, and load it. IOW in this particular scenario absolutely nothing concerning the addins collection or registry entries change, hence registry code not required. Finally, if updating an addin with revised version name and/or folder, any UDF links in other workbooks may also need updating (but from what I gather this is not in your scenario). Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks again for the detailed reply. Perhaps I will summarize what I was trying to do: Start.xls - (contains basic checking code and all my worksheets) (1) Check to see if "<networkdrive\macro.xla" is different to "<localdrive\Application Directory". If different copy off the server. (2) Check to see if "<localdrive\Application Directory" is different to Application.UserLibraryPath. If different copy from "<localdrive\Application Directory" (3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename") and all code for the duration 'session' is then run from macro.xla. (Auto Open/Close are in Start.xls) When I originally posted this thread, the addin was named "macro v1.0.xla" and each upgrade was then distributed as "macro v1.0.xla" for small changes and then "macro v1.1.xla" for more significant changes. I had/have three problems: (1) Although I also load the .xla from Application.UserLibraryPath occasionally the addin loaded said it path was "<localdrive\Application Directory". I am not sure why this was happening, but it just seemed to complicate what I was trying to do. (2) When I distributed the update with no name change I often was not seeing the new file, but the old file. (3) When I distributed the update with a name change this seemed fix the (2) problem, but I would then end up with a ever growing list in my ToolAdd-In. I am trying to keep this simple, but also flexible. Given that I distribute the code I don't want the user to have to do anything manually and I would prefer that everything is contained within Start.xla and macro.xla -- Trefor "Peter T" wrote: Trefor, either I didn't follow your earlier objective or you missed my point, or possibly both! I thought you want simply to replace xla1 with xla2, where each have the same name and title (if there is a title), and xla1 is currently loaded as an installed addin (though not necessarily). Try this in the Immediate window - first select your addin in Project explorer ?thisworkbook.FullName [hit enter] (returns fullname here) thisworkbook.Close [hit enter] The addin unloads but it remains an installed addin even though currently not loaded. Manually move xla1 out of its current folder and replace with the new identically named xla2. Back in the immediate window copy the addin's fullname as returned above and paste into the following workbooks.open "addin-fullname" [hit enter] For completeness you might also want to do - workbooks("addin-fullname).RunAutoMacros xlAutoOpen [hit enter]] Assuming name, title, and if relevant addin's project name are identical in both versions that's all you need to do. As I mentioned last time no other workbooks should be reference the addin during this process, ie udf's in formulas or a ref' in toolsreferences (probably not applicable). But same holds for whichever way you are updating your addin. In your real code you'd probably want to start by attempting to set object references both to the xla as a workbook (is it loaded) and to its identity in the addins collection (does it exist and if so is it installed). Retain these settings for possible use after replacing the addin or installing for the first time. The registry code posted by Bill here, or by KeepItCool in the earlier link, is very useful when updated addins' name/title are slightly different and the folder is not the default addin folder, replacing in different folders, or to remove all trace in the registry. But for your particular scenario I don't think necessary. Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks for your thoughts. Your final line "do this while no other workbooks are open referencing the addin" is an issue because I would like to contain this issue witin excel. For now I will go with Bill's code. -- Trefor "Peter T" wrote: Trefor, from what you describe below I'd keep it simple. Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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) |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Peter,
Application Directory - my application directory Start.xls resides in my app directory and is started through a shortcut or a double click. There are no links to the macro.xla. I don't know what what you mean by OPEN, OPEN1, etc how does code know that new & old addins are not same (file size/date ?). In start.xls: Public Const DataCollectClientVersion = "6.46c" Public Const DCMaster = "'Customer Data Collect Master.xla'" Public Const MinimumMacroVersion = "6.46b" ' This is the minimum version of the macro file, that this program will run with. Run DCMaster & "!Prepare_For_Startup", DataCollectClientVersion, MinimumMacroVersion In macro.xla (or as you can see above DCMaster) Public Const DataCollectMacroVersion = "6.46b" ' The version of this macro file Public Const DataCollectMasterVersion = "6.46" ' The version that must be run. I.E. the must upgrade to version Sub Prepare_For_Startup(DataCollectClientVersion, MinimumMacroVersion As String) ' Check Client/Master Versions If MinimumMacroVersion DataCollectMacroVersion Then msg = "A crital error has occured." & vbCrLf & vbCrLf msg = msg & "The '" & ThisWorkbook.name & "' file is version " & DataCollectMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "The minimum version required for this Data Collect is " & MinimumMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "This program can not continue until this is resolved." MsgBox msg, vbCritical Exit Sub End If .. .. .. .. End Sub It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. Thankyou very much for your very complete answer. One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? -- Trefor "Peter T" wrote: I don't entirely follow what you detail in your summary, eg. - What do you mean by "Application Directory", the Excel.exe folder ? - Start.xls, how is this loaded, in the start-up path? You say "contains...and all my worksheets" Do other workbooks have any links whatsoever to the addin. Apart from the .Run in Start.xls any other links to the addin. I well understand the points/problems you list under "When I originally posted this thread". Confusion abounds about the Addins collection so I'll try and list how Excel populates the collection with some further comments. 1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail installed addins which will load on startup. 2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward compatibility with xl97) 3. xla fullnames in Registry *Excel\Add-in Manager\ These are any other non-installed addins not located in either of the default addin paths. (4. non-installed system addins found by other means) Addin collection names are defined by .Title which defaults. to addin.name if a title does not exist. The Addins collection & Workbooks collection are not directly related. IOW if you unload an installed addin its status in the addins collection does not change (it will load on next Excel startup). If you uninstall an addin, try and install a same name/title addin in a different folder, Excel will look in its addins collection for a same name/title addin. It will find the addin listed in either 2 & 3 above and re-install that. This I think is the original problem you had (if you had not deleted or moved old addin). Even more confusing, if the old & new addin's titles are defined and both same, yet new addin's name & folder location are different, if you programmatically try and install the new addin the old addin may get re-installed (ie if the entry exists in 3 above and the old file is found). Uninstalled addins that are not in a default addin folder may not be visible in Tools Addins in next Excel session (yet remain in the addins collection due to the entry in 3 above). If you uninstall an addin that's not in a default folder, then delete the file, its entry will persist in 3 and in the addins collection. Ideally there ought to be a method to 'Remove' non-installed addins from the addins collection and hence the list in 3. above. But there isn't and this is why problems & confusion occurs. Back to your issue, apart from code in Start.xls to update the addin I don't follow the rest of the set up. Also how does code know that new & old addins are not same (file size/date ?). To summarize, if you are updating addins with slightly different version names and/or folder location, the old addin's details will remain in the addins collection. If the old addin is in a default addin folder it should be removed (not merely renamed). If in a non-default folder you could use the registry code to delete the entry in 3.above. But if everything is identical concerning the addins, ie name, title & intended folder (other than internal code) all you need to do is unload it, remove from folder or Kill it, replace new addin in the same folder, and load it. IOW in this particular scenario absolutely nothing concerning the addins collection or registry entries change, hence registry code not required. Finally, if updating an addin with revised version name and/or folder, any UDF links in other workbooks may also need updating (but from what I gather this is not in your scenario). Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks again for the detailed reply. Perhaps I will summarize what I was trying to do: Start.xls - (contains basic checking code and all my worksheets) (1) Check to see if "<networkdrive\macro.xla" is different to "<localdrive\Application Directory". If different copy off the server. (2) Check to see if "<localdrive\Application Directory" is different to Application.UserLibraryPath. If different copy from "<localdrive\Application Directory" (3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename") and all code for the duration 'session' is then run from macro.xla. (Auto Open/Close are in Start.xls) When I originally posted this thread, the addin was named "macro v1.0.xla" and each upgrade was then distributed as "macro v1.0.xla" for small changes and then "macro v1.1.xla" for more significant changes. I had/have three problems: (1) Although I also load the .xla from Application.UserLibraryPath occasionally the addin loaded said it path was "<localdrive\Application Directory". I am not sure why this was happening, but it just seemed to complicate what I was trying to do. (2) When I distributed the update with no name change I often was not seeing the new file, but the old file. (3) When I distributed the update with a name change this seemed fix the (2) problem, but I would then end up with a ever growing list in my ToolAdd-In. I am trying to keep this simple, but also flexible. Given that I distribute the code I don't want the user to have to do anything manually and I would prefer that everything is contained within Start.xla and macro.xla -- Trefor "Peter T" wrote: Trefor, either I didn't follow your earlier objective or you missed my point, or possibly both! I thought you want simply to replace xla1 with xla2, where each have the same name and title (if there is a title), and xla1 is currently loaded as an installed addin (though not necessarily). Try this in the Immediate window - first select your addin in Project explorer ?thisworkbook.FullName [hit enter] (returns fullname here) thisworkbook.Close [hit enter] The addin unloads but it remains an installed addin even though currently not loaded. Manually move xla1 out of its current folder and replace with the new identically named xla2. Back in the immediate window copy the addin's fullname as returned above and paste into the following workbooks.open "addin-fullname" [hit enter] For completeness you might also want to do - workbooks("addin-fullname).RunAutoMacros xlAutoOpen [hit enter]] Assuming name, title, and if relevant addin's project name are identical in both versions that's all you need to do. As I mentioned last time no other workbooks should be reference the addin during this process, ie udf's in formulas or a ref' in toolsreferences (probably not applicable). But same holds for whichever way you are updating your addin. In your real code you'd probably want to start by attempting to set object references both to the xla as a workbook (is it loaded) and to its identity in the addins collection (does it exist and if so is it installed). Retain these settings for possible use after replacing the addin or installing for the first time. The registry code posted by Bill here, or by KeepItCool in the earlier link, is very useful when updated addins' name/title are slightly different and the folder is not the default addin folder, replacing in different folders, or to remove all trace in the registry. But for your particular scenario I don't think necessary. Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks for your thoughts. Your final line "do this while no other workbooks are open referencing the addin" is an issue because I would like to contain this issue witin excel. For now I will go with Bill's code. -- Trefor "Peter T" wrote: Trefor, from what you describe below I'd keep it simple. Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
I don't know what what you mean by OPEN, OPEN1, etc
Look for OPEN, OPEN1 etc in your registry under HKCU - Software\Microsoft\Office\xl-ver\Excel\Options\ (or \Excel\Microsoft Excel\ in xl97) these are the Installed Addins, ie ticked in Tools Addins Also look in the nearby \Add-in Manager for uninstalled addins It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. There may be good reasons to append the addin name with its version though it does add to complications as discussed earlier. You could of course put the version inside the xla, eg in a cell or one of the file properties. Read this from your Main.xls by setting a ref to the xla as a Workbook. Just wondering, if your xla is only used by your Main.xls does the xla even need to be 'installed' as an addin, perhaps you could just load it on demand from Main.xls, eg on error resume next Public gbAddinRunning as boolean Sub SomeRoutineThatCallsMyAddin() If Not CheckAddinRunning then Exit sub 'OK to run code from myAddin End Sub Function CheckAddinRunning() as boolean On error resume next If not If gbAddinRunning then set wb = workbooks("myAddin.xla") if wb is nothing then set wb = workbooks.open(addin-fullname) CheckVersion wb end if gbAddinRunning = not wb is nothing End if CheckAddinRunning = gbAddinRunning End sub function CheckVersion(wb as workbook) as boolean 'appropriate error handling throughout & messages if/as required if wb.sheets(1).range("a1") < "required-version" then wb.close set wb = nothing ' kill or move addin-fullname ' bring in new addin from server set wb = workbooks.open(addin-fullname) end if CheckVersion = not wb is nothing 'everything seems OK end function (only an idea) One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? It would only be a problem if both same name/title files are in the Addins collection from respective folders, even if only one is to be installed. Obviously avoid possibility of both being loaded concurrently regardless as to their status in the addins collection. Regards, Peter T "Trefor" wrote in message ... Peter, Application Directory - my application directory Start.xls resides in my app directory and is started through a shortcut or a double click. There are no links to the macro.xla. I don't know what what you mean by OPEN, OPEN1, etc "how does code know that new & old addins are not same (file size/date ?)." In start.xls: Public Const DataCollectClientVersion = "6.46c" Public Const DCMaster = "'Customer Data Collect Master.xla'" Public Const MinimumMacroVersion = "6.46b" ' This is the minimum version of the macro file, that this program will run with. Run DCMaster & "!Prepare_For_Startup", DataCollectClientVersion, MinimumMacroVersion In macro.xla (or as you can see above DCMaster) Public Const DataCollectMacroVersion = "6.46b" ' The version of this macro file Public Const DataCollectMasterVersion = "6.46" ' The version that must be run. I.E. the must upgrade to version Sub Prepare_For_Startup(DataCollectClientVersion, MinimumMacroVersion As String) ' Check Client/Master Versions If MinimumMacroVersion DataCollectMacroVersion Then msg = "A crital error has occured." & vbCrLf & vbCrLf msg = msg & "The '" & ThisWorkbook.name & "' file is version " & DataCollectMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "The minimum version required for this Data Collect is " & MinimumMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "This program can not continue until this is resolved." MsgBox msg, vbCritical Exit Sub End If . . . . End Sub It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. Thankyou very much for your very complete answer. One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? -- Trefor "Peter T" wrote: I don't entirely follow what you detail in your summary, eg. - What do you mean by "Application Directory", the Excel.exe folder ? - Start.xls, how is this loaded, in the start-up path? You say "contains...and all my worksheets" Do other workbooks have any links whatsoever to the addin. Apart from the .Run in Start.xls any other links to the addin. I well understand the points/problems you list under "When I originally posted this thread". Confusion abounds about the Addins collection so I'll try and list how Excel populates the collection with some further comments. 1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail installed addins which will load on startup. 2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward compatibility with xl97) 3. xla fullnames in Registry *Excel\Add-in Manager\ These are any other non-installed addins not located in either of the default addin paths. (4. non-installed system addins found by other means) Addin collection names are defined by .Title which defaults. to addin.name if a title does not exist. The Addins collection & Workbooks collection are not directly related. IOW if you unload an installed addin its status in the addins collection does not change (it will load on next Excel startup). If you uninstall an addin, try and install a same name/title addin in a different folder, Excel will look in its addins collection for a same name/title addin. It will find the addin listed in either 2 & 3 above and re-install that. This I think is the original problem you had (if you had not deleted or moved old addin). Even more confusing, if the old & new addin's titles are defined and both same, yet new addin's name & folder location are different, if you programmatically try and install the new addin the old addin may get re-installed (ie if the entry exists in 3 above and the old file is found). Uninstalled addins that are not in a default addin folder may not be visible in Tools Addins in next Excel session (yet remain in the addins collection due to the entry in 3 above). If you uninstall an addin that's not in a default folder, then delete the file, its entry will persist in 3 and in the addins collection. Ideally there ought to be a method to 'Remove' non-installed addins from the addins collection and hence the list in 3. above. But there isn't and this is why problems & confusion occurs. Back to your issue, apart from code in Start.xls to update the addin I don't follow the rest of the set up. Also how does code know that new & old addins are not same (file size/date ?). To summarize, if you are updating addins with slightly different version names and/or folder location, the old addin's details will remain in the addins collection. If the old addin is in a default addin folder it should be removed (not merely renamed). If in a non-default folder you could use the registry code to delete the entry in 3.above. But if everything is identical concerning the addins, ie name, title & intended folder (other than internal code) all you need to do is unload it, remove from folder or Kill it, replace new addin in the same folder, and load it. IOW in this particular scenario absolutely nothing concerning the addins collection or registry entries change, hence registry code not required. Finally, if updating an addin with revised version name and/or folder, any UDF links in other workbooks may also need updating (but from what I gather this is not in your scenario). Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks again for the detailed reply. Perhaps I will summarize what I was trying to do: Start.xls - (contains basic checking code and all my worksheets) (1) Check to see if "<networkdrive\macro.xla" is different to "<localdrive\Application Directory". If different copy off the server. (2) Check to see if "<localdrive\Application Directory" is different to Application.UserLibraryPath. If different copy from "<localdrive\Application Directory" (3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename") and all code for the duration 'session' is then run from macro.xla. (Auto Open/Close are in Start.xls) When I originally posted this thread, the addin was named "macro v1.0.xla" and each upgrade was then distributed as "macro v1.0.xla" for small changes and then "macro v1.1.xla" for more significant changes. I had/have three problems: (1) Although I also load the .xla from Application.UserLibraryPath occasionally the addin loaded said it path was "<localdrive\Application Directory". I am not sure why this was happening, but it just seemed to complicate what I was trying to do. (2) When I distributed the update with no name change I often was not seeing the new file, but the old file. (3) When I distributed the update with a name change this seemed fix the (2) problem, but I would then end up with a ever growing list in my ToolAdd-In. I am trying to keep this simple, but also flexible. Given that I distribute the code I don't want the user to have to do anything manually and I would prefer that everything is contained within Start.xla and macro.xla -- Trefor "Peter T" wrote: Trefor, either I didn't follow your earlier objective or you missed my point, or possibly both! I thought you want simply to replace xla1 with xla2, where each have the same name and title (if there is a title), and xla1 is currently loaded as an installed addin (though not necessarily). Try this in the Immediate window - first select your addin in Project explorer ?thisworkbook.FullName [hit enter] (returns fullname here) thisworkbook.Close [hit enter] The addin unloads but it remains an installed addin even though currently not loaded. Manually move xla1 out of its current folder and replace with the new identically named xla2. Back in the immediate window copy the addin's fullname as returned above and paste into the following workbooks.open "addin-fullname" [hit enter] For completeness you might also want to do - workbooks("addin-fullname).RunAutoMacros xlAutoOpen [hit enter]] Assuming name, title, and if relevant addin's project name are identical in both versions that's all you need to do. As I mentioned last time no other workbooks should be reference the addin during this process, ie udf's in formulas or a ref' in toolsreferences (probably not applicable). But same holds for whichever way you are updating your addin. In your real code you'd probably want to start by attempting to set object references both to the xla as a workbook (is it loaded) and to its identity in the addins collection (does it exist and if so is it installed). Retain these settings for possible use after replacing the addin or installing for the first time. The registry code posted by Bill here, or by KeepItCool in the earlier link, is very useful when updated addins' name/title are slightly different and the folder is not the default addin folder, replacing in different folders, or to remove all trace in the registry. But for your particular scenario I don't think necessary. Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks for your thoughts. Your final line "do this while no other workbooks are open referencing the addin" is an issue because I would like to contain this issue witin excel. For now I will go with Bill's code. -- Trefor "Peter T" wrote: Trefor, from what you describe below I'd keep it simple. Assuming name + title pair are same in both addins, and the old addin is loaded and installed as an addin - unload the old addin as if a normal workbook (don't uninstall it), move it to a different folder or Kill it, copy the new addin into the original folder, eg Userlibrarypath, load it as a normal workbook (no need to install as an addin). As far as the addins collection is concerned nothing has changed. do this while no other workbooks are open referencing the addin Regards, Peter T "Trefor" wrote in message ... 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Peter,
Sorry for the delay in getting back to you and many thanks for the reply, but I am still have issues. Function AddinPresent(DCMaster2) As Boolean ' This Funcation checks that the add-in is available for use by the Client Dim AddInInstalled As Boolean, wb As Workbook With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) ' Is Addin Installed? AddInInstalled = .Installed End With If AddInInstalled Then On Error Resume Next Run DCMaster & "!any_macro" LastError = Err If LastError = 0 Then ' Addin is installed, exit function AddinPresent = True Exit Function End If End If ' Try and clear up all reference to the AddIn Call Clear_XLA(DCMaster2) ' This calls a series of .Installed = False for every path I can think of ' With AddIns.Add(FileName:=Application.UserLibraryPath & MemoryFile) ' .Installed = False ' End With ' Then it calls Bill's registry cleaner code' ' Addin is NOT install, attempt to load it With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) .Installed = True End With ' Now check that the Addin has loaded With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) AddInInstalled = .Installed End With If AddInInstalled Then ' Addin is installed, exit function AddinPresent = True Exit Function End If ' Still not loaded On Error Resume Next Set wb = Workbooks(DCMaster2) <<<< Error 9 at this point If wb Is Nothing Then Set wb = Workbooks.Open(Application.UserLibraryPath & DCMaster2) End If ' Given the file was loaded as a workbook, I am not sure whether I can now make it an Addin or whether this matters? ' Addin is NOT install, attempt to load it With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) .Installed = True End With With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) AddInInstalled = .Installed End With If AddInInstalled Then ' Addin is installed, exit function AddinPresent = True Exit Function Else ' I am exiting here with .XLA loaded in the project folder. ' ThisWorkbook.path = Application.UserLibraryPath & DCMaster2 ' If I got to Tools Add-Ins, DCMaster2 is listed and is unticked. ' If I click on it is says the file does not exist in myAppPath & DCMaster2 and do I want to remove it. If I remove this last Add-In check at this point everything seems to work. ' Addin has failed to install AddinPresent = False Exit Function End If End Function So as you put it: Just wondering, if your xla is only used by your Main.xls does the xla even need to be 'installed' as an addin, perhaps you could just load it on demand from Main.xls, eg Good question, I just presumed based on previous feedback that was what I needed to do. If I loaded the file this way, what are the advantages/ disadvantages compared to an Add-In? I.E. Is there catch I have not found yet? -- Trefor "Peter T" wrote: I don't know what what you mean by OPEN, OPEN1, etc Look for OPEN, OPEN1 etc in your registry under HKCU - Software\Microsoft\Office\xl-ver\Excel\Options\ (or \Excel\Microsoft Excel\ in xl97) these are the Installed Addins, ie ticked in Tools Addins Also look in the nearby \Add-in Manager for uninstalled addins It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. There may be good reasons to append the addin name with its version though it does add to complications as discussed earlier. You could of course put the version inside the xla, eg in a cell or one of the file properties. Read this from your Main.xls by setting a ref to the xla as a Workbook. Just wondering, if your xla is only used by your Main.xls does the xla even need to be 'installed' as an addin, perhaps you could just load it on demand from Main.xls, eg on error resume next Public gbAddinRunning as boolean Sub SomeRoutineThatCallsMyAddin() If Not CheckAddinRunning then Exit sub 'OK to run code from myAddin End Sub Function CheckAddinRunning() as boolean On error resume next If not If gbAddinRunning then set wb = workbooks("myAddin.xla") if wb is nothing then set wb = workbooks.open(addin-fullname) CheckVersion wb end if gbAddinRunning = not wb is nothing End if CheckAddinRunning = gbAddinRunning End sub function CheckVersion(wb as workbook) as boolean 'appropriate error handling throughout & messages if/as required if wb.sheets(1).range("a1") < "required-version" then wb.close set wb = nothing ' kill or move addin-fullname ' bring in new addin from server set wb = workbooks.open(addin-fullname) end if CheckVersion = not wb is nothing 'everything seems OK end function (only an idea) One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? It would only be a problem if both same name/title files are in the Addins collection from respective folders, even if only one is to be installed. Obviously avoid possibility of both being loaded concurrently regardless as to their status in the addins collection. Regards, Peter T "Trefor" wrote in message ... Peter, Application Directory - my application directory Start.xls resides in my app directory and is started through a shortcut or a double click. There are no links to the macro.xla. I don't know what what you mean by OPEN, OPEN1, etc "how does code know that new & old addins are not same (file size/date ?)." In start.xls: Public Const DataCollectClientVersion = "6.46c" Public Const DCMaster = "'Customer Data Collect Master.xla'" Public Const MinimumMacroVersion = "6.46b" ' This is the minimum version of the macro file, that this program will run with. Run DCMaster & "!Prepare_For_Startup", DataCollectClientVersion, MinimumMacroVersion In macro.xla (or as you can see above DCMaster) Public Const DataCollectMacroVersion = "6.46b" ' The version of this macro file Public Const DataCollectMasterVersion = "6.46" ' The version that must be run. I.E. the must upgrade to version Sub Prepare_For_Startup(DataCollectClientVersion, MinimumMacroVersion As String) ' Check Client/Master Versions If MinimumMacroVersion DataCollectMacroVersion Then msg = "A crital error has occured." & vbCrLf & vbCrLf msg = msg & "The '" & ThisWorkbook.name & "' file is version " & DataCollectMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "The minimum version required for this Data Collect is " & MinimumMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "This program can not continue until this is resolved." MsgBox msg, vbCritical Exit Sub End If . . . . End Sub It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. Thankyou very much for your very complete answer. One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? -- Trefor "Peter T" wrote: I don't entirely follow what you detail in your summary, eg. - What do you mean by "Application Directory", the Excel.exe folder ? - Start.xls, how is this loaded, in the start-up path? You say "contains...and all my worksheets" Do other workbooks have any links whatsoever to the addin. Apart from the .Run in Start.xls any other links to the addin. I well understand the points/problems you list under "When I originally posted this thread". Confusion abounds about the Addins collection so I'll try and list how Excel populates the collection with some further comments. 1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail installed addins which will load on startup. 2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward compatibility with xl97) 3. xla fullnames in Registry *Excel\Add-in Manager\ These are any other non-installed addins not located in either of the default addin paths. (4. non-installed system addins found by other means) Addin collection names are defined by .Title which defaults. to addin.name if a title does not exist. The Addins collection & Workbooks collection are not directly related. IOW if you unload an installed addin its status in the addins collection does not change (it will load on next Excel startup). If you uninstall an addin, try and install a same name/title addin in a different folder, Excel will look in its addins collection for a same name/title addin. It will find the addin listed in either 2 & 3 above and re-install that. This I think is the original problem you had (if you had not deleted or moved old addin). Even more confusing, if the old & new addin's titles are defined and both same, yet new addin's name & folder location are different, if you programmatically try and install the new addin the old addin may get re-installed (ie if the entry exists in 3 above and the old file is found). Uninstalled addins that are not in a default addin folder may not be visible in Tools Addins in next Excel session (yet remain in the addins collection due to the entry in 3 above). If you uninstall an addin that's not in a default folder, then delete the file, its entry will persist in 3 and in the addins collection. Ideally there ought to be a method to 'Remove' non-installed addins from the addins collection and hence the list in 3. above. But there isn't and this is why problems & confusion occurs. Back to your issue, apart from code in Start.xls to update the addin I don't follow the rest of the set up. Also how does code know that new & old addins are not same (file size/date ?). To summarize, if you are updating addins with slightly different version names and/or folder location, the old addin's details will remain in the addins collection. If the old addin is in a default addin folder it should be removed (not merely renamed). If in a non-default folder you could use the registry code to delete the entry in 3.above. But if everything is identical concerning the addins, ie name, title & intended folder (other than internal code) all you need to do is unload it, remove from folder or Kill it, replace new addin in the same folder, and load it. IOW in this particular scenario absolutely nothing concerning the addins collection or registry entries change, hence registry code not required. Finally, if updating an addin with revised version name and/or folder, any UDF links in other workbooks may also need updating (but from what I gather this is not in your scenario). Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks again for the detailed reply. Perhaps I will summarize what I was trying to do: Start.xls - (contains basic checking code and all my worksheets) (1) Check to see if "<networkdrive\macro.xla" is different to "<localdrive\Application Directory". If different copy off the server. (2) Check to see if "<localdrive\Application Directory" is different to Application.UserLibraryPath. If different copy from "<localdrive\Application Directory" (3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename") and all code for the duration 'session' is then run from macro.xla. (Auto Open/Close are in Start.xls) When I originally posted this thread, the addin was named "macro v1.0.xla" and each upgrade was then distributed as "macro v1.0.xla" for small changes and then "macro v1.1.xla" for more significant changes. I had/have three problems: (1) Although I also load the .xla from Application.UserLibraryPath occasionally the addin loaded said it path was "<localdrive\Application Directory". I am not sure why this was happening, but it just seemed to complicate what I was trying to do. (2) When I distributed the update with no name change I often was not seeing the new file, but the old file. (3) When I distributed the update with a name change this seemed fix the (2) |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird AddIn behaviour
Hello again Trefor,
I still haven't fully worked out your whole setup but the code you posted seems unnecessarily complicated. I assume "DCMaster2" is the addin name (a string) but confused. If the first character is not a "\" path separator some of your code will fail, but if it is another part of your code will fail (I think). I'm not sure if you are confused between an Addin and the Addins collection An Addin is a workbook with its IsAddin property True, typically saved with an xla extension The Addins collection (manager) is basically a set of references to Addins. A given addin may or may not be installed (ie if checked in tools addins will load on startup). If the addin has been removed from known location it will continue to exist in the collection, (though error and/or warning may occur on attempt to install it). I would suggest giving your addin a title as I mentioned previously, then test its existence in the collection dim adn as Addin on error resume next Set adn = application.addins(sTitle) bInManager = not adn is nothing If it exists in the collection is it installed bInstalled = adn.installed If it's in the collection you can return it's path whether or not it's installed or even if it no longer exists on disk sPath = adn.path To conclusively prove the addin is loaded if installed (or loaded by some other method) on error resume next set wb = application.workbooks("myAddin.xla") As for trying to find the location of the addin, if not installed or not loaded, use the Dir function rather than trying to force install from various possible locations. Don't forget the path separator. Just wondering, if your xla is only used by your Main.xls does the xla even need to be 'installed' as an addin, perhaps you could just load it on demand from Main.xls, eg Good question, I just presumed based on previous feedback that was what I needed to do. If I loaded the file this way, what are the advantages/ disadvantages compared to an Add-In? I.E. Is there catch I have not found yet? Your comment "compared to an Add-In" is what made me wonder about possible confusion. Having an Addin in the addins collection is merely a convenience to have it automatically load on startup. There is no difference as to how the addin will operate once it is loaded. It seems the addin is only used by your Main.xls and contains no UDF's. Therefore providing you know it's fullname it's easy for code in your Main.xls to test if the addin (ie workbook) is loaded and if not load it. You might do this in Main's open event. Do your checks to verify the current version is up to date etc. Error handler's elsewhere to do same if trying to run code from it fails. FWIW you could even rename the default xla extension to disguise it and reduce temptation for users to load it independently. Regards, Peter T "Trefor" wrote in message ... Peter, Sorry for the delay in getting back to you and many thanks for the reply, but I am still have issues. Function AddinPresent(DCMaster2) As Boolean ' This Funcation checks that the add-in is available for use by the Client Dim AddInInstalled As Boolean, wb As Workbook With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) ' Is Addin Installed? AddInInstalled = .Installed End With If AddInInstalled Then On Error Resume Next Run DCMaster & "!any_macro" LastError = Err If LastError = 0 Then ' Addin is installed, exit function AddinPresent = True Exit Function End If End If ' Try and clear up all reference to the AddIn Call Clear_XLA(DCMaster2) ' This calls a series of .Installed = False for every path I can think of ' With AddIns.Add(FileName:=Application.UserLibraryPath & MemoryFile) ' .Installed = False ' End With ' Then it calls Bill's registry cleaner code' ' Addin is NOT install, attempt to load it With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) .Installed = True End With ' Now check that the Addin has loaded With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) AddInInstalled = .Installed End With If AddInInstalled Then ' Addin is installed, exit function AddinPresent = True Exit Function End If ' Still not loaded On Error Resume Next Set wb = Workbooks(DCMaster2) <<<< Error 9 at this point If wb Is Nothing Then Set wb = Workbooks.Open(Application.UserLibraryPath & DCMaster2) End If ' Given the file was loaded as a workbook, I am not sure whether I can now make it an Addin or whether this matters? ' Addin is NOT install, attempt to load it With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) .Installed = True End With With AddIns.Add(FileName:=Application.UserLibraryPath & DCMaster2) AddInInstalled = .Installed End With If AddInInstalled Then ' Addin is installed, exit function AddinPresent = True Exit Function Else ' I am exiting here with .XLA loaded in the project folder. ' ThisWorkbook.path = Application.UserLibraryPath & DCMaster2 ' If I got to Tools Add-Ins, DCMaster2 is listed and is unticked. ' If I click on it is says the file does not exist in myAppPath & DCMaster2 and do I want to remove it. ' If I remove this last Add-In check at this point everything seems to work. ' Addin has failed to install AddinPresent = False Exit Function End If End Function So as you put it: Just wondering, if your xla is only used by your Main.xls does the xla even need to be 'installed' as an addin, perhaps you could just load it on demand from Main.xls, eg Good question, I just presumed based on previous feedback that was what I needed to do. If I loaded the file this way, what are the advantages/ disadvantages compared to an Add-In? I.E. Is there catch I have not found yet? -- Trefor "Peter T" wrote: I don't know what what you mean by OPEN, OPEN1, etc Look for OPEN, OPEN1 etc in your registry under HKCU - Software\Microsoft\Office\xl-ver\Excel\Options\ (or \Excel\Microsoft Excel\ in xl97) these are the Installed Addins, ie ticked in Tools Addins Also look in the nearby \Add-in Manager for uninstalled addins It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. There may be good reasons to append the addin name with its version though it does add to complications as discussed earlier. You could of course put the version inside the xla, eg in a cell or one of the file properties. Read this from your Main.xls by setting a ref to the xla as a Workbook. Just wondering, if your xla is only used by your Main.xls does the xla even need to be 'installed' as an addin, perhaps you could just load it on demand from Main.xls, eg on error resume next Public gbAddinRunning as boolean Sub SomeRoutineThatCallsMyAddin() If Not CheckAddinRunning then Exit sub 'OK to run code from myAddin End Sub Function CheckAddinRunning() as boolean On error resume next If not If gbAddinRunning then set wb = workbooks("myAddin.xla") if wb is nothing then set wb = workbooks.open(addin-fullname) CheckVersion wb end if gbAddinRunning = not wb is nothing End if CheckAddinRunning = gbAddinRunning End sub function CheckVersion(wb as workbook) as boolean 'appropriate error handling throughout & messages if/as required if wb.sheets(1).range("a1") < "required-version" then wb.close set wb = nothing ' kill or move addin-fullname ' bring in new addin from server set wb = workbooks.open(addin-fullname) end if CheckVersion = not wb is nothing 'everything seems OK end function (only an idea) One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? It would only be a problem if both same name/title files are in the Addins collection from respective folders, even if only one is to be installed. Obviously avoid possibility of both being loaded concurrently regardless as to their status in the addins collection. Regards, Peter T "Trefor" wrote in message ... Peter, Application Directory - my application directory Start.xls resides in my app directory and is started through a shortcut or a double click. There are no links to the macro.xla. I don't know what what you mean by OPEN, OPEN1, etc "how does code know that new & old addins are not same (file size/date ?)." In start.xls: Public Const DataCollectClientVersion = "6.46c" Public Const DCMaster = "'Customer Data Collect Master.xla'" Public Const MinimumMacroVersion = "6.46b" ' This is the minimum version of the macro file, that this program will run with. Run DCMaster & "!Prepare_For_Startup", DataCollectClientVersion, MinimumMacroVersion In macro.xla (or as you can see above DCMaster) Public Const DataCollectMacroVersion = "6.46b" ' The version of this macro file Public Const DataCollectMasterVersion = "6.46" ' The version that must be run. I.E. the must upgrade to version Sub Prepare_For_Startup(DataCollectClientVersion, MinimumMacroVersion As String) ' Check Client/Master Versions If MinimumMacroVersion DataCollectMacroVersion Then msg = "A crital error has occured." & vbCrLf & vbCrLf msg = msg & "The '" & ThisWorkbook.name & "' file is version " & DataCollectMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "The minimum version required for this Data Collect is " & MinimumMacroVersion & "." & vbCrLf & vbCrLf msg = msg & "This program can not continue until this is resolved." MsgBox msg, vbCritical Exit Sub End If . . . . End Sub It sounds to me that by using a version number in my macro.xla file name I am making this more complicated than it needs to be. This is not a big deal in my case and so I will tweak the code to use the same name from now on. Thankyou very much for your very complete answer. One last question, do you see an issue with macro.xla existing in both the default location and my apps directory? -- Trefor "Peter T" wrote: I don't entirely follow what you detail in your summary, eg. - What do you mean by "Application Directory", the Excel.exe folder ? - Start.xls, how is this loaded, in the start-up path? You say "contains...and all my worksheets" Do other workbooks have any links whatsoever to the addin. Apart from the .Run in Start.xls any other links to the addin. I well understand the points/problems you list under "When I originally posted this thread". Confusion abounds about the Addins collection so I'll try and list how Excel populates the collection with some further comments. 1. In Registry *\Excel\Options section, OPEN, OPEN1 etc. These detail installed addins which will load on startup. 2. xla's found in .UserLibraryPath & .LibraryPath (latter for backward compatibility with xl97) 3. xla fullnames in Registry *Excel\Add-in Manager\ These are any other non-installed addins not located in either of the default addin paths. (4. non-installed system addins found by other means) Addin collection names are defined by .Title which defaults. to addin.name if a title does not exist. The Addins collection & Workbooks collection are not directly related. IOW if you unload an installed addin its status in the addins collection does not change (it will load on next Excel startup). If you uninstall an addin, try and install a same name/title addin in a different folder, Excel will look in its addins collection for a same name/title addin. It will find the addin listed in either 2 & 3 above and re-install that. This I think is the original problem you had (if you had not deleted or moved old addin). Even more confusing, if the old & new addin's titles are defined and both same, yet new addin's name & folder location are different, if you programmatically try and install the new addin the old addin may get re-installed (ie if the entry exists in 3 above and the old file is found). Uninstalled addins that are not in a default addin folder may not be visible in Tools Addins in next Excel session (yet remain in the addins collection due to the entry in 3 above). If you uninstall an addin that's not in a default folder, then delete the file, its entry will persist in 3 and in the addins collection. Ideally there ought to be a method to 'Remove' non-installed addins from the addins collection and hence the list in 3. above. But there isn't and this is why problems & confusion occurs. Back to your issue, apart from code in Start.xls to update the addin I don't follow the rest of the set up. Also how does code know that new & old addins are not same (file size/date ?). To summarize, if you are updating addins with slightly different version names and/or folder location, the old addin's details will remain in the addins collection. If the old addin is in a default addin folder it should be removed (not merely renamed). If in a non-default folder you could use the registry code to delete the entry in 3.above. But if everything is identical concerning the addins, ie name, title & intended folder (other than internal code) all you need to do is unload it, remove from folder or Kill it, replace new addin in the same folder, and load it. IOW in this particular scenario absolutely nothing concerning the addins collection or registry entries change, hence registry code not required. Finally, if updating an addin with revised version name and/or folder, any UDF links in other workbooks may also need updating (but from what I gather this is not in your scenario). Regards, Peter T "Trefor" wrote in message ... Peter, Many thanks again for the detailed reply. Perhaps I will summarize what I was trying to do: Start.xls - (contains basic checking code and all my worksheets) (1) Check to see if "<networkdrive\macro.xla" is different to "<localdrive\Application Directory". If different copy off the server. (2) Check to see if "<localdrive\Application Directory" is different to Application.UserLibraryPath. If different copy from "<localdrive\Application Directory" (3) Start.xls then runs code in macro.xla (Run addinname & "!procedurename") and all code for the duration 'session' is then run from macro.xla. (Auto Open/Close are in Start.xls) When I originally posted this thread, the addin was named "macro v1.0.xla" and each upgrade was then distributed as "macro v1.0.xla" for small changes and then "macro v1.1.xla" for more significant changes. I had/have three problems: (1) Although I also load the .xla from Application.UserLibraryPath occasionally the addin loaded said it path was "<localdrive\Application Directory". I am not sure why this was happening, but it just seemed to complicate what I was trying to do. (2) When I distributed the update with no name change I often was not seeing the new file, but the old file. (3) When I distributed the update with a name change this seemed fix the (2) |
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 |