Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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).

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •