Linking Access and Excel

[CMDennis]

I wrote a database that keeps track of all corporate expenditures (capital and expense) and will track them by month until 2008. My problem is that I would like to report the data in a spreadsheet format. I have 6 different queries that generate data. I need to have those queries "feed" a spreadsheet, but the spreadsheet needs to keep the same format (headings, formulas, spacing, etc.) and the spreadsheet needs to update along with the database. Is this possible, or am I just out of luck???
Thanks in advance.

-Chris

 

[Rohdem]

You can link an excel spreadsheet to an Access database, and then use it like any other table when running queries and code.

If you right click in the database window, there will be an option to "link tables". Select excel as the file type and the wizard will walk you through linking the spreadsheet. You need to have already created the spreadsheet with the headings in the first row.

Another option would be to write some code that uses the 'Transfertext' command to export a table into a spreadsheet.

A third option would be to manually export a table or query to an excel file. Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[tlbroadbent]

Another option is to Get External Data in Excel. You can add the data to an Excel spreadsheet, Add formulas, formatting and so on. When you refresh the data, the formats, column widths and formulas will remain. Sub totals will be lost when data is refreshed. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin