Exclude ComboBox from Exporting to Excel

[qweeqweg]

Hello Friends
Is there a way to prevent my Combobox from exporting out to Excel? My ComboBox is situated in the form Header. I have a button which exports the form to Excel, but the ComboBox ends up as an unwanted column in the Excel spreadsheet. Some guidance on this would be greatly appreciated.

 

[theDBguy]

Hi. Maybe export the source data rather than the form? Just a thought...

 

[qweeqweg]

Hi. Maybe export the source data rather than the form? Just a thought...

How would I do that? I'm currently using DoCmd.OutputTo acOutputForm..

 

[vba_php]

How would I do that? I'm currently using DoCmd.OutputTo acOutputForm..

right click on the table name > EXPORT. or use the ribbon / top menu

 

[theDBguy]

How would I do that? I'm currently using DoCmd.OutputTo acOutputForm..

Yeah, instead of acOutputForm, try acOutputQuery. Is your form based on a query? If based on a table, you could try acOutputTable. If you filter the form before exporting, then you can create a query with the same filter as your form and export that query.

 

[qweeqweg]

right click on the table name > EXPORT. or use the ribbon / top menu

Thanks, but the record source for the form is based on 3 related tables with the combobox providing the filter... so, that wouldn't work.

 

[theDBguy]

Thanks, but the record source for the form is based on 3 related tables with the combobox providing the filter... so, that wouldn't work.

I think it would work. Use the same query you're using for the form in your new query and add the criteria from the combobox. Then, export that query. Wouldn't that work?

 

[qweeqweg]

Yeah, instead of acOutputForm, try acOutputQuery. Is your form based on a query? If based on a table, you could try acOutputTable. If you filter the form before exporting, then you can create a query with the same filter as your form and export that query.

I don't have a query specifically built for the form. I just set the record source.

 

[theDBguy]

I don't have a query specifically built for the form. I just set the record source.

In that case, you can also use code to either use automate Excel and use CopyFromRecordset or create a QueryDef object with the same SQL statement as your form.

 

[qweeqweg]

I think it would work. Use the same query you're using for the form in your new query and add the criteria from the combobox. Then, export that query. Wouldn't that work?

I had thought about that, and I believe that would work,.. but I was hoping for a quicker solution which didn't require that I create a query. I don't like accumulating stacks of queries. I like everything neat and clean and uncluttered, with record source set within each form. Must be an OCD thing.

 

[theDBguy]

I had thought about that, and I believe that would work,.. but I was hoping for a quicker solution which didn't require that I create a query. I don't like accumulating stacks of queries. I like everything neat and clean and uncluttered, with record source set within each form. Must be an OCD thing.

Hi. The "extra" query doesn't have to clutter your database. Create it, export it, and then delete it afterwards. Should be quick enough, no?

 

[qweeqweg]

Hi. The "extra" query doesn't have to clutter your database. Create it, export it, and then delete it afterwards. Should be quick enough, no?

The form is for end users. The Export is triggered by a button on the form, so that the user can export what they are seeing on the screen, should they need to. Im reading up on CopyFromRecordSet now.

 

[theDBguy]

The form is for end users. The Export is triggered by a button on the form, so that the user can export what they are seeing on the screen, should they need to. Im reading up on CopyFromRecordSet now.

Hi. So, the button code should be able to do everything. Shouldn't be a problem then. Let us know how it goes. Cheers!

 

[qweeqweg]

Like you advised.. I created a new query using the form's RecordSource SQL statement, and set the query as the object to be exported. Works great. I do wish I could somehow include a title block for the spreadsheet... but I don't know how to do that.

 

[theDBguy]

Like you advised.. I created a new query using the form's RecordSource SQL statement, and set the query as the object to be exported. Works great. I do wish I could somehow include a title block for the spreadsheet... but I don't know how to do that.

To add a title block (assuming you meant to insert a row at the top), you might have to use Excel Automation.

 

[zeroaccess]

I don't have a query specifically built for the form. I just set the record source.

Open up that recordsource and "Save" the query. Now you can use it.

There is a school of thought that saved queries perform differently (faster?) due to the way Access optimizes queries. Don't know for sure, but saving them to the Nav pane makes it easier to access them.