Linking Access Form with Excel Worksheet

[pbien]

I am trying to determine if I can link fields on an Access form with related fields on an Excel worksheet, have Excel automatically do some calculations and then have the results linked back to my form.

I have been doing some research on linking Access and Excel but I am still not sure now automatic this process can be.

Here is more detail about what I want to do (Access and Excel files attached,[attachment=87295:Bmi.zip][attachment=87296:BMICalculator.zip]). I want to add data to the frmBMI except for the BMI and Percentile fields. I want the data to automatically link to row 5 in the measurements worksheet in Excel. Excel would them calculate BMI and Percentile and then link the results back to my Access form. I could then save the data, clear the fields and add data for a new student and repeat the process.

How automatic can this process be?

What the code to at least get me started?

Thanks.

Paul

[HansV]

It's possible to link to an Excel sheet in an Access database, but the present layout of the Excel sheet isn't entirely suitable for that.
Moreover, a linked Excel sheet cannot be updated from Access, so you'd still need code to transfer data from your Access form to the Excel sheet.

I would approach it as follows:

- Convert the reference tables in the hidden sheet into one or more tables in the Access database.
- Convert the formulas used in the Excel workbook to expressions in a query in the database.
- If the expressions become too complicated, you could create user-defined VBA functions.

Implementing this will take time but it's not impossible; it's beyond the scope of what the Lounge can offer though (in my opinion).

[pbien]

Thanks Hans.

I was just trying to find out what was possible with an Access-Excel link. I'll explore you suggestions a little more but I think it is going to be too much for me to figure out.

Paul