Export Query to .xlsx file and set name based on Combo-box value

[alecs0101]

Hi,

Consider you would have to extract daily reports showing orders received by department (consider 5 departments) and you would need to export them to files named after each department (Dep1.xlsx, Dep2.xlsx, and so on).

How I solved this was to have a query for each department and also create an export button for each query (date is taken from a text box). This way each button runs the query for a particular department and then exports it to Dept_name.xlsx

So 6 queries & 6 buttons....this solution is very basic to say the least.

A nicer solution would be to have a single query and set the parameters via a text box (for the date) and a combo-box (for the department name).
This I have done, also quite easy.

The question would be, can you then export the resulting query (say you select Dep.1 in the combo-box) to an Excel file and have it's name determined from the department selected (Report "Dep1".xlsx)?
This would help a lot since I can have a single query, a single button, and in case department lists change, I's just have to add it and not go through the whole process of creating deleting a query and a button.....

Thanks a lot

[twinnyfo]

alecs0101,

Yes, this is very simple to do. For the name of the file, just use the column in your combo box that corresponds to the department. For exampl,e if you combo box row source lists the dept ID and the dept Name, Me.cboDept.Column(0) = the ID and Me.cboDept.Column(1) = the Department name.

Incorporate that value into the file name and you should be good to go. Sounds like you have a pretty good grasp of many of the details, but if you need help, we are standing by.

[alecs0101]

:) I have a logical grasp but am barely a beginner with access...functions and VBA ....brrr.... works only after first documenting and seeing examples from others ... and then not always

Now comes the "dumb/noobish" question, how/where can I incorporate --- Me."combo_box_name".Column(1)the file name --- in the file name? :)

I presume that in order for you to answer, you would first need some extra details....if so please let me know what and I'll spit it all out :)

Thanks

[twinnyfo]

So, the first question is to ask how you were exporting the queries previously? Did you have a form that exported them? Were you doing this manually?

I need to know how far along you are in the porcess before guiding further.

Don't worry about being a noob, as I was a noob at one time, too. Just keep at it, and you will get the hang of things.

[alecs0101]

Considering there are 4 departments...current solution is:
- there is one query that returns the date for each department in turn (total 4 queries)
- on the form there is a text box where one selects the date/interval for which they want to view the data (included in the criteria of all 4 queries)
- there are four buttons (one for each department) linked to the respective query and the text box
- buttons are set to export the query results to an excel file
- as each button connected to a different query, it was set up to save it directly (no prompts) based on the query...

The concern is that:
1. it is rather a stone age solution...or basic at least
2. if departments say add up, having +4 queries and +4 buttons seems really stupid :)

So the idea is to merge all queries into 1, I know how to do that - discovered rather (by adding a combo box with all departments).
Problem is that now, upon exporting I have to manually write the name of the file - basically "Report + dep. name".

Would like if possible for the report to be saved and name to include the dep. selected in the combo box

[twinnyfo]

Again, see my post #2.

If your code is building the string for the report name, just use hte combo box instead of whatever method you had for typing it in yourself (I don't know how you are doing this, as you don't mention).

Example:

strFileName = "My Report - " & _
    Me.cboDept.Column(1) & _
    " - " & strDate & ".xlsx"

This assumees that you have created a string for the date format you desire.

What you are trying to do seems to be rather straightforward and I use this methodology all the time. I think once you understand the concepts, you will be able to use them in all aspects of your projects.

[NeoPa]

It would help us to see the code you are using currently Alecs.

You describe quite well (which is really great) but on this occasion we need to see your existing code to know where to start explaining from.