Convert date to string for automated directory searching-CodePudding

My ultimate goal is to be able to search a file path, where another user will be creating a new folder each month (for example next month the user will create the folder "January 2022"), and I need to be able to search for that folder in an automated way based on the current month and year.

My plan is to create a string object like so:

Dim strDirectory As String
strDirectory = "C:\foo\bar\baz\" & strCurrentMonthName & " " & strCurrentYear

But, I'm having trouble extracting the year as text. Originally I tried to do this:

Dim TestMonth As String
Dim TestYear As String

TestYear = Year(Now)
TestMonth = GetMonthName(Format(Now, "mm"))

Problem is that testYear gets returned as 7/13/1905. This webpage shows how to fix this error in a spreadsheet, but that's not going to help my situation as the date will never be pulled from a spreadsheet (as I feel that's a very error-prone way to do things). The Year function supposedly only works on Variant, Numeric, or String expressions and I don't know how to get just the year from Now.

Keep in mind that my ability to access standard libraries is very limited, I'm using an older version of Excel (2016) and have no control over packages or updates (there are certain features I've had to do without such as MonthName() for example, I had to create my own version of that)

CodePudding user response：

Format returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

So whatever format you express in Format will be returned as a string.

Public Sub Test()

    Dim CurrentDate As Date
    CurrentDate = Date
    
    Dim strDirectory As String
    strDirectory = "C:\foo\bar\baz\" & Format(CurrentDate, "mmm yy") '"C:\foo\bar\baz\Dec 21"
    strDirectory = "C:\foo\bar\baz\" & Format(CurrentDate, "mmmm yyyy") '"C:\foo\bar\baz\December 2021"

End Sub

No need for a GetMonthName function - Format(Now(),"mmmm") returns December this month.

CodePudding user response：

Figured it out:


Dim TestMonth As String
Dim TestYear As Date



testYear = Now
TestMonth = GetMonthName(Format(Now, "mm"))

Range("A1").Select
ActiveCell.FormulaR1C1 = "C:\foo\bar\baz\" & TestMonth & " " & Year(TestYear)

Output:

C:\foo\bar\baz\December 2021