Hot to Capitalize a Formatted Date in VBA???

[AlexB123]

Hi all,

I created a little sub to rename and save my daily to do list in a specific folder with a specific name. However, instead of creating a folder named "10.Oct18" I would like it to be "10.OCT18". I tried to wrap "UCase" around the relevant function, but it doesn't work. Ideas?

Public Sub NewDay()
Application.DisplayAlerts = False
    Dim macroWB As Workbook
    Dim fileName As String
    Dim BaseDir1 As String, BaseDir2 As String, BaseDir3 As String
    
        BaseDir1 = "C:\\Alex\My Documents\ToDo's\"
        BaseDir2 = BaseDir1 & Year(Now()) & "\"
        BaseDir3 = BaseDir2 & Format(Now(), "mm") & "." & UCase(Format(Now(), "mmmyy")) & "\"
        
            Call CheckForPaths(BaseDir1, BaseDir2)
            Call CheckOnePath(BaseDir3)
            
        fileName = BaseDir3 & Format(Now(), "mm.dd.yy") & ".xlsm"
        
            Set macroWB = ThisWorkbook
            
    macroWB.Sheets("ToDo's").Activate
    macroWB.SaveAs fileName, 52
    macroWB.Save
    Set macroWB = Nothing
Application.DisplayAlerts = True
End Sub
Public Sub CheckForPaths(sPath1 As String, sPath2 As String)
    If Len(Dir(sPath1, vbDirectory)) = 0 Then
        MkDir (sPath1)
    End If
                        
    If Len(Dir(sPath2, vbDirectory)) = 0 Then
        MkDir (sPath2)
    End If
End Sub
Public Sub CheckOnePath(sPath3 As String)
    If Len(Dir(sPath3, vbDirectory)) = 0 Then
        MkDir (sPath3)
    End If
                        
End Sub

Any help is appreciated!!!

 

[Joe4]

UCASE works fine for me.

Note that you updated it here:

BaseDir3 = BaseDir2 & Format(Now(), "mm") & "." & [COLOR=#ff0000][B]UCase[/B][/COLOR](Format(Now(), "mmmyy")) & "\"

but didn't update it here:

fileName = BaseDir3 & Format(Now(), "mm.dd.yy") & ".xlsm"

Update the filename variable too and you should be good.

 

[AlexB123]

UCASE works fine for me.

Note that you updated it here:

BaseDir3 = BaseDir2 & Format(Now(), "mm") & "." & [COLOR=#ff0000][B]UCase[/B][/COLOR](Format(Now(), "mmmyy")) & "\"

but didn't update it here:

fileName = BaseDir3 & Format(Now(), "mm.dd.yy") & ".xlsm"

Update the filename variable too and you should be good.

I renamed my current working folder to test the code ... and it ran fine. LOL.

Thanks for pointing out the problem was "User Error"!!!

 

[shg]

You could simplify

BaseDir3 = BaseDir2 & Format(Now(), "mm") & "." & UCase(Format(Now(), "mmmyy")) & "\"

to

BaseDir3 = BaseDir2 & UCase(Format(Date, "mm\.mmmyy\\"))

 

[AlexB123]

You could simplify

BaseDir3 = BaseDir2 & Format(Now(), "mm") & "." & UCase(Format(Now(), "mmmyy")) & "\"

to

BaseDir3 = BaseDir2 & UCase(Format(Date, "mm\.mmmyy\\"))

Ooh... I like this! But can you explain what's happening with the Slashdot and double backslash??? Thanks!

BaseDir3 = BaseDir2 & UCase(Format(Date, "mm\.mmmyy\\"))

​

 

[shg]

Help is your friend:

\	Display the next character in the format string. To display a character that has special meaning as a literal character, precede it with a backslash (\). The backslash itself isn't displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (\\).
	Examples of characters that can't be displayed as literal characters are the date-formatting and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, / and :), the numeric-formatting characters (#, 0, %, E, e, comma, and period), and the string-formatting characters (@, &, <, >, and !).