Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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>

    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •