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.
--
Ken Snell
http://www.accessmvp.com/KDSnell/
"Michael_R" <(E-Mail Removed)> wrote in message
news:5FF25654-CC28-48D4-B096-(E-Mail Removed)...
> 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" wrote:
>
>> 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/EXC...ExportSepFiles
>>
>> --
>>
>> Ken Snell
>> http://www.accessmvp.com/KDSnell/
>>
>>
>>
>>
>> "Michael_R" <(E-Mail Removed)> wrote in message
>> news:21A749E1-7051-443E-8E2D-(E-Mail Removed)...
>> >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.
>>
>>
>> .
>>
Similar Threads
