Export data daily to Excel

[Guest]

Hello,

I have a query set up to combine all of the data I need in an Excel
spreadsheet. This data needs to be put into Excel to then be imported into
SAP. The layout is complete for the data that can change. What is the easiest
way to create a new excel spreadsheet every day with header information and
new data that was entered only?

I have the query set up to only pull entries from the current day. The data
from Access needs to start in cell C6 in Excel. I would also like to know if
it is possible to create the header information in Excel from Access while
exporting the data.

Thanks all for any help...
~MATT

 

[John Nurick]

Hi Matt,

Since you need to put header information into particular cells and then
start the table at cell C6, the built-in TransferSpreadsheet routine
won't help. Basically there are two ways to go.

One is to use cunning SQL queries that write data into individual cells
or specified ranges on the worksheet. Jamie Collins is the expert on
this; if you go to http://groups.google.com/advanced_group_search and
search this newsgroup for his messages you'll see what I mean.

The other is to use Automation to have VBA code running in Access create
the Excel workbook and put the header information and data where they
belongs. This article explains what can be done:
http://support.microsoft.com/default.aspx?scid=kb;en-us;247412 . It's
written for Visual Basic, but almost all of it applies to Access VBA.
For the header, I'd do what it describes as "transferring data cell by
cell"; for the query, I'd use the CopyFromRecordset method.

 

[Guest]

Thanks John!

More than I could have hoped for...

 

[Captain Oh!No!]

Matt,
I import data using the following menu options in Excel:
Data/Get External Data/New Database Query/MS Access
Database/
Find the database, then select the query you want to use
and choose the fields from the query. (Excel will prompt
you for all of these)

If you want a new spreadsheet every day, rather than
overwriting the previous data, then save the spreadsheet
each day with a different name, thereby preserving your
historical spreadsheets and providing a spreadsheet in
which you can overwrite existing data.

The next time you want to run the query from Excel, just
choose Data/Refresh


My spreadsheet has a header, also. I refresh my data in
aprox Row 6 of the excel spreasheet without a problem.

As far as populating with only new data, you may need a
field in your table that determines new data from old like
a date field. Looking at my own database, if I wanted to
do what you are doing and i didn't have a date field, I
would have to use my Primary Key field since they are
AutoNumbers, and I would have to know, the last number I
imported.

Someone else may have a better suggestion on that.

Captain Oh!No!