CODEWITHSUNDEEP
excelms-access
Chris Hammond
Chris Hammond

Reputation: 41

Use Excel spreadsheet from within Access

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

Upvotes: 4

Views: 1307

Answers (3)

dan
dan

Reputation: 3519

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')")

Upvotes: 0

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 3

sigil
sigil

Reputation: 9546

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.

Upvotes: 0

Related Questions