Results 1 to 8 of 8

Thread: Dates

  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates

    I need to have 2 different dates appear on my speadsheets automatically.

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> I need 1 date that shows the date the file was created. "=today()" does not work because every time I reopen the speadsheet, the date changes.

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> I need another date that shows when the file was last editted, not just openned and closed. Therefore "=today()" again, will not work.

    Thanks if you can help. <img src=/S/moon.gif border=0 alt=moon width=15 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    To automatically insert the create date, then workbook will have to be created using a template with the code below in the template's Workbook_Open event procedure in the module behind the ThisWorkbook object. The code inserts the date in cell A1 on Sheet1.

    <pre> If ActiveWorkbook.Path = "" Then
    Sheet1.Range("A1").Value = Date
    End If
    </pre>


    The following code, placed in the Workbook_BeforeSave event procedure in the module behind the ThisWorkbook object in the template will insert the date into Cell A2 on Sheet1 when the workbook is saved if the workbook has been modified.

    <pre> If ActiveWorkbook.Saved = False Then
    Sheet1.Range("A2").Value = Date
    End If
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    I know how to make a template, but you lost me on the "coding" part. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> How do I edit the code of the template? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks for the help! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    Open the template or workbook that will be used as the base for the template and press Alt + F11. That should bring up the VBA Editor window. On the left side of this window the project explorer should be visable (a window that shows the open workbooks and all of the objects in them). If this is not visible, then select "Project Explorer" from the View menu. In Project Explorer find the workbook that you opened (it will be named Book1 if you are in a new workbook). Find the ThisWorkbook object (you may have to click on some of the plus signs to expand some of the collections in the workbook), and double click on ThisWorkbook. This should open the code module behind ThisWorkboon a window on the right of the screen. On the top left of this window should be a combobox that displays "General". Drop down the list and select Workbook (it should be the only other choice). The combobox on the right side should now contain a list of event procedures available at the workbook level, and the WorkbookOpen should be displayed. You can insert the code between the Sub and End Sub statements in the lower part of the window. You can then select the BeforeOpen event from the combobox on the right to insert the other code.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    The following functions access the file's date properties and may be what you need. You can call them from a worksheet cell.

    <pre>Function FileCreatedDate()
    Dim fs, f
    Application.Volatile
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(ActiveWorkbook.FullName)
    FileCreatedDate = f.DateCreated
    End Function

    Function FileModifiedDate()
    Dim fs, f
    Application.Volatile
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(ActiveWorkbook.FullName)
    FileModifiedDate = f.DateLastModified
    End Function

    Function FileAccessedDate()
    Dim fs, f
    Application.Volatile
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(ActiveWorkbook.FullName)
    FileAccessedDate = f.Datelastaccessed
    End Function
    </pre>


    Ian.

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    Thanks so much for the help guys. I'll try both methods ASAP and let you know it worked. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  7. #7
    New Lounger
    Join Date
    Feb 2001
    Location
    Williamston, Michigan, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    Thank you for this information. You have NO IDEA how I have searched for this! I bought a copy of Woody's new book, in part hoping to find this answer. I cussed at the MS KB over this.

    One additional request: can it be protected, so the date cannot be changed? I tried locking that one cell and then protecting the worksheet, but I got a runtime error when I tried to save it as a template.

    Thanks again.

    Jim Conroy

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    The only reason I can think of that protecting the sheet would cause a runtime error is if you have some code that tries to change the protected cells. The code would have to unprotect the sheet, make the change, and then protect the sheet again. In the error message box, what code does it hilite if you click on Debug?

    I'm going to be traveling for the next couple of days, so hopefully someone else can help you until I get back on.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •