CODEWITHSUNDEEP
vbaexcel
assylias
assylias

Reputation: 328785

Excel: Change addin reference without opening a file

I have several (as in 100s) spreadsheets using an addin (let's call it Addin2003). Due to a migration to new versions of Excel, I need some of those spreadsheets to use a new addin (let's call it Addin2010).

Is there an easy way to check and change the references used by those spreadsheets without opening them.

So something like (pseudo code):

For each wbk in aListOfWorkbooks
  If wbk.containsReference("Addin2003") And someOtherCriteria Then
    wbk.removeReference("Addin2003")
    wbk.addReference("Addin2010")
  End If
Next

EDIT

To make it clearer, Excel 2003 and 2010 do not have the same addin loaded, so I want to change some spreadsheets that rely on the 2003 Addin and make them use the 2010 Addin instead. In other words, this would be equivalent to opening the workbook, opening the VBA editor, going to the Tools/References menu and changing the reference from Addin 2003 (which is flagged as MISSING) to Addin 2010. If i do open one of those workbooks in Excel 2010 (which does not have the 2003 Addin), I get various errors due to the fact that Excel can't find the 2003 Addin. That's why I thought it might be easier not to open the workbooks to change their References.

FINAL WORD

It seems that it is not possible to do that and I will have to physically open the files to change the references. Thank you all.

Upvotes: 0

Views: 1943

Answers (1)

Jon49
Jon49

Reputation: 4606

You'll probably want to do something similar to this:

Sub ChangeAddins()

    Dim oAddin As AddIn
    Dim sFullNameOfAddin As String, sThisAddinName As String

    sFullNameOfAddin = ThisWorkbook.Path & "\" & "NewAddin.xlam"
    'Add in name of xla
    sThisAddinName = "Add-in Name"

    If Application.Version > 11 Then
        Set oAddin = AddIns.Add(sFullNameOfAddin, True)
        oAddin.Installed = True
        For Each oAddin In Application.AddIns
            If oAddin.Name = sThisAddinName Then
                If oAddin.Installed Then
                    oAddin.Installed = False
                End If
            End If
        Next oAddin
    End If

End Sub

Upvotes: 2

Related Questions