Automatically load solver reference library

[Greg Snidow]

Greetings. I have a macro that runs solver. Every time I switch between my
work computer (Excel 2003) and my personal one (Excel 2007) the solver
reference library needs to be loaded again. When I first open the reference
list, solver is not an option. If I first run solver using the toolbar
menus, it will then appear in the reference list and I can select it, no
problem until I switch computers. How can I get it to load the library
automatically, depending on which version of Excel I am using? Thank you.

Greg

 

[Gary''s Student]

I had a similar problem:

http://groups.google.com/group/micr...ad/a38d859c231fb626/bea27fdcc6872056?q=solver

 

[Greg Snidow]

Well, I was hopeful when I added " Application.AddIns("Solver
Add-in").Installed = False
Application.AddIns("Solver Add-in").Installed = True", because it went
through the whole routine without throwing up an error message. However, it
no longer gives me an answer, so back to the drawing board.

 

[Peter T]

I recall suggesting that approach to install the ATP addin, but your problem
has nothing to do with installing addins but adding (and removing) a
reference. Try something like this -

' thisworkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim rf As Object ' Reference
Static bExit As Boolean

On Error GoTo errExit
If bExit Then
bExit = False
Exit Sub
Else
bExit = True
Cancel = True
With ThisWorkbook.VBProject.References
On Error Resume Next
Set rf = .Item("SOLVER")
On Error GoTo errExit
If Not rf Is Nothing Then
.Remove rf
End If
End With

ThisWorkbook.Save

AddSolverRef

ThisWorkbook.Saved = True

End If
errExit:

End Sub

Private Sub Workbook_Open()
AddSolverRef
End Sub


The idea is to add the reference in the workbook's open event, remove it
before saving, then re-add it again to carry on working with it. This is
only lightly tested, let us know how you get on transferring it between 2003
& 2007 systems.

Note - you will need to allow access to VBProject in both systems

Regards,
Peter T

 

[Peter T]

Oops, forgot the install routine which is called from both Open and
BeforeSave events

' normal module or thisworkbook module

Sub AddSolverRef()
Dim sFile As String
Dim ai As AddIn

Set ai = AddIns("Solver Add-in")
sFile = ai.FullName
ThisWorkbook.VBProject.References.AddFromFile sFile

End Sub

Peter T