Excel: Change addin reference without opening a file

Question

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.

In response to another question, I timed the opening and closing of 400 2.4Mb Excel workbooks. It took 188 seconds. For a one-off task, I do not see 3 minutes per 400 workbooks as an overhead worth worrying about. — Tony Dallimore
I'll give it a try but the 2 Addins are not installed in the 2 versions of Excel. So if I open the workbook in 2003, it will not like the 2010 addin and vice versa... — assylias
So you have an Excel 2003 macro that opens the workbooks and removes the old add-in and an Excel 2010 macro that opens the workbooks and adds the new add-in. — Tony Dallimore
I have not written anything yet but to avoid that I thought it might be possible to change the reference to which the workbooks are linked without having to open them... — assylias
@assylias Could you please clarify, are these found in the references section or the addin's section, also not to rain on the parade but there's no way to avoid opening the Excel files in order to change the reference/addin but this opening and closing of the files can be done through VB so it won't be a manual task. — Matt Donnan

Jon49 Jon49 · Accepted Answer · 2012-02-07T20:35:27

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

Excel: Change addin reference without opening a file

1 Answers