Export Access query to multiple Excel worksheets

[tread]

Overview:
I have an Access 2002 database that has a "Prices" table with roughly
1.8 million records. In general, the table includes the following
columns: ID, Stock Ticker, Date, and Price. I have a very simple
query based on a date range that includes: stock ticker, date, and
price.

Objective:
Get an Excel workbook that has a different worksheet for every day
that includes stock ticker, date, and price.

Current process:
Run query, export to Excel through tools or transferspreadsheet
function. Cut and paste each date range onto a new worksheet. The
problem is I need to do multiple years at a time, and if one price is
missing, i essentially have to do the entire year again for the one
stock price.

thanks.

 

[tread]

you're brilliant, works like a charm! Do you think it's possible to
have one query work for multiple dates? i.e.
INTO
[Excel 8.0;Database=C:\Prices.xls].[Sheet20030701]
FROM
Prices
WHERE
Prices=#07/01/2003
OR
INTO
[Excel 8.0;Database=C:\Prices.xls].[Sheet20030702]
FROM
Prices
WHERE
Prices=#07/02/2003

I tried a few different syntaxes, but couldn't get anything to work.
If not, I can easily create a new query for every day, and run them
separately.

Thanks so much for your help!

Trevor

Tread,
Use a SELECT...INTO query. If the specified workbook/worksheet doesn't
exist then Jet will create it. Something like:

SELECT
stock_ticker, date, price
INTO
[Excel 8.0;Database=C:\Prices.xls].[Sheet20030701]
FROM
Prices
WHERE
...

Jamie.

Overview:
I have an Access 2002 database that has a "Prices" table with roughly
1.8 million records. In general, the table includes the following
columns: ID, Stock Ticker, Date, and Price. I have a very simple
query based on a date range that includes: stock ticker, date, and
price.

Objective:
Get an Excel workbook that has a different worksheet for every day
that includes stock ticker, date, and price.

Current process:
Run query, export to Excel through tools or transferspreadsheet
function. Cut and paste each date range onto a new worksheet. The
problem is I need to do multiple years at a time, and if one price is
missing, i essentially have to do the entire year again for the one
stock price.

thanks.