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

zenjitsu

New Member
Joined
Nov 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
(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

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
yyyy-m will work with both, it only omits the leading 0 for jan-sep, oct-dec will still work correctly.
 

zenjitsu

New Member
Joined
Nov 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,390
Messages
5,746,432
Members
424,016
Latest member
Shizzl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top