i am trying to export data from a subform to the excel sheet.....if i do it using comand button wiht macro built in..it is just exporting the data from the main from and not from the subform....please anyone help me with this ...and it is very urgent.
Change my mind - despite the help files saying that you can not specify a range on an export - you can with TransferSpreadsheet . You just have to make sure that the Range defined is large enough to hold your recordset(see below).
This example uses a Workbook (c:\Book1.xls) that has two worksheets. The name of the worksheet doesn't matter when using ranges. Each worksheet has its own Range "MainForm" and "SubForm"
"MainForm" range =Sheet1!$A$1:$C$4
=Sheet1!$A$1:$C$4 is receiving an export of three fields.
The range must be equal in the number of cells to the number of fields, but will shrink or grow depending on the number of rows.
What I have done in the code below is grab the recordsource for the forms and append to a temp table that is created on the fly. Then transfer the table to the spreadsheet's range, and finally - delete the temp table and start over with the form's subform.
Function TransferToExcel(strTable As String, strFileName As String, strRange As String) As Boolean