Excel – Import to Excel from Access table based on parameters

excelexcel-2007importms-access

I am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.

The Access table will have three columns:
Part Number|Line|Catalog Code

I would like to import all three columns, but allow the user to select the records imported based on the catalog code.

The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.

I have 2007 versions. Is this type of import possible?

Thanks in advance!

Best Answer

You can do this without VBA, just by using MS Query (works in Office 2010, I can't check against '07):

  • in Excel go to the data ribbon and click the From other sources icon
  • click From Microsoft Query
  • select MS Access Database*
  • browse for and select your database.
  • in the wizard select the columns you need to import
  • on the next page select the column you want to filter on
  • select the type of filter you need, i.e. Equals
  • instead of selecting a value in the next box enter [Parameter please Bob]
  • enter you sort on the next page
  • select Return data to Microsoft Excel and finish

MS Query will ask you for the parameter, then Excel will ask you how and where you want the data. and your filtered data will appear.

Now each time you refresh the data you will be asked the parameter again, so you can easily change it.

p.s. If you don't want a 'programming' answer, ask on superuser.com

Related Topic