MS Access Query Export to Excel

[tread]

SITUATION:

I have an Access Database with one Table: 2.5 MM records (Tickers,
Date, Price) covering 6 years of information.

CHALLENGE:
Export the data to multiple Excel spreadsheets with Ticker, Date, and
Price, but have each day on a separate worksheet.

EFFORTS THUS FAR:
1. create individual queries by day that export data to a workbook,
and have each day on a separate sheet. The problem is creating each
query is time consuming, and once run, the query appears to revert to
a "make table" query.

2. Link an excel worksheet to the database and customize each
worksheet for a particular day. The problem is 1. it's time consuming
to create each custom data filter; 2. it's time consuming to download
the data (roughly 1.5 minutes / page)

QUESTION:
Is there some script or excel link functionality that I can implement
to accomplish the CHALLENGE at hand?

Thanks in advance for your thoughts, and suggestions.

Trevor

 

[[MVP] S.Clark]

You can use VBA to write to Excel, or use Docmd.TransferSpreadsheet to
automate the exports.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[tread]

Thanks for the suggestion Steve, which approach would you suggest
based on simplicity and speed? I somewhat familiar with the Docmd in
Access, but not very familiar with VBA to write to Excel, how would i
attach this?

Thanks for your help.

Trevor

 

[[MVP] S.Clark]

I'd say start with creating a macro, that performs the TransferSpreadsheet
action. Experiment with it, and see how close it gets to fulfilling your
need.

Using VBA to automate Excel isn't for the new and untrained, so I'll guide
you away from that, for now.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-