access vba to run query and create folder to export data into.

[iP_123]

Hi.
I manually run a monthly access query (not a make table query) that produces 10 query results that I in-turn copy and paste each results into an excel workbook in a specified folder. I paste all the results of the query into five separate workbooks in the current month folder but on different tabs (two data tabs in each workbook)...
Is there a way to have a vba in access that will run the query, then create a folder each month like ((01) - Jan Sales Report) and export the query results data into 5 separate workbooks in this folder eg. (WorkBook1, WorkBook2, WorkBook3 etc).
I currently use MS Access 2013 but will soon upgrade to 2016.

I very much appreciate the assistance.
ip_123

 

[Micron]

Yes, but it certainly would be more than a few lines of code. What's your proficiency with Access vba?
Some things aren't clear, at least to me, so a simplified answer is the best I can do (besides the yes part).
You'd need a procedure that either transfers the data using the built in TransferSpreadsheet function, or loop through a recordset and copy to Excel via Automation.
In addition, your procedure will need the msoFileDialogFolderPicker to select the target folder.

For automation, see http://www.accessmvp.com/kdsnell/EXCEL_Export.htm#WriteRstFile for example.

 

[iP_123]

Thank you Micron. I am not proficient in Access vba but with your direction and reference you highlighted in your response I will try to create something similar.
I appreciate your time and guidance.

Thanks again.
ip_123

 

[Micron]

You're welcome.
When you research the folder picker part, don't confuse the msoFileDialogFilePicker with the msoFileDialogFolderPicker (assuming I'm remembering their correct names). As their names suggest, one returns the folder chosen, and IIRC, won't show you files in that folder. The other will list files and allow you to choose from them. From what I can gather from your original post, you want to navigate to a folder. Thus, the function would return the folder path and your code would save the file into that folder by concatenating the file name to the path. You would have to ensure that you add the slash between the returned path and the file name.
Good luck.