Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Linked table (Excel) doesn't work as form

    I linked an Excel file to a table.
    Changing the data in the table works fine.

    But when I use this linked table in a subform (or a simple form), the data isn't entered in the Excel sheet...why?
    The subform behaves normally and I can even ad several records in the subform but they don't transfer to the Excel sheet.

    The subform works correctly because when I enter related information directly in Excel it shows in the subform.

    How do I solve this problem?

    I use the Excel sheet because Excel performs a specific calculation with the data entered, so I need this sheet..


    Thank you for reading and eventually answering this question.
    Michèle

  2. #2
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: Linked table (Excel) doesn't work as form

    [SIZE=1]Originally posted by MichèleW
    I linked an Excel file to a table.
    Changing the data in the table works fine.

    But when I use this linked table in a subform (or a simple form), the data isn't entered in the Excel sheet...why?
    What do you mean exactly by "data isn't entered in the Excel sheet"?

    There is no problem to enter raw data from Access into a linked Excel spreadsheet, but Access has no way to know that in your spreadsheet you have also data that is actually calculated based on other data. Access can see only values, not formulas. When you enter a new record into your spreadsheet from Access, or modify some existing data, how would Access know which formulas should be copied and/or recalculated?

    If you want Excel functionality in Access, instead of using a subform whose data source is a linked Excel sheet, embed directly your spreadsheet on an Access form. (In Form Design View, use Insert >> Object >> Excel Spreadsheet >> Link.) This way, when on the Access form you doubleclick the object that represents an Excel sheet, you will open Excel with that sheet, where you can do all the modifications you need. When you close the sheet, you will be back in Access.

    Another (and a more elegant) solution is to use Excel from Access as an Automation server. This way your users will not even notice they are actually working with an Excel spreadsheet. But, it is not a simple task, and requires writing quite a lot of VBA code and a good knowledge of properties and methods Excel exposes to the outside world. If you don't feel comfortable enough with VBA, try to find some simpler example to start with.

Posting Permissions

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