PDA

View Full Version : Solved: date footer



tkaplan
11-30-2008, 11:14 AM
Hi,
Is there a way that I can insert a datte in the footer of a spreadsheet that reflects the last time the spreadsheet was modified?

Thanks in advance

Ischyros
11-30-2008, 11:36 AM
Hi....below is some code that selects the existing footer, deletes whatever is in it, and then inserts the text "last modified" and the current date. You could set this to execute whenever it the document opens. I am more familiar with the excel vba object model, but I think this should work. Also note this was for word 2007 so you might need to tweek it for an older version, but I am not sure.


Sub Macro1()

WordBasic.ViewFooterOnly
Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:="Last Modified"
Selection.TypeText Text:=vbTab & Date
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

End Sub

Hope this works! let me know.

Bob Phillips
11-30-2008, 11:48 AM
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "Last saved on: " & Format(Me.BuiltinDocumentProperties("last save time"), "dd mmm yyyy hh:mm:ss")
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Ischyros
11-30-2008, 11:52 AM
Oops! I realized you were talking about Excel.... in that case see the code below. I have modified it so that when the worksheet is changed it puts it in, but you could change it so that it reacts to whatever event you want. Hope this post helps haha.


Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.PageSetup
.CenterFooter = Date
End With
Range("A1").Select
End Sub

tkaplan
11-30-2008, 11:59 AM
Thank you both.
Follow up question - is there a simple formula that i can put in a cell to reflect any of the document properties - not using macros though
i.e. the size of the file, date created, modified, etc.

thanks!

Bob Phillips
11-30-2008, 12:16 PM
'-----------------------------------------------------------------
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 such as
=DocProps ("last author")
or
=DocProps ("last save time")

tkaplan
11-30-2008, 12:27 PM
awsome thank you!