Automatically load solver reference library
 

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

Automatically load solver reference library
 

I had a similar problem:

http://groups.google.com/group/micro...72056?q=solver
--
Gary''s Student - gsnu200905


"Greg Snidow" wrote:

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

Automatically load solver reference library
 

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.

"Gary''s Student" wrote:

I had a similar problem:

http://groups.google.com/group/micro...72056?q=solver
--
Gary''s Student - gsnu200905


"Greg Snidow" wrote:

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

Automatically load solver reference library
 

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

"Greg Snidow" wrote in message
...
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.

"Gary''s Student" wrote:

I had a similar problem:

http://groups.google.com/group/micro...72056?q=solver
--
Gary''s Student - gsnu200905


"Greg Snidow" wrote:

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

Automatically load solver reference library
 

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


"Peter T" <peter_t@discussions wrote in message
...
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

"Greg Snidow" wrote in message
...
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.

"Gary''s Student" wrote:

I had a similar problem:

http://groups.google.com/group/micro...72056?q=solver
--
Gary''s Student - gsnu200905


"Greg Snidow" wrote:

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