CODEWITHSUNDEEP
excelvbadate-comparison
Gustavo Silva
Gustavo Silva

Reputation: 159

Excel VBA Write filename based on system date

I am trying to write a VBA Script in Excel that will save a file and will give a pre-determined name. One of the criteria is that if it isn't past 11 AM, then it should add to the filename "H12". If it is past 11 AM, then it should add "H16".

Here's what I have so far, regarding this specific part (there is more code that is not relevant to show here:

Dim SaveTime As Integer
SaveTime = Round(Timer / 3600, 0)
dt = Format(CStr(Now), "yyyy_mm_dd")
FolderName = "path/to/file"
If SaveTime < 11 Then
    With Destwb
       .SaveAs FolderName _
                    & "\" & Destwb.Sheets(1).Name & dt & "H12", _
                        FileFormat:=FileFormatNum
       .Close False
    End With
    Else
        With Destwb
            .SaveAs FolderName _
                    & "\" & Destwb.Sheets(1).Name & dt & "H16", _
                        FileFormat:=FileFormatNum
            .Close False
        End With
     End If

The script is working properly except this part because it is always saving the files as "H16". Ran this 45 minutes ago and it should've written it as "H12". I am sure I am not doing the time comparison correctly. I have tried comparing if dt < "11:00" but that does not work either.

Can someone give me a hand here?

Thanks in advance!

edit: added one last detail.

Upvotes: 0

Views: 73

Answers (1)

Martin Dreher
Martin Dreher

Reputation: 1564

While I'm not entirely sure what your error was (which line caused with Err#?), I do hope this helps you solve your problem

(...)
Dim SaveTime As Integer
SaveTime = Hour(Now)

FolderName = "C:\Users\b036081\NoBackupData"
FileFormatNum = 51 ' that's xlsx without macros

    With Destwb

        If SaveTime < 11 Then
            .SaveAs FolderName _
                & "\" & .Sheets(1).Name & dt & "H12", FileFormatNum
            '.Close False
        Else
            .SaveAs FolderName _
                    & "\" & .Sheets(1).Name & dt & "H16", FileFormatNum
            '.Close False
        End If

    End With
  • I noticed you used / in your FolderName-placeholder, but I trust you know that you have to use \ as a path separator in VBA
  • I'm not sure if you actually want to do SaveTime = Round(Timer / 3600, 3). If your goal is to extract the hour of the date, instead of for example return 5 at 04:35, you could use something like Hour(Now), which returns an Integer
  • I wrapped the With Destwb around the If, instead of the other way around, which cleans up your code a bit

Edit: Updated code with working solution

Upvotes: 1

Related Questions