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
|