Export To Excel 2

[Waynest]

Hi

I want a quick way to export the contents of a sql server (7) table to an excel spreadsheet by clicking on a command button. Is docmd.TransferDatabase the way to go?
If so, could someone paste an example of the syntax.

Thanks very much
Wayne

 

[neecie]

Look in Access help for the OutputTo Action. It is used with the DoCmdobject.
Example:

DoCmd.OutputTo acOutputTable, Tablename, acFormatXLS, Excelfilename, AutoStart, TemplateFile

 

[AJParkinson]

I've got a further problem to this scenario, wonder if you can help? :

I've got the DoCmd.OutputTo command on a button on a form, and yes it seems to work fine on the surface, ie no errors thrown up & on looking in the output directory, the xls file has been created - great - until you open the xls file to find it contains no data!

One of two possibilities?:

1) I've got it all wrong
or
2) The report I'm outputting (which works perfectly as an adp report) contains nothing in the 'detail' section but has a few sum totals in various group/report footers - and the only thing output to the xls file would be data in the detail section of the report.

Can anybody tell me which, and if it is the second possibilty - how do I get round it?

 

[misscrf]

Im doing something similar, but I have a different problem. I used the DoCmd.TransferSpreadsheet acExport, and it works good, I think. Thing is, it doesnt overwrite the excel spreadsheet that it exports too, and I need it to. The query it exports has date parameters, and if the user chooses start as 02/01/04 and end as 08/04/04, but later chooses 05/01/04 as the end, the 08s will still be there.

Any suggestions?

Thanks!


misscrf

Management is doing things right, leadership is doing the right things

 

[AJParkinson]

Had answer there in point 2 - I created a second report specifically for output to excel (ie no formatting required to view adp report) and moved all the fields to the detail section.

Exports fine and I can then pull data from this spreadsheet into a pivot table or format it other ways that I wish.

...and misscrf, try the output to method it overwrites the file each time.

 

[misscrf]

Well, I got the transferspreadsheet to work. Now I have a new issue.

Instead of specifying the name of the workbook to save the spreadsheet to, I have been asked to prompt the save as dialog box and have the user choose their own path and workbook, or name it. They can choose to overwrite or not to.

Any suggestions?


misscrf

Management is doing things right, leadership is doing the right things

 

[AJParkinson]

Hi misscrf, I've not done much in the way of transfering data to excel from access before but I'll have a look (after our month end processes are out of the way)

 

[misscrf]

Thanks, but I got it all figured out. I wrote an FAQ on it, so please feel free to take a look if you are interested.

faq68-5428

I thought it might help some others out there.


misscrf

Management is doing things right, leadership is doing the right things