none
Using Excel worksheet in Access RRS feed

  • Question

  • In an Access 2010 database, I would like to provide an Excel style worksheet for users to edit some data.

    So I include an unbound OLE Object that is an Excel worksheet.  It comes in and users can edit data on it.

    However, when it is activated, it shows the Excel menu bar and hides the Access bar.  In the process there is this big jump on the screen as it makes room for its toolbar etc.

    How can I avoid this?  Is there a better way to do this?

    Tuesday, February 2, 2016 5:14 PM

Answers

  • Yes sure. But in the scenario I presented, I need to display it like a crosstab.  So e.g. Division names are being displayed, and Expense descriptions across the top.  However, in storing the updates back to the real tables, I will need to know the division ID and the Expense ID.  Currently, I just save these as offsets in the spreadsheet. 

    So how would I store these in this temp table?  ok, so I would have to dynamically create a table with how ever many columns I need.  and then create a datasheet that only shows the columns I need.  Is this about it?

    Hi. Basically, yes. You would create the temp table in a temp database with a non-normalized structure, so you can display it in datasheet as if it's a crosstab.

    Hope that helps...

    • Marked as answer by serenejen Thursday, February 4, 2016 5:16 PM
    Tuesday, February 2, 2016 7:19 PM
  • Hi serenejen,

    >> no way to make Excel open without jumping around on the screen

    In my option, there is no way to achieve this without jumping around. Have your original issue been resolved? If you have, I suggest you mark the helpful reply as answer.

    >> Will be interesting to see how this works with an Access web database that is linked to sharepoint lists.
    For this new issue, I suggest you post a new thread for this, and then more community members would help you.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by serenejen Thursday, February 4, 2016 5:16 PM
    Thursday, February 4, 2016 6:43 AM

All replies

  • Are the data being edited in an Access table? If so, why use an Excel sheet?

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 2, 2016 5:21 PM
  • No. it is actually a combination of information from multiple tables.  It would take about 3 queries to populate the spreadsheet.  Then after editing, there will be code to traverse the spreadsheet and save information back to the appropriate table.

    Think of something as simple as a crosstab display, where data needs to be entered for expenses in every division.  It would be good to show a grid of divisions down the rows and expenses across.  Then users can just type in quickly, then Save.  Then each cell will be saved back to a table with a row for each expense.

    • Edited by serenejen Tuesday, February 2, 2016 5:28 PM
    Tuesday, February 2, 2016 5:23 PM
  • I would probably try a temp table/database approach rather than a spreadsheet one, but that's just one person's humble opinion. Cheers!
    Tuesday, February 2, 2016 5:34 PM
  • I would probably try a temp table/database approach rather than a spreadsheet one, but that's just one person's humble opinion. Cheers!
    That's exactly what I would do. In fact, I did something similar for employee time sheets.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 2, 2016 5:46 PM
  • ok good.  So where do you store the keys from the original records so you can write the answers back?  And how do you handle the variable columns that result from a crosstab?
    Tuesday, February 2, 2016 5:51 PM
  • ok good.  So where do you store the keys from the original records so you can write the answers back?  And how do you handle the variable columns that result from a crosstab?

    Hi. You said you have code to traverse the spreadsheet. Can you adapt it to go through a table?
    Tuesday, February 2, 2016 6:23 PM
  • Yes sure. But in the scenario I presented, I need to display it like a crosstab.  So e.g. Division names are being displayed, and Expense descriptions across the top.  However, in storing the updates back to the real tables, I will need to know the division ID and the Expense ID.  Currently, I just save these as offsets in the spreadsheet. 

    So how would I store these in this temp table?  ok, so I would have to dynamically create a table with how ever many columns I need.  and then create a datasheet that only shows the columns I need.  Is this about it?

    Tuesday, February 2, 2016 6:31 PM
  • In my case, the columns were always the same. If you would like a sample originally written by A.D. Tejpal, a real master of pushing what Access can do, download it from my OneDrive here.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, February 2, 2016 7:01 PM
  • Yes sure. But in the scenario I presented, I need to display it like a crosstab.  So e.g. Division names are being displayed, and Expense descriptions across the top.  However, in storing the updates back to the real tables, I will need to know the division ID and the Expense ID.  Currently, I just save these as offsets in the spreadsheet. 

    So how would I store these in this temp table?  ok, so I would have to dynamically create a table with how ever many columns I need.  and then create a datasheet that only shows the columns I need.  Is this about it?

    Hi. Basically, yes. You would create the temp table in a temp database with a non-normalized structure, so you can display it in datasheet as if it's a crosstab.

    Hope that helps...

    • Marked as answer by serenejen Thursday, February 4, 2016 5:16 PM
    Tuesday, February 2, 2016 7:19 PM
  • ok thank you. will take a look.
    Tuesday, February 2, 2016 11:37 PM
  • ok yes I can do this.  Will be interesting to see how this works with an Access web database that is linked to sharepoint lists.

    Will let you know how it turns out.

    So then, no way to make Excel open without jumping around on the screen eh?

    Tuesday, February 2, 2016 11:48 PM
  • Hi serenejen,

    >> no way to make Excel open without jumping around on the screen

    In my option, there is no way to achieve this without jumping around. Have your original issue been resolved? If you have, I suggest you mark the helpful reply as answer.

    >> Will be interesting to see how this works with an Access web database that is linked to sharepoint lists.
    For this new issue, I suggest you post a new thread for this, and then more community members would help you.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by serenejen Thursday, February 4, 2016 5:16 PM
    Thursday, February 4, 2016 6:43 AM