Saving Excel File Path Creation

[Strider89]

I don't know if this is even possible. I am in charge of our template for work. I need to make sure our files are being saved properly and to a secure location. Right now, the workbook will save itself when certain cells are filled, and will autosave to a specific location every 20 minutes, and then save again when the file is closed. The file path is a specific location, so if the user does save as, they will be able to still select other locations to save their project.

Here's the rub. The file path is set to a folder named by quarter, in another folder named by year. I don't want to have to change the code every quarter. Is there a way that Excel, upon saving, can check the file path to see if it is the right one? And if that file path doesn't exist, create a folder for it to save itself in?

Here's what I'm thinking (no code, because I don't know where to start.)
In the workbook

• Dim Year as String, then somehow set it to the current year.
• Set January 1st through March 31st as Q1, April 1st through June 30th as Q2, and so on.
• Check to see if C:\\Reports\YEAR\Q# exists as a file path to save
• If yes, save to that file path
• If not, create the path and save itself to that path
• The file will ultimately sort itself into a location.

I'm sure some of this is beyond the scope of Excel, but I am hoping that someone here can help me figure this out.

 

[Strider89]

Also, the save feature cannot change the directory. If the user does Save As to a specific location, this script should not override their file path when they click Save. That is to say, the user should not have to use the Save As feature to constantly reset their file path every 20 minutes.