4

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

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.

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

Improve this question

3 Answers 3

Reset to default
3

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

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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Beat me to it, might want to open the workbook as read-only too
@NickSlash I assumed he needs to change cell values in order to trigger the calculation, so needs to write to the sheet.
The worksheet should still be editable, you're just not able to save the changes. I've not done macros on readOnly workbooks so don't know if it affects calculations or not.
0

To push the data back to Access, you can insert data from within the Excel VBA as follows:

dim val as variant
dim db as DAO.Database

val=thisworkbook.range("a1").value
set db=OpenDatabase("c:\myAccessDB.accdb")
db.execute "insert into patientData (someField) values (" & val & ")",dbFailOnError
db.Close

You'll need to add a reference to the Microsoft Office Access Database Engine Object Library.

Improve this answer

Comments

0

Not sure to perfectly understand what you want, but if you just want to export the results of a query to a spreadsheet, you could use the following:

Private Sub ExportAccessDataToExcel()
    Dim SqlString As String

    SqlString = "CREATE TABLE testMeasurements (TestName TEXT, Status TEXT)"

    DoCmd.RunSQL (SqlString)
    SqlString = "INSERT INTO testMeasurements VALUES('Average Power','PASS')"
    DoCmd.RunSQL (SqlString)
    SqlString = "INSERT INTO testMeasurements VALUES('Power Vs Time','FAIL')"
    DoCmd.RunSQL (SqlString)

    SqlString = "SELECT testMeasurements.TestName, testMeasurements.Status INTO exportToExcel "
    SqlString = SqlString & "FROM testMeasurements "
    SqlString = SqlString & "WHERE (((testMeasurements.TestName)='Average Power'));"

    DoCmd.RunSQL (SqlString)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "exportToExcel", "C:\TestMeasurements.xls", True, "A1:G12"
End Sub

Source: http://www.ehow.com/how_7326712_save-access-query-excel-vba.html

This could be done either directly from the database or from Excel (you would need to open the database with Excel VBA to do so, but most of the Office Suite products interact well with each other).

If you want to push the data of your spreadsheet into an Access database, that's different. You just have to open the database and loop through INSERT query. Here is a quick example, you just need to add the loop:

Dim db as DAO.Database
Set db = OpenDatabase(myDataBase.mdb)
Call db.Execute("INSERT INTO myTable (Field1, Field2) VALUES('Value1', 'Value2')")
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.