Export to Excel from unbound subform datasheet?

[dnme]

Hi,

I am wondering if anyone can help me here. I have an Access 2003 adp project. In it I have a search form/subform. The form provides controls for the user to select search criteria, the subform is a datasheet that displays the search results. I have designed this as an unbound subform, I set it's recordset property in VBA code.

However, I am finding that I cannot export this datasheet to excel, nor can I print it. These functions only seem to work for bound forms. My subform datasheet has the columns ordered in a certain way (across the top) and some columns are hidden so I would really like to be able to export it (DoCmd.OutputTo) rather than copy trhe underlying recordset to excel which outputs all fields in wronmg order etc.

So is there a way to export and print unbound forms / datasheets? I'm really stumped on this.

[June7]

Open Excel workbook and then read value of control on form and save to cell of worksheet - one field and record at a time.

[dnme]

Open Excel workbook and then read value of control on form and save to cell of worksheet - one field and record at a time.

Many thanks for the reply.

But I'm wondering if this is workable say if I have 50000+ results in my recordset returned? Could you provide sample code as to how I would iterate through each column on a datasheet?

Many thanks

[June7]

Don't have anything specific to your situation. Start with a review of this http://forums.aspfree.com/microsoft-...el-414974.html

Instead of a recordset you could read each form control by name, save to cell of worksheet, move to next record of form and repeat. What you should get out of the links is how to open a workbook object and how to use loop code to change cell reference. I don't know how long 50,000 records would take with this method, maybe quite a while. Alternative is to build a query that replicates the dataset of the subform and export the query. You want the hidden fields data to export as well?

[dnme]

No I do not want to export hidden fields, It works great when the subform datasheet is bound. I can then use DoCmd.OutputTo, but once I unbind the subform, I am stumped. Thanks for your help anyway.

[June7]

It is possible to do export by method I describe but 50000 records could be very slow. I just don't know if 'slow' would be 10 minutes or 10 hours. You don't want the hidden data then ignore those textboxes.

Why unbound form? Doesn't it become bound by setting the RecordSource property in VBA? How do you populate the controls?

[dnme]

Its unbound for a number of reasons, firstly the underlying SQL Server has a stored proc whick takes many optional parameters, this is the search part. I have now included an output parameter in it which gives me back the record count (this is the accurate way to do this as subform recordset counts are not reliable). So I need to consume an output param, therefore I cannot use form binding, rather I need to code my sql string and parameters into a command object, execute that and then I can access the returned output parameter. I also want to clear the subform (no records) when the user presses the clear search button etc etc.

[Norbert]

Hi,
I created an MS Access add-in called 'A2EE.mda' (which stands for Access To Excel Exporter) and it is to be downloaded on:
<old invalid link removed>
It is intended right for exporting data from subforms to Excel sheets.
I wonder if this add-in can solve your dilemma?
Will you try and let us know?
BR
Norbert