Access to Excel filtered

[Michael_R]

I have an Access table that holds sales data for customers by months by
product (key = Customer + YYYYMM + product)

How can I retrieve all records from this table into Excel where the required
customer and month are defined in Excel?

In other words:
1) In Excel I say: show all products for customer x, August 2009
2) Access reads the filter from Excel and delivers the selected data to
Excel.

 

[Ken Snell]

Where in the EXCEL file are you storing this "filter" information?

What if you could just export the data from ACCESS, filtered by the various
combinations of customer and month/date, and let the data be put into
separate EXCEL workbooks? See this article:


Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles

 

[Michael_R]

Ken,

Thanks for your proposal, here is why I think that it does not exactly fit
my requirements.

Because i know my ways around Excel far better than in Access I decided to
maintain my slowly growing database in Excel rather than Access.

Record selection is done via some fancy Excel functions; records are taken
from the Table sheet and copied into an Extract sheet. Performance of this
function is the bottleneck that i want to improve - and I thought moving the
data into Access could be a step in the right direction.
A typical selection extracts less than 2% of all records. That's why I think
that your proposal to extract all records and spread them over multiple
extract sheets will not improve my performance issue.

The selection criteria (customer, month) are entered in 2 cells in the top
(header) part of the Extract sheet. The resulting records are then displayed
underneath the header.

Based on the records in the Extract sheet other Excel workbooks perform
further analysis.

 

[Ken Snell]

You'd need to use Automation to open the EXCEL file, read the cells' values,
close the EXCEL file, use the cells' values as filters in a query that you
run in ACCESS, save that query in ACCESS, export the query from ACCESS to
EXCEL (this can be done in many ways, depending upon which cell is the first
one into which the data are to be written, so probably use Automation to do
this too), and delete the query from ACCESS.

The web page link I gave you also takes you to a page where I show how to
use Automation to open EXCEL and read data from specific cells in the EXCEL
worksheet. Also, I have a page that shows how to use Automation to export
data to EXCEL.

The other actions related to query creation/saving/deletion are fairly
straightforward actions for VBA in ACCESS.

Take a look at the examples, and then post back with questions that you may
have.