HI, many questions have been asked, but I still cannot get a simple practical method of exporting a table to a Excel spreadsheet. My table consist of
ItemID - Client - ClientType - Item Description - Qty Sold - UnitPrice . If I can get hold of a working sample in Access 2003, is very much appreciated.

Hi,

Have you tried using the OutputTo method? For example:

DoCmd.OutputTo acOutputTable, "TableName", acFormatXLS, "C:\FolderName\Filename.xls"

Or, the TransferSpreadsheet method? For example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableName", "C:\FolderName\FileName.xls"

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi DBguy, nice to hear from you again. Thanks , will do as stated.

Hi,

(IMG:style_emoticons/default/yw.gif)

Good luck with your project.

Hi DBguy,

The codes you gave me work with tables, how do you export a query? I tried to change the tablename to a query, it does not work. Is there a code for query?

Hi,

No, the codes should have worked for queries too. Did you get any error?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi DBguy, great to hear from you. Yes, it works. I was desperate to get it right I sent another post and Mr.Peter46 taught me how.It is woking now.
The problem was the query, I had zero divided by zero in the calculations that produced #errors.
Since I have you the guru with me, may I post you these questions

1. I need to transfer my query to the Sales Dept on a daily basis. The path at the end of the code allows me to transfer to the same file which overwrites the previous one.
How do I transfer to another file, which do not over-write the previous one?
2. Can I transfer to a standard Excel template, with standard formatted columns, rows etc? So I need not do house-keeping?
Really appreciate if you can help me on this. Thanks again DBguy.

Hi,

Glad to hear you got it to work now.

1. One way to save to a different file each time is to add the date and time in the filename.

2. The only way I can think of to format the exported file is to use Excel Automation instead of the OutputTo or TransferSpreadsheet methods.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Thanks DBguy. How do I get hold of a sample of "Excel Automation"?

Hi,


Here's a quick sample from The Access Web.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Thanks DBguy, will sit back and study it slowly as my access skill is limited.

Hi,

No sweat! Let us know if you get stuck.

Good luck...