Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Hi all
I want to load and reference solver in a spreadsheet if its not already referenced/opened, but I am having some problems. Here's my code below. I find that SolverInstall runs ok, but if Solver is not referenced already from VBA or is MISSING, then SolverAutoOpen fails with the project not referenced. I have to save the workbook after running SolverInstall and then close/reopen it for solver to be referenced properly. I am running the SolveIt macro below from a Vb6 application. Can anyone help with a foolproof way to get Solver always referenced and loaded first time (from vb6)? Thanks Andrew Dim sh1 as worksheet Sub SolveIt() SolverInstall SolverAutoOpen sh1.Activate SolverOk SetCell:="$K$63", MaxMinVal:=2, ValueOf:="0", ByChange:="$K$54:$K$61" SolverSolve UserFinish:=True End Sub Sub SolverInstall() Dim wb As Workbook On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With wb.VBProject.References .Remove .Item("SOLVER") End With With AddIns("Solver Add-In") .Installed = False .Installed = True wb.VBProject.References.AddFromFile .FullName End With End Sub Sub SolverAutoOpen() If Not SOLVER.AutoOpened Then SOLVER.Auto_open End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
did you try putting the install code in the workbook_open event, rather than
trying to install it in the same routine where you are trying to use it. -- Regards, Tom Ogilvy "CodeMonkey" wrote: Hi all I want to load and reference solver in a spreadsheet if its not already referenced/opened, but I am having some problems. Here's my code below. I find that SolverInstall runs ok, but if Solver is not referenced already from VBA or is MISSING, then SolverAutoOpen fails with the project not referenced. I have to save the workbook after running SolverInstall and then close/reopen it for solver to be referenced properly. I am running the SolveIt macro below from a Vb6 application. Can anyone help with a foolproof way to get Solver always referenced and loaded first time (from vb6)? Thanks Andrew Dim sh1 as worksheet Sub SolveIt() SolverInstall SolverAutoOpen sh1.Activate SolverOk SetCell:="$K$63", MaxMinVal:=2, ValueOf:="0", ByChange:="$K$54:$K$61" SolverSolve UserFinish:=True End Sub Sub SolverInstall() Dim wb As Workbook On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With wb.VBProject.References .Remove .Item("SOLVER") End With With AddIns("Solver Add-In") .Installed = False .Installed = True wb.VBProject.References.AddFromFile .FullName End With End Sub Sub SolverAutoOpen() If Not SOLVER.AutoOpened Then SOLVER.Auto_open End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Tom,
that's exactly what I tried. I added the SolverInstall Sub to the Workbook_Open event and it didn't help. Any more ideas? Cheers Andrew |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
SolverAutoOpen ' Calls your routine
Hi. Just a guess. Instead of calling you routine here, I'd be curious if you just skipped it. I think the AutoOpened flag has a bug left over from who knows how many versions. I've never seen any code within Solver that calls it. When Solver opens, it deletes a small internal sheet, and then rebuilds it, and sets the flag. ( I don't think "If Not SOLVER.AutoOpened then" would ever be executed) With vb6, I'd be curious to learn if you just set a reference to the correct workbook, and than reset it again with Solver.Auto_open. Sub SolveIt() SolverInstall SOLVER.Auto_open As a side note, in a Min problem, value of 0 is ignored. SolverOk SetCell:="$K$63", MaxMinVal:=2, ByChange:="$K$54:$K$61" Again, I'd be curious for feedback. :) -- HTH. :) Dana DeLouis Windows XP, Office 2003 "CodeMonkey" wrote in message ups.com... Tom, that's exactly what I tried. I added the SolverInstall Sub to the Workbook_Open event and it didn't help. Any more ideas? Cheers Andrew |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Hi Dana
I just tried your suggestion and no luck :( VB doesn't understand what SOLVER is and gives me the error message "Compile error: Can't find project or library" I have now found a workaround, which isn't elegant but seems to work. I open the workbook programmatically from my vb6 app and then I execute SolverInstall. I then save the workbook and close it. I then reopen the workbook and execute SolverAutoOpen and run SolverOK and SolverSolve. The key here is that I rebuilding the reference to Solver, saving the workbook and closing it, then reopening it to run solver. I don't know how else to do guarantee that solver is loaded. Any input from you and Tom appreciated. Thanks Andrew |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
open the workbook programmatically from my vb6 app
With AddIns("Solver Add-In") .Installed = False Hi. Just a guess. I don't have Vb6, but I wonder if there needs to be some reference to Excel when doing this. The Solver code you have probably works on a vba module. From vb6, maybe we need to set a reference to Excel also. From vb6, maybe you have something like this... Dim XLApp As Excel.Application ....etc Maybe it needs something like this. I don't know. ?? With XLApp .AddIns("Solver Add-In") ...etc Again, just a guess. -- HTH. :) Dana DeLouis Windows XP, Office 2003 "CodeMonkey" wrote in message oups.com... Hi Dana I just tried your suggestion and no luck :( VB doesn't understand what SOLVER is and gives me the error message "Compile error: Can't find project or library" I have now found a workaround, which isn't elegant but seems to work. I open the workbook programmatically from my vb6 app and then I execute SolverInstall. I then save the workbook and close it. I then reopen the workbook and execute SolverAutoOpen and run SolverOK and SolverSolve. The key here is that I rebuilding the reference to Solver, saving the workbook and closing it, then reopening it to run solver. I don't know how else to do guarantee that solver is loaded. Any input from you and Tom appreciated. Thanks Andrew |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
I know there's some disagreement about this, but I have never had success
with referencing Solver the "official" way. Below is what I posted last week to someone who wanted to programmatically set a reference to Solver: It can be done. I won't tell you how, because it will bring you no joy. This advice is the result of hours of trial and error, mostly error, and fruitless searching of Microsoft and Frontline Systems help. Later versions of Excel not only have the macro protection but also protect the VBA project from changes, and this means the ability to set references. "Enable macros" is one thing, but it's never a good idea to rely on the user to muck around with VBA protection. What you need to do is make sure Solver is installed on the computer, then run Solver's Auto_Open procedure to initialize it, then use Solver. All calls to Solver should be made using Application.Run, to avoid needing the reference. When opening the workbook check that the following function CheckSolver is true, which checks that the Solver add-in is installed, then initializes the add-in: ''================================================ ========================== Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' © 2004 J. Peltier, Peltier Technical Services. Dim bSolverInstalled As Boolean If gbDebug Then Debug.Print Now, "NewCheckSolver" '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not WORK.", vbCritical, sTITLE CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "solver.xla!SOLVER.Solver2.Auto_open" End If On Error GoTo 0 End Function ''================================================ ========================== The following shows the sequence you'd use to run Solver with Application.Run: ''================================================ ========================== ' reset Application.Run "solver.xla!SolverReset" ' set up new analysis Application.Run "solver.xla!SolverOk", "Blah1", 1, , "BlahBlah1" ' add constraints Application.Run "solver.xla!SolverAdd", "Blah2", 3, 0 Application.Run "solver.xla!SolverAdd", "Blah3", 2, "BlahBlah3" ' run the analysis Result = Application.Run("solver.xla!SolvSolve", True) ' report on success of analysis If Result <= 3 Then ' Result = 0, Solution found, optimality and constraints satisfied ' Result = 1, Converged, constraints satisfied ' Result = 2, Cannot improve, constraints satisfied ' Result = 3, Stopped at maximum iterations MsgBox "Solution Found", vbInformation, sTITLE Else ' Result = 4, Solver did not converge ' Result = 5, No feasible solution Beep MsgBox "No solution was found.", vbExclamation, sTITLE End If ''================================================ ========================== - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CodeMonkey" wrote in message oups.com... Hi all I want to load and reference solver in a spreadsheet if its not already referenced/opened, but I am having some problems. Here's my code below. I find that SolverInstall runs ok, but if Solver is not referenced already from VBA or is MISSING, then SolverAutoOpen fails with the project not referenced. I have to save the workbook after running SolverInstall and then close/reopen it for solver to be referenced properly. I am running the SolveIt macro below from a Vb6 application. Can anyone help with a foolproof way to get Solver always referenced and loaded first time (from vb6)? Thanks Andrew Dim sh1 as worksheet Sub SolveIt() SolverInstall SolverAutoOpen sh1.Activate SolverOk SetCell:="$K$63", MaxMinVal:=2, ValueOf:="0", ByChange:="$K$54:$K$61" SolverSolve UserFinish:=True End Sub Sub SolverInstall() Dim wb As Workbook On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With wb.VBProject.References .Remove .Item("SOLVER") End With With AddIns("Solver Add-In") .Installed = False .Installed = True wb.VBProject.References.AddFromFile .FullName End With End Sub Sub SolverAutoOpen() If Not SOLVER.AutoOpened Then SOLVER.Auto_open End If End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Dana
thanks for the response. However the code is actually executing within the excel workbook itself, rather than the Vb6 application. The vb6 application is just opening the workbook and then telling the macro to execute. In fact, if I take the excel workbook itself and forget about the vb6 application and then move it to a machine where the solver.xla is in a different location thus breaking the link to the reference, I find that I must run my SolverInstall macro (originally from one of your postings btw!), then save the workbook, close it and reopen it in order for the reference to be restored. Just running SolverInstall and then trying to invoke or even AutoOpen Solver will *not* work (project/library not found error) until the workbook has been saved and reopened. For now I am stuck with my work-around, but thanks for replying. Cheers Andrew |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Thanks Jon. I saw your an earlier post with your code in and the use of
Application.Run was going to be my next port of call. However my workaround of setting the reference using my SolverInstall routine (thanks Dana) saving quiting and restarting seems to work at the moment. Cheers Andrew |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Hi. Ok. Just another wild guess... Does your workbook name have any
spaces in it? There are a few things that won't work with Solver if the workbook name has a space in it. Also, make sure the workbook is in A1 notation, and not R1C1 notation. Again...just wild guesses. What version of Excel are you using? -- HTH. :) Dana DeLouis Windows XP, Office 2003 "CodeMonkey" wrote in message ups.com... Dana thanks for the response. However the code is actually executing within the excel workbook itself, rather than the Vb6 application. The vb6 application is just opening the workbook and then telling the macro to execute. In fact, if I take the excel workbook itself and forget about the vb6 application and then move it to a machine where the solver.xla is in a different location thus breaking the link to the reference, I find that I must run my SolverInstall macro (originally from one of your postings btw!), then save the workbook, close it and reopen it in order for the reference to be restored. Just running SolverInstall and then trying to invoke or even AutoOpen Solver will *not* work (project/library not found error) until the workbook has been saved and reopened. For now I am stuck with my work-around, but thanks for replying. Cheers Andrew |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Whatever works, right?
- Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CodeMonkey" wrote in message oups.com... Thanks Jon. I saw your an earlier post with your code in and the use of Application.Run was going to be my next port of call. However my workaround of setting the reference using my SolverInstall routine (thanks Dana) saving quiting and restarting seems to work at the moment. Cheers Andrew |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing and loading Solver programatically - how?
Actually, the utility I was working on that led me to Application.Run was a
widely distributed workbook that had to be able to be used by people on different versions of Excel. If it's only a small group of users on one version, then twiddling with the reference may be appropriate. Another constraint is the fact that the VBA Project is protected in recent versions of Excel and you can't get to it via code (oh sure, trust the users to change that when they can barely double click on the icon to start the program). So App.Run was the only feasible option. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Jon Peltier" wrote in message ... Whatever works, right? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "CodeMonkey" wrote in message oups.com... Thanks Jon. I saw your an earlier post with your code in and the use of Application.Run was going to be my next port of call. However my workaround of setting the reference using my SolverInstall routine (thanks Dana) saving quiting and restarting seems to work at the moment. Cheers Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver model loading problem | Excel Discussion (Misc queries) | |||
referencing a different tab by referencing a list in the current s | Excel Worksheet Functions | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Programatically access Excel's Solver? | Excel Programming | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming |