RE: SaveWorkbookAsToday()

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


"sfleck" wrote:

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

.

Relevant Pages

  • SaveWorkbookAsToday()
    ... I have question about this script from Office VBA Macros you can use today ... ' The format to be used for the file names ... Dim DateFormat As String ... Dim Append As String ...
    (microsoft.public.excel.programming)
  • Re: Parsing IETF standard date format.
    ... I am trying to use the DateFormat class to parse a string in that format ... UNTIL YOU CONVERT IT TO A STRING. ... > If all of the Calendar functionality was in Date You'd have to have a way to set ... > That is the intent of DateFormat and Calendars. ...
    (comp.lang.java.programmer)
  • Re: International date format and user form
    ... Here is a past function I originally developed for a question over in the compiled VB newsgroups which will format a date in the same format that Windows is using... ... Function DateFormatAs String ... DateFormat = Replace, "MMM") ... Dim DateSep As String ...
    (microsoft.public.excel.programming)
  • Re: International date format and user form
    ... Function DateFormatAs String ... DateFormat = Replace) ... compiled VB newsgroups which will format a date in the same format that ... Dim DateSep As String ...
    (microsoft.public.excel.programming)
  • Re: Parsing IETF standard date format.
    ... I am trying to use the DateFormat class to parse a string in that format ... UNTIL YOU CONVERT IT TO A STRING. ... If all of the Calendar functionality was in Date You'd have to have a way to set ... That is the intent of DateFormat and Calendars. ...
    (comp.lang.java.programmer)