[AccessD] Run Excel Solver from Access

Sample rough code to run function Least Common Multiple of Integers or 
bond yield
from solver pack, finicial pack is similar

'sample call
'?fLCM(24,36)
'
Function fLCM(intA As Integer, intB As Integer) As Integer
'Least Common Multiple of Integers
'set a reference to Excel object library
    Dim objXL As Excel.Application
    Dim strText As String
    Dim blnCheck As Boolean
    Set objXL = New Excel.Application
    strText = objXL.Application.LibraryPath
   
    Debug.Print strText
    With objXL
   
    blnCheck = .RegisterXLL(.Application.LibraryPath & "\solver\solver.dLL")
    Debug.Print blnCheck

     'If .AddIns("Analysis Toolpak").Installed Then
     .Workbooks.Open (objXL.Application.LibraryPath & _
                          "\Analysis\atpvbaen.xla")
     .Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
     fLCM = .Application.Run("atpvbaen.xla!lcm", intA, intB)
      '    Else
         
         '  .Workbooks.VBProject.References.AddFromFile 
Filename:=obj.Application.LibraryPath & "\Analysis\atpvbaen.xla"
     'fLCM = 0
      'MsgBox "Can't Find Analysis Toolpak atpvbaen.xla"
      'End If
    End With
    objXL.Quit
    Set objXL = Nothing
End Function

Here is another for bond yield

Function fXLYield(dtmSettlement As Date, dtmMaturity As Date, dblRate
As Double, dblPR As Double, dblRedemption As Double, bytFrequency As
Byte, bytBasis As Byte) As Double
'   Uses the Excel function YIELD to return the yield on a security
that pays periodic interest
'   Accepts:
'   dtmSettlement - The security's settlement date
'   dtmMaturity - The security's maturity date
'   dblRate - The security's annual coupon rate
'   dblPR - The security's price per $100 face value
'   dblRedemption - The security's redemption value per $100 face
value
'   bytFrequency - the frequency of the payments per year
'   bytBasis - The type of day count to use
'   Returns:
'   The yield
    On Error GoTo E_Handle
    Dim objXL As Excel.Application
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (objXL.Application.LibraryPath &
"\Analysis\atpvbaen.xla")
    objXL.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
    fXLYield = objXL.Application.Run("atpvbaen.xla!yield",
dtmSettlement, dtmMaturity, dblRate, dblPR, dblRedemption,
bytFrequency, bytBasis)
fExit:
    objXL.Quit
    Set objXL = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " &
Err.Number
    Resume fExit
End Function


Kaup, Chester wrote:

>I need to run the solver add-in in Excel from Access. I have some code
>but how do I pass values to the function and then bring them back to
>access? Below is what I have so far.
>
>Dim Obj As Excel.Application
>
>Set Obj = CreateObject("Excel.Application")
>
>Obj.Workbooks.Open (Obj.Application.LibraryPath & "\SOLVER\solver.xla")
>
> 
>
>Then something like this?
>
>Obj.Application.Run("solver.xla",???????????)
>
> 
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada