RyanT
07-11-2008, 09:49 PM
My scenario: A time log for the week that is updated multiple times per day. Right now I have a command button that I click the last day of the week which advances the dates in my columns to the next week and clears the old time data, it then prompts for the a new file name. Would like my file name to be generated as “TimeSheet MyName_WeekEndingDate” (in MM-DD-YY format)
I can manage to format the CURRENT date in a M-D-YY format, but it always uses a MM/DD/YY format (which is invalid because of the “ / ” ) when I use the date stored in a cell even if the cell format is something other than m/d/y... Does anyone know of a workaround?
This is what I’m working with:
'Advance dates and Save.
Range("A1") = Range("A1") + 1
Range("F5") = Range("F5") + 7
Dim FileName As String, WeekNumber As String, MyName As String
WeekNumber = Range("A1").Value
MyName = Range("A29").Value
FileName = "Time Sheet " & MyName & " Week #" & WeekNumber & " Ending " & Format$(Date, "mm-dd-yyyy")
ChDir _
"E:\My Documents\time sheets\Test"
ActiveWorkbook.SaveAs FileName:=FileName
End Sub
Thanks Much
I can manage to format the CURRENT date in a M-D-YY format, but it always uses a MM/DD/YY format (which is invalid because of the “ / ” ) when I use the date stored in a cell even if the cell format is something other than m/d/y... Does anyone know of a workaround?
This is what I’m working with:
'Advance dates and Save.
Range("A1") = Range("A1") + 1
Range("F5") = Range("F5") + 7
Dim FileName As String, WeekNumber As String, MyName As String
WeekNumber = Range("A1").Value
MyName = Range("A29").Value
FileName = "Time Sheet " & MyName & " Week #" & WeekNumber & " Ending " & Format$(Date, "mm-dd-yyyy")
ChDir _
"E:\My Documents\time sheets\Test"
ActiveWorkbook.SaveAs FileName:=FileName
End Sub
Thanks Much