How to send form contents to MS Excel?

[jarekz]

Hi guys,

I have a tabular form that contains header section and detail section. The header section is used for searching and applying filters. The header section is not bound to any table in the database. Basically, users can enter their search criteria in the header section and see results in the details section.

How can I send the results (so only the fields from details section) to MS Excel? I can't use DoCmd.TransferSpreadsheet, because this method can only be based on a table or a query and not on a form. When I use OutputTo method it sends to Excel also the contents of the header section, which I absolutely don't want.

Thanks for any help!

[jaxjagfan]

Hi guys,

I have a tabular form that contains header section and detail section. The header section is used for searching and applying filters. The header section is not bound to any table in the database. Basically, users can enter their search criteria in the header section and see results in the details section.

How can I send the results (so only the fields from details section) to MS Excel? I can't use DoCmd.TransferSpreadsheet, because this method can only be based on a table or a query and not on a form. When I use OutputTo method it sends to Excel also the contents of the header section, which I absolutely don't want.

Thanks for any help!

Make a query that uses your header as its criteria and then use the TransferSpreadsheet method to export the query.

[jarekz]

Make a query that uses your header as its criteria and then use the TransferSpreadsheet method to export the query.

Thanks, but some of the fields in the header are using filters. How do I use filters in a query?

[jaxjagfan]

Thanks, but some of the fields in the header are using filters. How do I use filters in a query?

What type of controls are you using in the header (TextBoxes, ComboBoxes, ToggleButtons, CheckBoxes, etc)? What are the names of these controls? What is your data table name and field names?

A simplistic view of possible SQL.
Select MyTable1.MyField1 from MyTable1 Where MyField1 = [txtHeader1];

If you use the QBE grid to build you query and use the builder and select forms. It will display the names of your controls to choose from. In my sample above I would have had a textbox control named "txtHeader1".

[jarekz]

Thanks for your reply, jaxjagfan.
All controls in my header are text boxes. Some of them contain texts, some numbers and some dates.
I see one problem with the query that you posted. Not all search criteria fields are always filled in. What if txtHeader1 is empty and only txtHeader2 is filled in? The query will not work...

[jarekz]

Why is it so complicate? Isn't it possible to just send the current recordset of the form after the users have chosen their search criteria?