Use Excel spreadsheet from within Access -

i have excel spreadsheet calculates risk (of perioperative mortality after aneurysm repair) based on various test results.

the user inputs test results spreadsheet (into cells) , out comes set of figures (about 6 results) various models predict mortality. spreadsheet acts complex function produce results 1 patient @ time.

i have (separate) access database holding data on multiple patients - including data on test results go spreadsheet. @ moment have manually input data spreadsheet, results out , manually enter them onto database.

is there way of doing automatically. ie can export data1, data2, data3... access spreadsheet cells data needs input , results (result1, result2, result3...) cells results displayed ported access.

ideally done live.

i suppose try program functionality of spreadheet complex function in access, if i'm honest, not sure how algorithm in spreadsheet works. designed anaesthetists cleverer me....

hope makes sense. appreciated.

chris hammond

it's possible automate excel access.

const cstrfile string = "c:\somefolder\foo.xls" dim xlapp object dim xlwrkbk object dim xlwrkst object  set xlapp = createobject("excel.application") xlapp.workbooks.open cstrfile, readonly:=true set xlwrkbk = xlapp.workbooks(1) set xlwrkst = xlwrkbk.worksheets(1) xlwrkst     .range("a1") = 2     .range("a2") = 19     debug.print .range("a3") end xlwrkbk.close savechanges:=false 

however, seems cumbersome repeat each row of access table , i'm uncertain whether doing live reasonable.

i try adapt excel calculations access vba functions , use custom functions in access query. don't know how big of task be. suggest shouldn't scared off the anaesthetists' cleverness; doesn't mean know more vba you. @ least see whether can tackle it.