Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation Not Completing Via VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation Not Completing Via VBA
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 -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam DLC wrote in message . com: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hidden cells expanding when completing calculation | Excel Worksheet Functions | |||
Macro NOT completing | Excel Discussion (Misc queries) | |||
Completing a Chart | Charts and Charting in Excel | |||
Completing Formula in Vba | Excel Discussion (Misc queries) | |||
Need some help completing this Formula | Excel Worksheet Functions |