I have a VB6/VBA application that hooks into Excel and loads a workbook. It has been working great for many years. We have now upgraded to Excel 2010 and have ran into some issues. After troubleshooting it seems that if i turn off the PowerPivot COM Add-In the process is able to run as it did before with no issues. While I look for the exact cause of this i wanted to see if i can turn off that Add-In just for my app. I load up Excel like this:
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
On a test Excel workbook I have this code to list the add-ins. However only "Excel Add-Ins" are the only ones that get listed. "COM Add-Ins" do not get listed.
Sub ListAddIns()
Dim CurrAddin As Excel.AddIn
Dim r As Long
Dim ws As Excel.Worksheet
Set ws = ActiveSheet
Cells.Select
Selection.ClearContents
Range("A1").Select
r = 1
For Each CurrAddin In Excel.Application.AddIns
ws.Cells(r, 1).Value = CurrAddin.FullName
ws.Cells(r, 2).Value = CurrAddin.Installed
ws.Cells(r, 3).Value = CurrAddin.Name
ws.Cells(r, 4).Value = CurrAddin.Path
ws.Cells(r, 5).Value = CurrAddin.progID
ws.Cells(r, 6).Value = CurrAddin.CLSID
r = r + 1
Next CurrAddin
MsgBox "Its done.", vbInformation
End Sub
After I find a way to reference the COM Add-In I need to keep it from loading in the Excel object in my app. Any help or suggestions welcome.
Thanks
I don't know if there is a "pretty" way to achieve this, but a "dirty" way would be to change the registry settings for the add-in before you start Excel so it won't be loaded. This can be done by setting HKCU\Software\Microsoft\Office\Excel\AddIns\\LoadBehavior to 0 (don't load automatically).
However, this is something you probably shouldn't do unless you are sure that the user accepts it, so be sure to ask the user if he agrees to this change.
You were very close with your code, the way to go is something like this:
You can use the Connect property to load and unload a COM addin.
I have the same problem but there's an easier solution. I Just turn the Powerpivot add-in off from the excel(untick it) and turn it on again...problem fixed.