Import Access data into Excel Spreadsheet

  • Thread starter Thread starter Sitara Lal
  • Start date Start date
S

Sitara Lal

Is there a way to do this without having to write VB code?

I have an Access table which has fields such as Product_ID,
Product_Description, Product_Image etc.

To generate a quotation for a customer in Excel, I would like to create a
worksheet where the user would enter the Product_ID in one cell so that the
Product_Description, Product_Price etc. loads automatically into adjacent
cells on the same row of the Excel worksheet. In other words, the values for
the remaining cells in the row woudl be automatically looked up from the
Access table.

It is necessary to use Excel because only one user in the workgroup has an
Office Pro 2003 license (hence Access) whereas other have only an Office
Small Business or Office XP (and hence do not have Access). The Access .mdb
file would be on a shared folder in the workgroup server, so that all users
may access it (though not necessarily all at the same time). Also, various
calculations need to be done on the data and that would be much easier to
do in Excel.

What is the best way to accomplish this? Looking at the Excel Help online, I
only found a 'copy and paste' solution, which is quite impractical.

Thanks
 
You could use MS Query (Data>Import External Data>New Database Query) to
extract the information from the database.

It allows you to add parameters as criteria. You could enter a product
ID when prompted, or refer to a cell on the worksheet.
 
Thanks, that works great for importing one row of data at a time. However
the procedure is cumbersome if one needs to populate multiple rows of a
spreadsheet, where the parameter will be different for each row.

Is there any way where one could define a link source once and then simply
enter the parameter (e.g, Product_ID) in one cell of the spreadsheet, to
have all adjacent rows populated with the lookup values, without having to
go about redefining the link for each row? Does that make sense?
 
Back
Top