Import an Excel macro into a database

[DiscombobulatedDuck7]

Hi,


sorry for asking another question so shortly. I'm confronted with a rather complex problem that I haven't dealt with before. Basically, I have an Excel macro that calculates and outputs a bunch of medical data. Now I want to integrate it into my database where I store patient information and their examination data. The macro or rather VBA module should take the exam data I am about to enter in the database and return the results just like it did in the Excel spreadsheet.


I have attached the relevant files and hope someone can point me in the right direction.


Note: I had the rename the Excel file extension from "xlsm" to "xls" so it was accepted.

 

[Uncle Gizmo]

I think you should consider asking a different question.

I would prefer to see example data and an example of the results you want.

This approach is more likely to arrive at a better/best solution, and use MS Access features which may not be available in Excel.

I haven't looked at your files because from past experience it usually just muddies the water.

 

[arnelgp]

you can use all your Calculation on your
Excel workbook. (i named it test_calc.xlsm).

see the code behind "Show Result" button
on frm_Patients_Exam.

note that I linked (linked table)
sheet3 of the Workbook.

after extracting all files, open
the db, and re-link (External Data, Link Table Manager) the xlResult (linked table)
to the proper path.

 

[DiscombobulatedDuck7]

you can use all your Calculation on your
Excel workbook. (i named it test_calc.xlsm).

see the code behind "Show Result" button
on frm_Patients_Exam.

note that I linked (linked table)
sheet3 of the Workbook.

after extracting all files, open
the db, and re-link (External Data, Link Table Manager) the xlResult (linked table)
to the proper path.

Thanks a bunch. This definitely looks like a winner.



Is it possible to integrate the calculated results into the Examinations subform so they show up automatically whenever I go to the next record instead of having to push the "Show result" button repeatedly?

 

[arnelgp]

its been so long but ive manage to convert it to pure access.

 

[DiscombobulatedDuck7]

its been so long but ive manage to convert it to pure access.

Thanks, arnelgp. I really appreciate you taking the time to do this.


Since I am no VBA-expert, a few things still confuse me. For example, in "subfrm_Examinations" you set a timer interval (TimerInterval = 600). What is the purpose of this function?


Since everything has been imported into the database I assume I can safely remove any Excel-related object in the code behind? Like "modExcelintance" and basically every sub in "frm_Patients_Exam".

 

[arnelgp]

the purpose of the timer is to create a record in tbl_result.
i am saving the result to this table and linked to subform exam.

you keep the modExcelInstance.
i am stull using excel fir the functuon NirmDist.