Forum Discussion
Macro to 'save as' with a date in the file name
I'm trying to create a macro to save my spreadsheet with the filename as: filename1 (which is a cell with my name in it) & " Timecard" & Filename2 (which is a date in cell p4)
My problems are: 1) getting VBA to recognize the date format from mm/dd/yyyy to mm-dd-yyyy
2) getting that date into the filename from a cell value
Maybe it would be easier to convert the date into text and parse out the month, day and year
Private Sub CommandButton1_Click()
Dim Path As String
Dim filename1 As String
Dim filename2 As String
Path = "C:\Users\Inlan\OneDrive\AG Emp TimeCards\TimeABoles\AA This Year\"
filename1 = Range("G4")
filename2 = Range("p4")
ActiveWorkbook.SaveAs Filename:=Path & filename1 & " TimeCard" & Format(filemane2(), "DD-iMM-YYYY"), FileFormat _
:=xlOpenXMLWorksheetkMacroEnabled, CreateBackup:=False
End Sub
2 Replies
Hello Andrew Boles , Sorry it's taken 3 yrs for you to get a reply from someone. If you're still looking for an answer to you question try this code. It's not mine but made up of bits from all around the internet. Hope it helps.
Sub File_Saver()
' Some variables
Dim IntialName As String
Dim fileSaveName As Variant'A catch to give a default name of what's in cell A1
InitialName = Range("A1")'Automatic generation of month day, year file name, 4 m's will give the full month, 1 d will give 2 instead of 02 and yy will give you a 2 digit year. This includes spaces and a comma.
YourFile = Format(Date, "mmm") & " " & Format(Date, "dd") & ", " & Format(Date, "yyyy") & ".xlsx"'File save dialog opener
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=IntialName & " " & sFile, _
fileFilter:="Excel Files (*.xlsx), *.xlsx")End Sub
- sorry, small mistake above, please change line above End Sub to:
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=IntialName & " " & YourFile, _
fileFilter:="Excel Files (*.xlsx), *.xlsx")
