Access output to Excel 1

[mvpdep]

Good morning, I have scrolled through many posts and i have found some information but nothing to exactly match what I am looking to do.

Could anyone advise me as to where on the web i could go (free tutorial) to learn more about outputting my data in access to an excel spreadsheet.

More importantly I would like to be able to transplant data into one workbook and three separate worksheets.

Is this possible? If so how is it done. Where could I go to learn more about this.

For example, Workbook is called Test and worksheets are Company, Cities, Sales.

Ideally I am looking to pull all of the company names from access and put them into sheet one, all the cities into sheet two and all the sales figures into sheet three.

I know there is someone out there that can help thank you in advance.

 

[PHV]

Why not having a QueryTable in each sheet ?
When in Excel menu Data -> External data -> Create a query ...
The spelling may be different as I don't play with an english version of msoffice.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tigerlili3]

The response time on getting external data from excel is not always the greatest. This may be faster...

You could export your tables/queries using the transfer spreadsheet action. This can be done through an Access macro or through vba, whichever you prefer. If you're using a macro, you can list a transfer spreadsheet action in the grid for each of the tables/queries that you want to output. In vba you would write a line of code in a module like this:

Sub OutputToTest()
DoCmd.TransferSpreadsheet acExport, 8, "tblCompany", "C:\Test.xls", True, ""
DoCmd.TransferSpreadsheet acExport, 8, "tblCities", "C:\Test.xls", True, ""
DoCmd.TransferSpreadsheet acExport, 8, "tblSales", "C:\Test.xls", True, ""
End Sub

You'll obviously have to replace the table names I've used with table/query names from your database, and enter the correct path to your excel workbook.

After you export the first time, you will have to rename the sheets in Excel to Companies, Cities, and Sales, but after that you'll be golden. The sheets will keep their names and the data will go to the correct place when exported.