Spreadsheet Help: Can I .....

  • Thread starter Thread starter Thanos Of Titan
  • Start date Start date
T

Thanos Of Titan

Is there a way to insert the date a spreadsheet was last saved in a
cell of that spreadsheet? I need to report on Service Level
Agreements with various clients. While I collect and report the data
on a shared drive, others will view the data daily. I would like to
avoid manually entering when it was last updated. I'm not too good at
VB but will gladly try. Thanks, Thanos
 
Thano,

Not sure it can be done without code... if you don't get a
simpler solution, you might want to try this:

Function Last_Saved(filename)

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filename)
Last_Saved = Int(f.datelastmodified)

exit function

In your spreadsheet (or your personal.xls for global
availability) right click on any sheet tab, select View
Code, and when in the VB window choose Insert > Module
from the menu. Paste the above code and save.
Following that you will have a new function category
called User Defined, and the function will appear in there
much like any other Excel built-in function. It takes one
argument that is the full path and name for the file you
want date checked.

Hope this helps. Good luck,
Nikos
 
Liked this, so tried to add to my setup.

With Excel Xp syntax is

Function Last_Saved(filename)

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filename)
Last_Saved = Int(f.datelastmodified)


End Function

Note end function not exit function.

Also note file must be saved before it can show date
modified and cell must be formatted as a date.

Many Thanks to Thano for suggestion, is there a list
anywhere of "Int(f.datelastmodified)" information.

Paul Moles
 
You can use a BeforeSave event macro. It would look like this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
[A1] = Date
End Sub

Put this macro in the Workbook module. The Workbook module is accessed by
right-clicking the icon to the left of the "File" in the menu across the top
of the screen, and select View Code. Paste the macro into that module. "X"
out to get back to the worksheet. This macro puts the date in cell A1.
HTH Otto
 
Paul,

You're absolutely right about the end instead of exit (had
I gone through the trouble of actually running it, VB
would have picked it up! Bloody hurry... what do you
expect on a Friday evening in the office...).
Anyway, I'm glad you found it useful. What do you mean by
list of "Int(f.datelastmodified)"? What are you after?
Pls notify me by e-mail if you continue the thread, or I
might miss it (I very rarely check postings older than 2-3
days).

Best,
Nikos
 
I would make Otto's suggestion a bit more specific with:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
ThisWorkbook.Worksheets(1).Range("b1").Value = Now()
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar
That's better. Thanks. Otto
Tushar Mehta said:
I would make Otto's suggestion a bit more specific with:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
ThisWorkbook.Worksheets(1).Range("b1").Value = Now()
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
Click to expand...
 
My pleasure, Otto. I've seen too many people -- including myself --
burnt by unqualified references to range objects.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top