Best way to export/transfer data into Excel?

[RMcCallan]

This is more of a discussion than a question, I know how to export data etc but was wondering other people's opinions on which way is the best to do it?

I'm currently working on a reporting database and have been advised to use the 'TransferSpreadsheet' method but which way do you guys do it?

Thanks,
Rebecca

 

[lameid]

I use TransferSpreadsheet.... The short answer is TransferSpreadsheet is the best way to get data into Excel unless you need something more than it allows.

If I want something more than a basic datagrid, I will then open that file using Excel automation and have it do what I want like pivot the data.

If you wanted something really complex in Excel that was not worth coding, I would make an Excel template that uses the data however I wanted. I would then link a sheet to the excel file in some specific path. When I wanted to run the 'Excel report', I would copy the template to the specific path and use an append query to write the data. Finally move the file to wherever the 'report' belongs. (I have not done this but I have more or less done the same thing with a database file to use a 'temp'table).

Excel users might also suggest linking to the Access database or using VBA to load the data using recordsets... Recordsets are probably the slowest way to put data in Excel. Linking as the issue of Excel griping about not being able to find the data source. There are probably Excel solutions for this, but I try to stay out of Excel

Something to let users know upfront, it is a lot easier to put data in Excel than to get it out. Excel is good for a lot of things but transfering data into a database is not one of them... Although from what I saw of Access 2007, I think they have made this easier.

 

[ClydeData]

This will send table or query to named spreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourTableOrQuery", "C:\YourFolder\YourSpreadsheet.xls"


This here
DoCmd.OutputTo acOutputQuery, "YourQuery", acFormatXLS

will output prompting the user for where to save

Jimmy