Populate specific cells in Excel from an Access Query

[laina222]

I have an Excel workbook and an Access database in question.

I built the Access database in order to replace the Excel workbook, but now the user only wants to use Excel to populate the data. I know that there's a way to do this by linking tables to the spreadsheet, only I only found ways to do it BEFORE the database is created.

Also, there is a report that I created in Access to calculate certain values that the user now wants to run in Access and have those values automatically populate in Excel.

So I need to know how to have the user update the tables in Excel, send the data over to Access and then the user wants to click on a report button and have the report values sent over to the Excel document.

Is this an absolute ridiculous request, or is it possible? I would LOVE help, and thanks in advance to anyone who can!

 

[nickdel]

Hi Iaina22,

Within Access, select the Table objects/menu. Within here right click and select link tables. Change "Files of type" to Excel and then simply select the spreadsheet that the user will be updating. Now whenever they make changes to the spreadsheet you will be able to access this via Access.

Now for your report, I would simply do this the other way round. i.e. Use Excel and "import data" (Data Menu) from the Access database; Data > Import External Data > Import Data. Depending on what version of Office, you can select the source to be an Access query instead of just a table. Failing that, have your report query populate a table within Access and then link Excel to this table as described above.

Hope this helps

Nick