referencing folder named *current year*

[npacker]

I'm running a macro on an excel template that does a "save as" on open and I would like it to save to a certain location. However, depending on what year it is, it will save to a different folder. In the location where I want to save it, there are folders named after the year, ie, "2004", "2005", "2006", etc...

I set up a variable called FName, whick defines the location I want it to save to, and then set it equal to

FName = "U:\First Folder\Second Folder\" & ActiveWorkbook.Name

After the second folder, I would like it to check the date, and if it is 2005, I would then like it to go from the second folder, to the folder named 2005, or if is 2006, then to that folder.

Can I set up a variable that is equal to the current year in the 4-digit format, and then call that variable in the folder location?

Any help would be appreciated.
Thanks,
Nate

 

[npacker]

That was totally my problem. I was looking for a text file, but the file is ACTUALLY an excel spreadsheet. So I changed the code to be Workbooks.OPen Filename...
So it works now, but I have one more question.
The path it needs to go to is U:\DFAS OST\Turnover\*current year*\*current month*\ and then look in the *current month* folder for the file. However, I have a variable set up called CurrMonth, and it equals Month(Date). The Month(Date) format is set to be a "1" for January, "2" for February, and so forth. But the folder is actually called "January." Can I change the format to look for "January", rather than "1"
Thanks so much for all your help thus far!
Nate

 

[Von Pookie]

Sure, just use the MonthName command instead.

Using:
CurrMonth=MonthName(Month(Date))

Should return "January"

Using:
CurrMonth=MonthName(Month(Date),True)

Should return "Jan"

 

[npacker]

Thanks, that's perfect. You've been most helpful. What do you do for a living? You're a pro at this!
Nate

 

[npacker]

The code is working now, but if the new file has already been created, of course Excel comes up and says, "This file already exists, would you like to replace it?" If you click yes, that's fine, it replaces it, but if you click no, the code errors, cause the SaveAs failed. Is there any way, I can tell the code that if the user answers "No", then to go to the end of the sub or something?
Thanks,
Nate

 

[Von Pookie]

The code here should work:

http://www.mrexcel.com/board2/viewtopic.php?p=538386#538386