Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OutputTo vs TransferSpreadsheet 1

Status
Not open for further replies.

ryDen

Programmer
Jun 6, 2002
71
AP
Hi,

I am trying to export the result of an access query to excel. I have read somewhere that the TransferSpreadsheet method is faster, but it seems that the filesize of the resulting workbook is smaller when I use OutputTo. However, I have no clue what is the difference between these two methods. Would appreciate if someone could enlighten me on this. Also, which would be better for exporting a query resultset of around 40,000 records to excel?

Thanks in advance.

Regards,
Russ
 
There are lots of differences, but here are some possibly relevant ones:

- OutputTo is output-only. TransferSpreadsheet does import, export or link.

- OutputTo supports formats like RTF, TXT, HTML, SNP, XLS, etc. TransferSpreadsheet looks for Excel of Lotus 123 (on my install of Access at least).

- TransferSpreadsheet lets you optinoally specify whether or not the first row of the exported spreadsheet should contain field names. This is a mandatory "yes" usign OutputTo.

- OutputTo does some rudimentary formatting to the spreadsheet, e.g. auto-width of columns, shading of column headings. TransferSpreadsheet doesn't do this.

-TransferSpreadsheet creates a named range in the resultant spreadsheet, OutputTo doesn't.

Hope this helps you make up your mind which is best for you. As to which is faster/most efficient, I'd try some tests - basic way would be to set up a macro that does 10 OutputTo's of large tables, then MsgBox at the end. Time this from clicking 'Run' to the MsgBox. Then change the macro to do 10 TransferSpreadsheet's and time that.



[pc2]
 

I also use TransferSpreadsheet and the excel file is of 40MB fat. When opened and saved, size reduces to 16MB. Don 't know why but any answer would be valuable and appriciated too....
 
Another easy way to do this is get the 'Analyze with Excel' Icon from View - Toolbars - Customize - Commands - Tools and add it to your Toolbar. Bring up the query and press the button.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top