Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

VBA save files with date in the name

This works perfectly, but how do I add the date to the file name?

 Thank you!!!

Sub SplitEachWorksheet()
Dim FPath As String
Dim strFilename As String


FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window


Avatar of ste5an
ste5an
Flag of Germany image

E.g.

Option Explicit

Public Sub SplitEachWorksheet()

  Const DATE_FORMAT As String = "yyyymmdd" ' ISO 8601, no delimiter, lexical sortable.
  Const NAME_DATE_DELIMITER As String = "-"

  On Local Error GoTo LocalError

  Dim CurrentPath As String
  Dim NewFilename As String
  Dim CurrentSheet As Excel.Worksheet

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  CurrentPath = Application.ActiveWorkbook.Path & "\"
  For Each CurrentSheet In ThisWorkbook.Sheets
    CurrentSheet.Copy
    NewFilename = CurrentSheet.Name & NAME_DATE_DELIMITER & Format(Now(), DATE_FORMAT) & ".xlsx"
    Application.ActiveWorkbook.SaveAs Filename:=CurrentPath & NewFilename
    Application.ActiveWorkbook.Close False
  Next CurrentSheet

  Set CurrentSheet = Nothing
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  Exit Sub

LocalError:
  Set CurrentSheet = Nothing
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  MsgBox Err.Number & ": " & Err.Description

End Sub

Open in new window

Avatar of Euro5

ASKER

ste5an - I get an error on the Format - not sure why?

This exact thing happened on other code I used. I don't know why it won't run that Format
It works as it is on my system ;)

1) The workbook where the code is hosted, must be saved before running the macro.
2) Format() issues normally indicate an Offices installation issue with different Office versions. Check the references in the VBA IDE under Tools/References.
3) Test it in the immediate window as:

?Format(Now(), "yyyymmdd")

Open in new window

What error do you get exactly?
 Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & Date() & ".xlsx"

Open in new window

If the slashes give error
replace(date(),"/","-")
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & replace(Date(),"/","-") & ".xlsx"

Open in new window

Caveat: Using Date() like this leads to an implicit format call using the users current short date settings. Working with international users, this leads to different naming schemes. Also are the common short date formats not lexical sortable.
Avatar of Euro5

ASKER

John - getting error that wrong number of arguments. Error on both - so sorry, can't figure this out!
Error on both - so sorry, can't figure this out!
What about posting the exact error message, maybe as screenshot?

btw, as you use the worksheet name for file name generation, do you use any fancy sheet names?
Avatar of Euro5

ASKER

ste5an - the sheet names are not fancy at all.
Avatar of Bill Prew
Bill Prew

Euro5, you need to include the full set of code you are running, and also a screen capture showing thee act error and line location.
Hi,
Try

Sub SplitEachWorksheet()
Dim FPath As String
Dim strFilename As String




FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & Cstr(Date()) & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window

You didn't say what the date should look like or where it should be placed in the name but try this.

Sub SplitEachWorksheet()
Dim FPath As String
Dim strFilename As String
Dim strNow As String
Dim ws As Worksheet

strNow = Format(Now, "yyyymmdd")
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & "_" & strNow & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of Euro5

ASKER

I get an error on line 7 Format
I think I must not have the library I need?
What is the error? You could also try changing line 7 to
strNow = Format(Now(), "yyyymmdd")

or

strNow = Format(Date, "yyyymmdd")

And in all three of the options you could prepend the assignment with VBA. e.g. strNow = VBA.Format(Now(), "yyyymmdd")
Did you check points 2) and 3)?
Maybe a solution would be to share the Workbook
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial