referencing folder named *current year*

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132
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
Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132
OK, I just figured out I could probably set a NOW function in a hidden worksheet, and change format to just show the year, and just call the value of that cell for my FName, but then how do I implement that variable into my code:
Code:
FName = "U:\First Folder\Second Folder\" & ActiveWorkbook.Name

Then I would like it, instead of naming it activeworkbook.name, name it the current date, ie, "Jan. 26.xls" I should be able to do the same thing, but then I'm not sure how to call that in the formula either. I guess I know the idea behind doing all this, just don't know how to implement it in the code.

Thanks!
Nate
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
npacker said:
OK, I just figured out I could probably set a NOW function in a hidden worksheet, and change format to just show the year, and just call the value of that cell for my FName, but then how do I implement that variable into my code

You can do it without the NOW function. If I'm understanding you correctly, this should get you to the folder named 2005:

Dim CurrYear As String, FName As String

<font face=Tahoma><SPAN style="color:#00007F">Dim</SPAN> CurrYear <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

CurrYear = Year(Date)
FName = "U:\First Folder\Second Folder\" & CurrYear & "\" & ActiveWorkbook.Name
</FONT>
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
npacker said:
Then I would like it, instead of naming it activeworkbook.name, name it the current date, ie, "Jan. 26.xls"

I think this is what you're going for:

<font face=Tahoma><SPAN style="color:#00007F">Dim</SPAN> CurrYear <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

CurrYear = Year(Date)
FName = "U:\First Folder\Second Folder\" & CurrYear & "\" & MonthName(Month(Date), <SPAN style="color:#00007F">True</SPAN>) & " " & Day(Date) & ".xls"</FONT>
 

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132

ADVERTISEMENT

Thanks, I'll give that a try!
Nate
 

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132
When I implement that code, and try to run it, it gives me a Run-time error '1004':
The file could not be accessed. Try one of the following.
Then ti says make sure the folder exists, make sure the folder isn't read only, blah blah blah. But the thing that confuses me is that one of the lines says,
Make sure the file name does not contain any of the following characters: < > ? [ ] : Jan 26.xls or *
Doesn't it seem odd that it's saying to make sure that the filename does not contain the FILE NAME that I want it to access? I'm confused. Any help would be appreciated.
Thanks,
Nate
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

What is the rest of your code?
 

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132
Here is the single line of code I'm trying to implement
Code:
Workbooks.OpenText Filename:="U:\DFAS OST\Turnover\" & CurrYear & "\" & CurrMonth & "\" & MonthName(Month(Date), True) & " " & Day(Date) & ".xls"

I have a file in that correct folder called Jan 26.xls. I even tried replacing the space with an underscore, and it gave the same error, only it said it can't contain Jan_26.xls. REALLY WEIRD. Thanks for your quick reply
Nate
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Well, the command Workbooks.OpenText actually is for opening a text file (.txt) as a new workbook.

We've got it looking for a workbook file (.xls)

So...are you actually looking for a spreadsheet named after the current date, or a text file?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Nate

Do these folders 2005, 2006 etc exist?

If they don't you'll have to create them.

You can do this using code:

Code:
CurrYear = Year(Date)
MkDir "U:\First Folder\Second Folder\" & CurrYear

You can also check for their existence:
Code:
CurrYear = Year(Date)

If Dir("U:\First Folder\Second Folder\" & CurrYear, vbDirectory) = "" Then
    AskMakeDir = MsgBox("The required directory does not exist. " & _
                  vbCrLf & vbCrLf & "Do you wish to create it now?", vbYesNo + vbQuestion, "Make directory")
    If AskMakeDir = vbYes Then
        MkDir "U:\First Folder\Second Folder\" & CurrYear
    Else
        Exit Sub
    End If
End If
 

Forum statistics

Threads
1,148,057
Messages
5,744,550
Members
423,882
Latest member
Seeham

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