Export to Dynamic Path Monthly (1 Viewer)

[Wapug]

At the end of every month I, or someone else now, need to export a query named qMonthEndMaintenanceData to an excel workbook with a tab named qMonthEndMaintenanceData. The Excel file is already located in a folder named for the previous month and workbook named for the month. I can do this in access by creating an export and just going in and updating the path, however, I would like to automate it for others, so they are not going into the database and monkeying with things. How can I set it so that a button on a form will open a browser to allow my user to browse to and select \\ShareDrive1\Tracking\April\AprilMonthEndData.xlsx or whatever location they wish?

 

[theDBguy]

Hi. Maybe something like (for the current month):

"\\ShareDrive1\Tracking\" & Format(Date(),"mmmm") & "\" & Format(Date(),"mmmm") & "MonthEndData.xlsx"

 

[Wapug]

Not sure I follow what you're saying here.

 

[Isaac]

Or, do a search for FileDialogue vba and check out that functionality.

 

[theDBguy]

Not sure I follow what you're saying here.

Well, the way I understood your question was, you sort of wanted this path:

\\ShareDrive1\Tracking\April\AprilMonthEndData.xlsx

to become somewhat like this:

\\ShareDrive1\Tracking\NameOfMonth\NameOfFolderMonthEndData.xlsx

Did I misunderstand?

 

[Cronk]

Export your query to a fixed folder with a fixed name eg Folder: C:\temp, File: MonthEndData.xlsx.

Then have a line to store the new folder path as suggested by DBguy and create the folder

strFolder = "\\ShareDrive1\Tracking\" & Format(Date(),"mmmm")
mkdir strfolder

Then copy the file to the new folder and change its name

FileCopy "C:\temp\MonthEndData.xlsx",  strFolder & "\" & Format(Date(),"mmmm") & "MonthEndData.xlsx"

Then you'll have to use automation (look up how) to open the generated file in an instance of Excel and change the worksheet name.
Finally remove the temporary file using Kill to be ready for running the procedure next month.

BTW, what are you going to do April next year?