santoshpc
11-13-2011, 08:24 AM
I've faced this problem several times, especially when it comes to deployment, i.e. when you want to release your excel file to multiple users, running on different office versions and you want your macro to just work!
I searched for a couple of hours yesterday and finally found a few articles (one from this forum itself) that I combined to make a perfect solution.
I wanted to put up this post to help out others like me and save people some valuable time, which can be used to actually write the macros rather than worry about such issues. Also, I felt that I should give something back to this forum, without which I couldn't have got my file up and working.
I'll demonstrating this with the two most common addins that people deal with: Solver & Analysis, but the code can be easily modified for other addins as well.
Sub AddinsNReferences()
On Error Resume Next
'First install the add-in on Excel
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
AddIns("Solver Add-in").Installed = True
Dim i As Long
Dim flpth As String
Dim theRef As Variant
'All the possible references to the project for the add-ins. XLAM works for 2007/2010 & the others work for the earlier versions.
'The reference files' locations differ based on where the office/excel has been installed. Therefore, I've first determined the Excel location and then I'm modifying it to add the reference.
For i = 1 To ThisWorkbook.VBProject.References.Count
With ThisWorkbook.VBProject.References(i)
If Trim(UCase(.Name)) = "EXCEL" Then
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\Analysis\ATPVBAEN.XLA"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\Analysis\ATPVBAEN.XLAM"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\SOLVER\SOLVER.XLA"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\SOLVER\SOLVER.XLAM"
ThisWorkbook.VBProject.References.AddFromFile flpth
Exit For
End If
End With
Next i
'Now we remove the extra references that we added earlier that belong to other versions of office/excel
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
On Error GoTo 0
End Sub
If you found my post useful, post something to let me know :)
I searched for a couple of hours yesterday and finally found a few articles (one from this forum itself) that I combined to make a perfect solution.
I wanted to put up this post to help out others like me and save people some valuable time, which can be used to actually write the macros rather than worry about such issues. Also, I felt that I should give something back to this forum, without which I couldn't have got my file up and working.
I'll demonstrating this with the two most common addins that people deal with: Solver & Analysis, but the code can be easily modified for other addins as well.
Sub AddinsNReferences()
On Error Resume Next
'First install the add-in on Excel
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
AddIns("Solver Add-in").Installed = True
Dim i As Long
Dim flpth As String
Dim theRef As Variant
'All the possible references to the project for the add-ins. XLAM works for 2007/2010 & the others work for the earlier versions.
'The reference files' locations differ based on where the office/excel has been installed. Therefore, I've first determined the Excel location and then I'm modifying it to add the reference.
For i = 1 To ThisWorkbook.VBProject.References.Count
With ThisWorkbook.VBProject.References(i)
If Trim(UCase(.Name)) = "EXCEL" Then
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\Analysis\ATPVBAEN.XLA"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\Analysis\ATPVBAEN.XLAM"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\SOLVER\SOLVER.XLA"
ThisWorkbook.VBProject.References.AddFromFile flpth
flpth = Left(.FullPath, Len(.FullPath) - 9) + "Library\SOLVER\SOLVER.XLAM"
ThisWorkbook.VBProject.References.AddFromFile flpth
Exit For
End If
End With
Next i
'Now we remove the extra references that we added earlier that belong to other versions of office/excel
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i
On Error GoTo 0
End Sub
If you found my post useful, post something to let me know :)