#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    Question OLE Automation Excel


    I posted a previous thread which asked how to use the Solver add in included in excel in the VB6 environemnt in order to solve an equation with 3 unkowns. In short, I use solver to minimize the sum of the squares.

    In thinking about the problem I think I can do this by openning an excel workbook in VB which has the solver included as a recorded macro. I would then dump my data onto the excel workbook and use a shortcut key to launch the solver macro. I am unsure if this will work or how to do this ==> Open an existing file and type the shortcut key in excel without the user being aware of it. Any suggestions would be appreciated.

    If you know how to call the solver add in in Excel from a VB6 application that would do as well.

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Melbourne, Australia
    Posts
    212
    Rep Power
    12
    If you write the solver macro, you should be able to "call" the function from your code.

    Code:
    i.e.
    call SolverLaunchMacro
    How can I soar like an eagle when
    I'm flying with turkey's?
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    Ole clarification


    I have this macro saved in personal.xls which from my understanding is loaded in the background everytime excel is launched.

    If I launch Excel via:

    PHP Code:
    Set objXl CreateObject("Excel.Sheet"
    Set objXl objXl.Application.ActiveWorkbook.ActiveSheet 
    I am assuming I can run this Macro since it should be saved and loaded unto my current instance of Excel. How would I call it from my VB environment? ie Call objxl.Solver_Mac

    PHP Code:
    Sub Solver_Mac()

    SolverOk SetCell:="$F$1"MaxMinVal:=2ValueOf:="0"ByChange:= 
         
    "$E$1,$E$2,$E$3"
        
    SolverSolve userfinish:=True
      solverfinish keepfinal
    :=1
    End Sub 
    Thank You
    Last edited by ofunkym; June 10th, 2003 at 08:01 AM.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    5
    Rep Power
    0

    OLE answer


    Thanks for the help I was able to call the macro by saving it in personal.xls and using the line:

    PHP Code:
    objXl.Application.Run ("personal.xls!Solver_Mac"
    All is well.

IMN logo majestic logo threadwatch logo seochat tools logo