VBA Macro doesn't save Worksheet with date AND time

[olorin_117]

I have the made the code below that basically saves one sheet of the workbook. First it creates the current year, then the current month, then gives the file todays date and a string that is in Range A1. If the year exists but the month folder doesnt, it then creates only the month folder and saves the sheet. If the file already exists, the sub exits. The sheet works as a daily production review and I want it to work forever (i mean to always save it at the correct folder. The codes works PERFECTLY but now I have a specific problem. There are occasions of 2 reviews during the day, with the same name in the range A1 but are actually 2 different productions so I thought of adding the time also in the filename.

Sub savesheet()

Application.ScreenUpdating = False
Dim fixedSavePath, yrName, mthName, sheetFileName, mstryr, yearpath, mstrmth, allto As String

Datetest = Now()

fixedSavePath = "C:\Users\jerka\Desktop\Arxeia kapsoulas\"
yrName = Format(Date, "yyyy")
mthName = Format(Date, "mmmm")
sheetFileName = Format(Date, "dd.mm.yyyy") & " " & Worksheets("Sheet1").Range("A1").Value

mstryr = fixedSavePath & yrName
yearpath = fixedSavePath & yrName & "\"
mstrmth = yearpath & mthName
allto = yearpath & mthName & "\"

fol = Dir(yearpath, vbDirectory)
fal = Dir(allto, vbDirectory)

On Error Resume Next
If fol = "" And fall = "" Then
MkDir mstryr
MkDir mstrmth
ElseIf fol <> "" And fall = "" Then
MkDir mstrmth
End If

strFileName = mstrmth & "\" & sheetFileName & ".xlsx"
strFileExists = Dir(strFileName)
 
If strFileExists <> "" Then
MsgBox "The selected file exists"
Exit Sub
End If
  
Worksheets("Sheet1").Copy
With ActiveWorkbook
.SaveAs Filename:=mstrmth & "\" & sheetFileName & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Close
End With

Application.ScreenUpdating = True

End Sub

For some reason that I can't understand, If i write the filename as

sheetFileName = Format(Date, "dd.mm.yyyy hh:mm") & " " & Worksheets("Sheet1").Range("A1").Value

or in any other way that contains the time, either the "file exists" triggers (even if I delete everything), either it opens a new workbook with the name Book1.xlsx and doesn't save it. Any help will be greatly appreciated cause I really can't wrap my head around this problem

P.S. I have tried the DateTime.Now, putting only the time etc, NOTHING works. Also Format(Datetest, "Long Date") works but "Long Time" doesn't

 

[olorin_117]

P.S. In the code there is a Datetest = Now() that forgot to remove because i am not using it...Tried though but still no result

 

[Candyman8019]

You'll need to represent the time without a ':' which is an invalid character for filenames.

 

[olorin_117]

You'll need to represent the time without a ':' which is an invalid character for filenames.

I really can't BELIEVE the fact that I made that mistake, even though I know this.. You just unstuck my head.Lost 3 hours working on this..Thanks a lot man..I m thinking of deleting the post because is SO stupid..

 

[Candyman8019]

No problem...we've all been there

 

[Cyberus_72]

I have the made the code below that basically saves one sheet of the workbook. First it creates the current year, then the current month, then gives the file todays date and a string that is in Range A1. If the year exists but the month folder doesnt, it then creates only the month folder and saves the sheet. If the file already exists, the sub exits. The sheet works as a daily production review and I want it to work forever (i mean to always save it at the correct folder. The codes works PERFECTLY but now I have a specific problem. There are occasions of 2 reviews during the day, with the same name in the range A1 but are actually 2 different productions so I thought of adding the time also in the filename.

Sub savesheet()

Application.ScreenUpdating = False
Dim fixedSavePath, yrName, mthName, sheetFileName, mstryr, yearpath, mstrmth, allto As String

Datetest = Now()

fixedSavePath = "C:\Users\jerka\Desktop\Arxeia kapsoulas\"
yrName = Format(Date, "yyyy")
mthName = Format(Date, "mmmm")
sheetFileName = Format(Date, "dd.mm.yyyy") & " " & Worksheets("Sheet1").Range("A1").Value

mstryr = fixedSavePath & yrName
yearpath = fixedSavePath & yrName & "\"
mstrmth = yearpath & mthName
allto = yearpath & mthName & "\"

fol = Dir(yearpath, vbDirectory)
fal = Dir(allto, vbDirectory)

On Error Resume Next
If fol = "" And fall = "" Then
MkDir mstryr
MkDir mstrmth
ElseIf fol <> "" And fall = "" Then
MkDir mstrmth
End If

strFileName = mstrmth & "\" & sheetFileName & ".xlsx"
strFileExists = Dir(strFileName)
 
If strFileExists <> "" Then
MsgBox "The selected file exists"
Exit Sub
End If
 
Worksheets("Sheet1").Copy
With ActiveWorkbook
.SaveAs Filename:=mstrmth & "\" & sheetFileName & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Close
End With

Application.ScreenUpdating = True

End Sub

For some reason that I can't understand, If i write the filename as

sheetFileName = Format(Date, "dd.mm.yyyy hh:mm") & " " & Worksheets("Sheet1").Range("A1").Value

or in any other way that contains the time, either the "file exists" triggers (even if I delete everything), either it opens a new workbook with the name Book1.xlsx and doesn't save it. Any help will be greatly appreciated cause I really can't wrap my head around this problem

P.S. I have tried the DateTime.Now, putting only the time etc, NOTHING works. Also Format(Datetest, "Long Date") works but "Long Time" doesn't

This is extremely helpful code. Would you by chance have an edit to add more then one Worksheet to the export?