SaveWorkbookAsToday()

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

  • RE: SaveWorkbookAsToday()
    ... Create a string variables for the day and convert your date to the format you ... Then append or concatenate the string date where necessary in the file name. ... Dim DateFormat As String ...
    (microsoft.public.excel.programming)
  • RE: SaveWorkbookAsToday()
    ... DateFormat = Format$ ... Is there a way to set the format of Cell B1 to be mm-dd-yy ... Then append or concatenate the string date where necessary in the file name. ...
    (microsoft.public.excel.programming)
  • RE: SaveWorkbookAsToday()
    ... DateFormat = Format$ ... Is there a way to set the format of Cell B1 to be mm-dd-yy ... Then append or concatenate the string date where necessary in the file name. ...
    (microsoft.public.excel.programming)
  • Re: Write position
    ... Formatted direct-access file: nearly as complicated as except that the record-length is not generally o/s-dependent, and you can use a formatted write, but you need to get the format correct to fill the record (including the newline sequence). ... As for direct-access you need to convert the numbers to text yourself with an internal file write, and append the newline sequences yourself, but a minor advantage is that you don't have to worry about record lengths at all. ...
    (comp.lang.fortran)
  • Re: Copy and paste from Excel to access.
    ... where the format of the excel sheet matches the format of the datagrid? ... I might want to update or I may want to append. ... you can import a range of cells using the TransferSpreadsheet ...
    (microsoft.public.access.gettingstarted)