Microsoft Office Tutorials and References
In Depth Information
Saving a Workbook as Today's Date
Dim Path As String¶
'Any text to be appended to the file name¶
Dim Append As String¶
'Change the following variables¶
'Do not use "\" or "/" as a date separator¶
DateFormat = "yyyy-mm-dd"¶
DateFormat = Range("B1").Value¶
Path = ""¶
'Path = "C:\My Documents"¶
Path = Range("B2").Value¶
Append = ""¶
'Append = "Report "¶
Append = Range("B3").Value¶
'Make sure a valid date format is used¶
If DateFormat Like "[\/]" Then¶
MsgBox "Illegal date format used", vbCritical¶
Else¶
'Assign today's date¶
DateFormat = Format$(Date, DateFormat)¶
'Add text to the filename?¶
DateFormat = Append & DateFormat¶
'Is there a path assigned?¶
If Len(Path) = 0 Then¶
'Use the current directory¶
Path = CurDir()¶
End If¶
'Create the full name for the file¶
'Make sure there's a folder separator at the end¶
If Right$(Path, Len(Application.PathSeparator)) <> _¶
Application.PathSeparator Then¶
Path = Path & Application.PathSeparator¶
End If¶
'Append the date¶
Path = Path & DateFormat¶
'Try to save the active workbook with that name¶
On Error Resume Next¶
ActiveWorkbook.SaveAs Path¶
'See if an error occurs¶
If Err.Number <> 0 Then¶
MsgBox "The following error occured:" & vbNewLine & _¶
"Error: " & Err.Number & ", " & Err.Description, vbCritical¶
End If¶
End If¶
End Sub¶
Exl
In the 'Change the following variables' section, there are three variables that
you can change:
Path: where the file will be saved
¾
DateFormat: the format to use for the file name
¾
Search JabSto ::




Custom Search