SaveWorkbookAsToday()

[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

 

[Kevin B]

Create a string variables for the day and convert your date to the format you
want to use.

Dim strDate as string

strDate = Format(Date,"mm-dd-yyy")

Then append or concatenate the string date where necessary in the file name.

 

[sfleck]

This is put in with the code or somwhere else ?

What is the difference with the line that is in there that is
DateFormat = Format$(Date, DateFormat)

and earlier DateFormat was defined

DateFormat = "mm-dd-yy"

Is there a way to set the format of Cell B1 to be mm-dd-yy
It is diplayed in that format but is not actually that

Is that where strDate goes?

Create a string variables for the day and convert your date to the format you
want to use.

Dim strDate as string

strDate = Format(Date,"mm-dd-yyy")

Then append or concatenate the string date where necessary in the file name.
--
Kevin Backmann

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

 

[sfleck]

After writing this I fixed it by doing the following

DateFormat = Format$(Date, DateFormat)
DateFormat = Format$(Date, mm-dd-yy)

My only question is why did it not work when DateFormat was defined as
DateFormat = "mm-dd-yy"

This is put in with the code or somwhere else ?

What is the difference with the line that is in there that is
DateFormat = Format$(Date, DateFormat)

and earlier DateFormat was defined

DateFormat = "mm-dd-yy"

Is there a way to set the format of Cell B1 to be mm-dd-yy
It is diplayed in that format but is not actually that

Is that where strDate goes?

Create a string variables for the day and convert your date to the format you
want to use.

Dim strDate as string

strDate = Format(Date,"mm-dd-yyy")

Then append or concatenate the string date where necessary in the file name.
--
Kevin Backmann

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