Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveWorkbookAsToday()
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 |