Want to make sure this script will still work come January - date formatting

[zenjitsu]

Hey there folks, I'm brand new to all things VBA (and scripting/automation in general) and have crafted together a script that is working pretty great for my needs so far, but I am concerned when January rolls around it might break.

What I'm doing: I'm copying information from a .CSV spreadsheet that is imported into a folder that I have another .XLM file located at that has my script on it. User opens the XLM file, clicks the button to start the script, then that script is copying the data from the CSV file, pasting it into itself, then exporting that newly formatted spreadsheet to a .txt file in a different folder, then deleting the original .CSV file that was imported so the next .CSV file can be imported without overwriting needing to be done

The naming format of the CSV file that will constantly be imported into the folder comes out of our other program formatted by year-month, IE 2021-11 for something exported today. I have created the variables in the script to read the current year-current month (yyyy-mm) so the file doesn't have to be renamed each time.

I know that January - September filename is exported as 2021-1.csv, 2021-2.csv, 2021-3.csv, etc. and I'm afraid when January rolls around this script will be looking for 2021-01.csv and throw an error that it doesn't see the CSV file. (I tried to test this by changing my clock on my PC but then O365 thinks I'm not licensed and won't let me do anything.)

Are my fears correct? If so, can I get some assistance in VBA in how to rename those 2021-1.csv files to 2021-01.csv at the beginning of the script with if/then statements? Or some other way? I've been looking into this but this is getting a bit too deep for me.

 

[jasonb75]

I have created the variables in the script to read the current year-current month (yyyy-mm) so the file doesn't have to be renamed each time.

(yyyy-mm) will look for 2021-01, (yyyy-m) will look for 2021-1.

 

[zenjitsu]

(yyyy-mm) will look for 2021-01, (yyyy-m) will look for 2021-1.

Gotcha, so how can I put in the script to look for the yyyy-m when it doesn't see yyyy-mm or vice versa?

 

[jasonb75]

yyyy-m will work with both, it only omits the leading 0 for jan-sep, oct-dec will still work correctly.

 

[zenjitsu]

Well that was a MUCH simpler answer than I figured would take. Tested it with yyyy-m and it works with 11 for November. Thank you for the clarification!

 

[jasonb75]

Well that was a MUCH simpler answer than I figured would take.

That is often the case, things that you think will be complicated are simple, things that look like they should be simple are closer to rocket science.