export form data to excel

[slimjen]

Hi All; using 2010. I have a form with a subform. There are a great deal of calculated fields on the main form mostly calculated from the subform. Is it possible to export unbound access form data included calculated fields to excel? I never had to do this but have a need for this now. I know I can do the calculations in a query and export this but is there a way to have the users do this?
Thanks

[ranman256]

you export a query.
the query has the form items.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, vTabName

[slimjen]

Yes; but I have to create the query first and recreate the calculations correct?

[ssanfu]

Is it possible to export unbound access form data included calculated fields to excel?

Yes, but you have to use automation.
This involves VBA; Ken Snell has examples of Importing from and Exporting to Excel.
The site is http://www.accessmvp.com/kdsnell/EXC...ExpCopyFromRst
There is a section sub titled Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)
This section shows how to use VBA to export a recordset; you use the same concept (looping to move cells), but modify the code to loop through the unbound controls on your main form.
YOU are responsible to ensure all data is written, looping to data in the correct cells.

[June7]

I just tested exporting with the wizard an UNBOUND form with textbox that has expression as ControlSource. That worked. TransferSpreadsheet doesn't work with forms and reports, only tables and queries. So only other option is as described by Steve.

Can have a button that exports query using TransferSpreadsheet. Users don't have to know it's not really the form that is exported.