在Access中使用Excel电子表格 [英] Use Excel spreadsheet from within Access

问题描述

我有一个Excel电子表格，根据各种测试结果计算出风险（围手术期死亡率）。

I have an Excel Spreadsheet that calculates a risk (of perioperative mortality after aneurysm repair) based on various test results.

用户将测试结果输入到电子表格中（进入细胞），然后出来预测死亡率的各种模型的一组数字（约6个结果）。电子表格作为一个复杂的功能，一次可以产生一个病人的结果。

The user inputs the test results into the spreadsheet (into cells) and then out comes a set of figures (about 6 results) for the various models that predict mortality. The spreadsheet acts as a complex function to produce the results one patient at a time.

我还有一个（单独的）访问数据库，用于存储多个患者的数据，包括所有关于进入电子​​表格的测试结果的数据。目前我必须手动将这些数据输入到电子表格中，得到结果，然后手动将其输入数据库。

I also have a (separate) access database holding data on multiple patients - including all the data on test results that go into the spreadsheet. At the moment I have to manually input this data into the spreadsheet, get the results out and then manually enter them onto the database.

有没有办法自动执行。那么我可以将data1，data2，data3 ...从Access中导出到电子表格到需要输入数据的单元格，然后从显示结果的单元格中获取结果（result1，result2，result3 ...）

Is there a way of doing this automatically. Ie can I export data1, data2, data3... from Access into the spreadsheet to the cells where the data needs to be input and then get the results (result1, result2, result3...) from the cells where the results are displayed ported back into access.

理想情况下，这可以直播。

Ideally this could be done live.

我想我可以尝试编程电子表格的功能在访问中成为一个复杂的功能，但如果我老实说，我不太确定电子表格中的算法如何工作。它是由麻醉师设计的，比我更聪明。

I suppose I could try to program the functionality of the spreadheet into a complex function in access, but if I'm honest, I am not really sure how the algorithm in the spreadsheet works. It was designed by anaesthetists who are much cleverer than me....

希望这是有道理的。任何帮助非常感谢。

Hope this makes sense. Any help much appreciated.

Chris Hammond

Chris Hammond

推荐答案

从Access自动化Excel。

It's possible to automate Excel from Access.

Const cstrFile As String = "C:\SomeFolder\foo.xls"
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSt As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open cstrFile, ReadOnly:=True
Set xlWrkBk = xlApp.Workbooks(1)
Set xlWrkSt = xlWrkBk.Worksheets(1)
With xlWrkSt
    .Range("A1") = 2
    .Range("A2") = 19
    Debug.Print .Range("A3")
End With
xlWrkBk.Close SaveChanges:=False

然而，对于Access表的每一行来说，这似乎很麻烦，我不知道这样做是否合理。

However, that seems like it would be cumbersome to repeat for each row of an Access table and I'm uncertain whether doing that live is reasonable.

我会尝试使Excel计算适应Access VBA函数并使用这些自定义功能一个Access查询中的离子。但我不知道一个任务会有多大。我建议你不要害怕麻醉师的聪明才智;这并不意味着他们实际上比你更了解VBA。至少看看你能否处理它。

I would try to adapt the Excel calculations to Access VBA functions and use those custom functions in an Access query. But I don't know how big of a task that would be. I suggest you shouldn't be scared off the the anaesthetists' cleverness; that doesn't mean they actually know much more about VBA than you. At least look to see whether you can tackle it.

这篇关于在Access中使用Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！