| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveWorkbookAsToday()
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? "Kevin B" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveWorkbookAsToday()
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" "sfleck" wrote: 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? "Kevin B" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
