Loading and unloading xla AddIn (Excel 2000\SR 1)

[Jim Bassett]

Below is code I am using to load an Excel AddIn: It fails when it attempts to use the passed variable "name" in the line: ' Get reference to desired AddIn
Set addXLA = Excel.AddIns(name) giving me an error message of "Run time error 9" Subcript out of range. I have checked the name of the "name" variable ans it is correct. What am I doing wrong and also does it look like I have the load and unload setup correctly?

Thanks




Option Explicit

' The following function loads an addin if it is not already loaded
' Note the passed parameter name is the name of the AddIn minus the extension
' The passed parameter path is the path, including the drive of where
' the addin is located
Public Function LoadAddIn(ByVal name As String, ByVal path As String) As Boolean

Dim addXLA As Excel.AddIn
Dim fullpath As String

' Create the full path to the AddIn
fullpath = path + name + ".xla"
' Get reference to desired AddIn
Set addXLA = Excel.AddIns(name)

If Err <> 0 Then
Err.Clear
'See if AddIn is already in collection. If not add it
Set addXLA = Nothing
Set addXLA = Excel.AddIns.Add(fullpath)
If Err <> 0 Then
'If there was error loading desired Addn the exit
LoadAddIn = False
Set addXLA = Nothing
Exit Function
End If
End If

'Load the AddIn
If Not addXLA.Installed Then
addXLA.Installed = True
End If
Set addXLA = Nothing

End Function


' The following function unloads an addin
' Note the passed parameter name is the name of the AddIn minus the extension
' The passed parameter path is the path, including the drive of where
' the addin is located
Public Function UnLoadAddIn(ByVal name As String, path As String) As Boolean

Dim addXLA As Excel.AddIn
Dim fullpath As String

' Create the full path to the AddIn
fullpath = path + name + ".xla"

' Get reference to desired AddIn
Set addXLA = Excel.AddIns(name)

If Err <> 0 Then
Err.Clear
'See if AddIn is in collection. If so remove it
Set addXLA = Nothing
Set addXLA = Excel.AddIns.Add(fullpath)
If Err <> 0 Then
'If there was error loading desired Addn the exit
UnLoadAddIn = False
Exit Function
End If
End If

'UnLoad the AddIn
If addXLA.Installed Then
addXLA.Installed = False
End If


End Function

[LegareColeman]

If you make an empty worksheet the active sheet and run the code below, do you get a name in column A that matches the string being passed in "name"?

<pre>Dim oAddin As AddIn
Dim I As Long
I = 0
For Each oAddin In AddIns
ActiveSheet.Range("A1").Offset(I, 0).Value = oAddin.Name
I = I + 1
Next oAddin
</pre>

[pieterse]

This seems to work OK for me:

Public Function LoadAddIn(ByVal name As String, ByVal path As String) As Boolean
Dim addXLA As Workbook
Dim fullpath As String
On Error Resume Next
LoadAddIn = True
' Create the full path to the AddIn
fullpath = path + name + ".xla"
' Get reference to desired AddIn
Set addXLA = Workbooks(name & ".xla")
If addXLA Is Nothing Then
Err.Clear
With AddIns.Add(Filename:=fullpath)
.Installed = True
End With
If Err <> 0 Then
LoadAddIn = False
Exit Function
End If
End If
Set addXLA = Nothing
End Function

Note that the AddIns collection expects the NAME of the addin as it is listed in the Addins listing (to qualify what addin you mean) and not the FILENAME of the add-in!
I therefore changed that part using the Workbooks collection.