Export Multiple Queries to Excel Through Combo Box

[bliffer]

My GoogleFu is failing me big time on this one but here is the basic idea of what I would like to do:

I have a form with a combo box (cboChainSelect) that has a list of three character chain codes in it. I would like to be able to select multiple chain codes in the combo box and then click a button that would run one query for each selected chain code using the chain code in a where clause and then export it to Excel using the chain code as part of a file name [ChainCode] + "impact.xlsx"

I haven't come close to finding anything like this through Google - I get inundated with articles about how to export multiple queries to the same workbook. Anyone have any good links or ideas?

 

[dhookom]

The first step if you want multi-select is to change your combo box to a list box. Do you have any code so far?

Duane
Hook'D on Access
MS Access MVP

 

[bliffer]

Sorry, list box is what I meant. I always misreference those two.

At this point I'm not sure where to start. The vast majority of articles I have found deal with passing multiple parameters into a query from a list box. But what I want to do is loop through and pass a single criteria from the list box into mulitple queries. I'm better on the SQL/query side of Access than I am at the VBA side so at this point I'm not sure where to start. I was hoping there would be a good FAQ or article somewhere that could give me the basics.

 

[dhookom]

The article should suggest how to loop through the selected items in the list box.

As you loop through the selected items, your code would:
- Change the SQL property of a saved query based on the chain code faq701-7433
- Create a file name based on the chain code
- Export the saved query to the file name


Duane
Hook'D on Access
MS Access MVP

 

[bliffer]

Thanks. I did some preliminary Googling and found some good stuff on looping. I think I can piece something together. As I said, VBA isn't my strong suit but if I can find snippets of existing code I can usually tinker and get them to do what I want (or close!)

 

[dhookom]

Make sure you come back with a question (and your current code) if you get stuck in coding.

Duane
Hook'D on Access
MS Access MVP

 

[bliffer]

So, I'm starting to rethink this one because as I find out more about what I've been asked to do, it doesn't really look feasible to use a list box based on the number of individual values to be selected. At times there may be upwards of 100 values and no one is going to want to select that with a list box. What I am thinking now is:

Query1: This would be the query to be exported into individual Excel files. It's a very simple query along the lines of "SELECT * FROM Table2 WHERE CHAINCODE = "[SomeValue]"

Table1: This would hold the CHAINCODE values that would go into a WHERE CHAINCODE = "Table1" clause of the query one at a time.

What I would like to do is automate it so that when I run the VBA/Macro/whatever, it would use Table1 as a reference and create one Excel file for each value of CHAINCODE in Table1 and name it "CHAINCODE-GenericFileName.xlsx". My problem is that it is a bear to find anything on this because most of the stuff I've found is for exporting queries into multiple sheets in one workbook.

 

[dhookom]

I'm not sure I follow. An alternative to a multi-select list box is a separate table with the unique codes and a yes/no selected field. Users and check the codes they want included. Then rather than loop through the selected items in the list box, loop through a recordset of the the separate table where the selected yes/no field is Yes.

Duane
Hook'D on Access
MS Access MVP