Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically load an object library in real time | Excel Programming | |||
Cannot Load Library "LXBCPRP.dll using defualts error 126 | Excel Discussion (Misc queries) | |||
Reference Library - Missing Library in a lower version. | Excel Programming | |||
Solver automatically in the VBA Reference | Excel Programming | |||
access the solver reference library | Excel Programming |