S
sfleck
I have question about this script from Office VBA Macros you can use today
How do I format cell B1 so that it does not put in the "\"
I have tried setting it as text but then it sees it and correct and adds
the dateformat that represents the date as 395128 as todays date ( 03-05-08)
When I put in the format of B1 as mm-dd-yy as desired it sees it as 03/05/08
and obviously blows up.
I am using Office 2003 on XP
VBA Follows
Option Explicit
Sub SaveWorkbookAsToday()
'this Macro saves the current (active) workbook with today's date
'varible declaration
' The format to be used for the file names
Dim DateFormat As String
' The path to be used to save the file( if empty , current path of workbook
is used
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 seporator
DateFormat = "mm-dd-yy"
DateFormat = Range("B1").Value
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 todays date
DateFormat = Format$(Date, DateFormat)
' add text to filename
DateFormat = Append & DateFormat
' is there a path assigned?
If Len(Path) = 0 Then
'use current directory
Path = CurDir()
End If
'create the full name for the file
' make sure there is 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
How do I format cell B1 so that it does not put in the "\"
I have tried setting it as text but then it sees it and correct and adds
the dateformat that represents the date as 395128 as todays date ( 03-05-08)
When I put in the format of B1 as mm-dd-yy as desired it sees it as 03/05/08
and obviously blows up.
I am using Office 2003 on XP
VBA Follows
Option Explicit
Sub SaveWorkbookAsToday()
'this Macro saves the current (active) workbook with today's date
'varible declaration
' The format to be used for the file names
Dim DateFormat As String
' The path to be used to save the file( if empty , current path of workbook
is used
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 seporator
DateFormat = "mm-dd-yy"
DateFormat = Range("B1").Value
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 todays date
DateFormat = Format$(Date, DateFormat)
' add text to filename
DateFormat = Append & DateFormat
' is there a path assigned?
If Len(Path) = 0 Then
'use current directory
Path = CurDir()
End If
'create the full name for the file
' make sure there is 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