Excel Calculation Not Completing Via VBA

[DLC]

I am creating an Access application that uses an Excel file as its
calculation engine. My basic process is to, from Access, open an
existing Excel file, input some values into predefined cells, retrieve
output values from another set of predefined cells, and then write the
results to a table in Access. The problem I am running into is that
the calculations in the Excel file don't seem to completely run when I
try to do this via VBA. A series of the calculated cells in the Excel
file end up set to #NAME?, and hence my output cells are not complete
either.

After my application runs, I can actually open the Excel file directly
and see all the #NAME? cells. If I then press the F9 key, the cells
calculate correctly. As a further note, Excel is set to
auto-calculate, although I have tried the manual calculation modes as
well. I have tried using the Calculate methods on the Application and
Worksheet objects, I have tried using SendKeys to send an F9, and I
have tried waiting for a CalculationState of xlDone.

Below is a portion of my VBA code. Any help in getting these
calculations to run to completion would be greatly appreciated.
Thanks.

-Don

Option Compare Database
Option Base 1
Option Explicit

Public objExcel As New Excel.Application
Public objWorkbook As Excel.Workbook
Public objInputSheet As Excel.Worksheet
Public objOutputSheet As Excel.Worksheet
Public objModelSheet As Excel.Worksheet

Sub RunSimulation(numRuns As Integer)
' OPEN THE MODEL ENGINE EXCEL FILE
objExcel.Workbooks.Open <MODEL_FILE_PATH_NAME>
Set objWorkbook = GetObject(<MODEL_FILE_PATH_NAME>)

Set objInputSheet = objWorkbook.Worksheets(<INPUT_SHEET_INDEX>)
Set objOutputSheet = objWorkbook.Worksheets(<OUTPUT_SHEET_INDEX>)
Set objModelSheet = objWorkbook.Worksheets(<MODEL_SHEET_INDEX>)

' RUN THE MODEL
Call RunModel()

' CLEAN UP
objWorkbook.Close True
objExcel.Quit
Set objExcel = Nothing

' SHOW THE RESULTS
DoCmd.OpenTable "RESULTS"
End Sub

Sub RunModel()
' INPUT DATA TO EXCEL MODEL
Dim inputArray(37, 3) As Double
<Set the input array data...>

objInputSheet.Range("INPUT") = inputArray

' OUTPUT DATA FROM EXCEL MODEL
<Write results to table...>
For i = 1 To 24
<RESULT> = objOutputSheet.Range("A" & i)
Next i
End Sub

 

[keepITcool]

do you use functions from the Analysis toolpak?

if so b aware that addins are not loaded if you create
an excel instance using automation.

add following line and it should be ok (if i guessed right)..

objExcel.RegisterXLL "analys32.xll"
if it cant find the thing..

then try

Dim objAddin As Excel.AddIn
For Each objAddin In objExcel.AddIns
If UCASE$(objAddin.Name) = "ANALYS32.XLL" Then
objExcel.Workbooks.Open objAddin.FullName
Exit For
End If
Next