Dates

[g0t0pless]

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>

[LegareColeman]

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>

[g0t0pless]

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>

[LegareColeman]

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.

[IanSaunders]

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.

[g0t0pless]

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>

[jconroy]

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

[LegareColeman]

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.