VBA Macro doesn't save Worksheet with date AND time

olorin_117

New Member
Joined
Jan 19, 2022
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

olorin_117

New Member
Joined
Jan 19, 2022
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Dec 2, 2020
Messages
498
Office Version
  1. 365
Platform
  1. Windows
You'll need to represent the time without a ':' which is an invalid character for filenames.
 
Solution

olorin_117

New Member
Joined
Jan 19, 2022
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
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..
 

Cyberus_72

New Member
Joined
Mar 23, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
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?
 

Forum statistics

Threads
1,176,085
Messages
5,901,296
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top