Getting date of last change as a cell value in Open Office Calc

Question

I have a few Open Office spreadsheet files (let us call them source files), each containing the financial info of a particular office of ours. I also have a summary spreadsheet, that reads some key data about the offices and does some analysis. For the analysis, I would like to know also, how outdated the individual source files, when was the last time the offices updated their data. Is there a way to access the "Last time modified" and/or "Time of creation" file system properties of the source files from Open Office, and import the date into a given cell?

Alternative solutions are also OK, as long as I get the date of last change of the source files into the cells of the summary spreadsheet.

Answer 1

The closest thing I've found for libreoffice is the NOW() function. The internet has many Excel-related answers that require visual basic. See for example

http://www.sumproduct.com/thought/creating-a-timestamp

Hope you (and I!) get a good answer some day, but 9 months on I suppose we will have to live without a bit longer...

Answer 2

I keep seeing questions about getting the last file modification date in a Calc cell with no good answers. Below is the code I use. Add it as a macro to your spreadsheet.

Function CalcDoc_Modify_Date()
Dim sTemp
' work with DocInfo
With ThisComponent.DocumentProperties.ModificationDate
   ' set return format
   ' force month and day to two digits
   sTemp = _
   Format( .Month, "0#" ) & "/" & _
   Format( .Day, "0#" ) & "/" & _
   .Year & " " & _
   Format( .Hours, "0#" ) & ":" & _
   Format( .Minutes, "0#" ) 
   End With
' assign func return value
CalcDoc_Modify_Date = sTemp
End Function 

In your spreadsheet, enter =CALCDOC_MODIFY_DATE() in a cell to call this function.

Answer 3

Use this VBA (Macro) code as Module, will return last Date & Time the file has been modified.

Disclaimer:

This code works only for EXCEL but never with Open Office Calc.

I've posted this answer for benefit of the community,, since I do believe that it's rare to find.

Function LastWBKModDate() As String

    Dim FSO As Object
    Dim File As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set File = FSO.GetFile(ThisWorkbook.FullName)
    LastWBKModDate = Format(File.DateLastModified, "m/d/yy h:n ampm")
    MsgBox LastWBKModDate
    Set FSO = Nothing
End Function

How it works:

• Enter this Formula in any blank cell

=LastWBKModDate()

Formula will return Message Box showing Date & Time then as soon you hit Ok the last modified Date & Time will be filled in the Cell.