CODEWITHSUNDEEP
excelvba
mlott
mlott

Reputation: 1

VBA to SaveAS with Filename, Specific Folder, and Date

I am trying to save file in a specific folder, add the filename, and add todays date. My VBA is not working. Any suggestions?

Sub SaveFile()

ActiveWorkbook.SaveAs ("G:\Product Support\Platinum\Agents Case Reports\Michael\Saved Client Reports\CAF\CAF Open Case Report.xlsx") & Date

End Sub

Upvotes: 0

Views: 4349

Answers (2)

Robson
Robson

Reputation: 2032

You can do this:

Public Sub SaveFile()

    Dim formattedDate As String
    formattedDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
    
    Dim filename As String
    ' path + filename + formatted date + extension
    filename = _
      "G:\Product Support\Platinum\Agents Case Reports\Michael\Saved Client Reports\CAF\" & _
      "CAF Open Case Report - " & _
      formattedDate & _
      ".xlsx"
    
    ActiveWorkbook.SaveAs filename

End Sub

Whenever I output a date on a filename I always make sure it will sort chronologically. In the code above this is yyyy-mm-dd hh-mm-ss, which is year-month-day hour-minute-second. All numbers have leading zeroes, where necessary. An example from a few moments ago is "2021-08-03 17-58-59".

Upvotes: 1

Anmol Kumar
Anmol Kumar

Reputation: 182

File name cannot contain "/" character which is in the date you should firstly store Current Date in a variable then replace "/" from it before passing it to file name

Sub SaveFile()
Dim CurrentDate as string
CurrentDate = Date
CurrentDate = Replace(CurrentDate, "/", "_")
CurrentDate = Replace(CurrentDate, ".", "_")

ActiveWorkbook.SaveAs ("G:\Product Support\Platinum\Agents Case Reports\Michael\Saved Client Reports\CAF\CAF Open Case Report " & CurrentDate & ".xlsx") 
    
End Sub

This would work now.

Upvotes: 0

Related Questions