insert the date the file was last modified

[Guest]

How do I automatically insert the date the current file was last modified
into a cell in Excel?

 

[Frank Kabel]

Hi
use the following UDF:

Function DocProps(prop As String)
application.volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

and enter in a cell
=DOCPROPS("last save time")
(format cell as date)

 

[Guest]

Frank: Thank you for your help. This solved my problem perfectly!

Regards,

Randy

 

[Guest]

sorry to intrude, but... what exactly is a UDF and how do i make one?

 

[Frank Kabel]

Hi
a UDF is a user defined function written in VBA (visual Basic for
Applications'). For getting started with this see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

This sounds like what I'm looking for too, but I when I follow the
instructions, I get an error: "#NAME?" in the cell.
Any ideas?

 

[Gord Dibben]

Hoff

The UDF below goes into a General Module in the workbook.

With your workbook open hit ALT + F11 to get to the VBEditor.

CTRL + r to open the Project Explorer.

Find your workbook/project and right-click on it and Insert>Module.

Copy and paste the DocProps UDF in there.

ALT + Q to go back to Excel.

In a cell enter =DocProps("last save time")

When happy with results, save the workbook.


Gord Dibben Excel MVP

 

[Jim May]

This isn't working - in UDF should prop be in ( ) ?
Back in sheet1 cell b4 what exactly does one enter?
= DOCPROPS("last time saved") doesn't get it
is there a substitute for last time saved, here/,...*&^%*&
Daaa,

 

[Jim May]

Never mind
It's: last save time
not: last time saved
my dyslexia at work..
daaa

 

[Guest]

Thanks! That did it. I thought I had entered the code into a module, but it
I had not actually inserted a new one.
Thanks!

 

[Guest]

This works beautifully. Is there a way to put the date in an Excel
header/footer? That would be... excellent!

 

[Guest]

Hi Moreta,

Here is what I use. Feel free to use it. Hopefully the comments are self
explainatory.
Good luck,

JohnO

' Author: John Ostar
' Last Modified: 10/10/2005
'Description:
'This VBA code will put the Workbook's Last Modified Date and Time into the
' printed page footer of all sheets, including charts, of the open
workbook.
' It is needed because MS-Excel does not have a field similar to
MS-Word's
' LastSaveDate that can just be easily added to a custom footer.
' This code will run everytime the workbook is saved.
'Installation: Open the desired Workbook. Click Tools->Macro->Visual Basic
Editor.
' In Project Explorer, double click on ThisWorkbook and paste this code
into it.
' Close the Visual Basic Editor and Save the Workbook.
'Note: It will be necessary to set Tools->Macro->Security to Low or Medium

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Get the active workbook's last modified date property.
dtMyLastSaveDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save
Time")

'Put value into center footer of every sheet in the workbook
For Each wsheet In Sheets
'the default date format is m/d/yy h:m:s AM/PM
'alternate format is m/d/yy h:mm am/pm
dtMyLastSaveDate = Format(dtMyLastSaveDate, "m/d/yy h:m am/pm")
wsheet.PageSetup.CenterFooter = "Last Modified: " & dtMyLastSaveDate
Next wsheet
End Sub

 

[Guest]

Opps. One error in my comments left over from an earlier attempt.
'This code will run everytime the workbook is PRINTED (not saved).
Sorry.

 

[Guest]

Hi, John

I found your suggestion to be quite interesting because I am looking for a
way to define a 'default' header in Excel. When I print my spreadsheets, I
always like to include the workbook name (and folder), the worksheet name,
the date and time. Using your example, I was able to quickly define the
desired header for every sheet in my workbook. What I am wondering now is
whether there is a way to automatically include this code in any new workbook
I create, or whether I need to cut and paste it each time. That would be
almost as much of a pain as manually defining the header in each new workbook
is now.

I look forward to your reply. Thanks!
Hugh John Cook

 

[Guest]

Hi,

I'm trying to get this UDF to work in my Excel 2002 workbook. I've followed
the instructions closely and sometimes it works. For example, I created a
new workbook, book1, and inserted the UDF module as instructed. It worked.
Then I tried putting the same code into a module in my Personal.xls workbook
so that I can run the UDF from any open workbook. It seems to run OK if I
stay inside the Personal.xls workbook, but not when I try to use it in a new
workbook. I've tried using Insert Function and accessing the UDF from the
user-defined functions and I've tried just keying in the =DocProps("last time
save") and neither works (the function returns either #VALUE or #NAME).

I know I'm copying and pasting correctly because the function works
sometimes.

Any idea what I'm doing wrong?

Ted

 

[Dave Peterson]

Try:
=personal.xlsb!docprops("last save time")

Make sure you use the correct extension for your personal.xl* file. And watch
the order of the words!

"Last Save Time" is not the same as "last time save".

 

[Chip Pearson]

If the code resides in a workbook other than the workbook containing the
cell function, you need to prefix the call to the function with the
name of the (open) workbook that contains the code. E.g,.

='personal.xls'!DocProps("Last Save Time")

See http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx for a
discussion of User Defined Functions.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Hi guys,

Thanks a million for the quick replies. I understand the requirement to
precede the function name with the workbook name when running the function
from another wookbook. I think I have discovered the problem. If I insert
the function into a cell in a workbook, it returns #VALUE!. But if I save
the file, close it and then reopen it the function returns the expected
value. What I was missing was the requirement to save and close the workbook
to get the function to work.

Ted

 

[Dave Peterson]

I think that original code has a small bug in it.

If you create two new workbooks and save them at different times, you can put:

=personal.xls!docprops("Last save time")

In A1 of Sheet1 in both workbooks.

Now arrange your windows so you can see Sheet1 of both workbooks at the same
time.
Window|Arrange|horizontal

Force a recalc (put =rand() in any empty cell). Look at the times.

I'd make a small change to get the properties from the workbook that holds the
formula.

application.caller is the cell with the formula.
application.caller.parent is the worksheet that owns the cell with the formula
application.caller.parent.parent is the workbook that owns the worksheet that
owns the cell with the formula

Option Explicit
Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = Application.Caller.Parent.Parent.BuiltinDocumentProperties(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

 

[Chip Pearson]

You don't need to close the workbook nor does the workbook need to be in a
saved state. All that is required is that the workbook has been saved to
disk once. The last saved property is created in the workbook when it is
saved for the first time, so a new, unsaved workbook will not have the
property. Save it once and all will be right with the world.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)