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]

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:

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]

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]

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]

Thanks, I'll give that a try!
Nate

 

[npacker]

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]

What is the rest of your code?

 

[npacker]

Here is the single line of code I'm trying to implement

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]

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]

Nate

Do these folders 2005, 2006 etc exist?

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

You can do this using code:

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

You can also check for their existence:

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