2

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.

1
  • Eliminated the non-applicable tags because this is attracting solutions in Basic, and the different Basics aren't compatible.
    – fixer1234
    Apr 4, 2019 at 7:45

3 Answers 3

0

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...

0

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.

2
  • @RajeshS, this author hasn't been here for 2 yrs. Does everything here look like legit VBA? I'm just wondering if AlexJ was a OOCalc user and picked up on what the OP was using. Maybe he wrote this in OOBasic and it would actually work for the OP, but it's not working for you because you're treating it as VBA? I've been away from VBA for decades and forgot it all. :-)
    – fixer1234
    Apr 4, 2019 at 7:41
  • @fixer1234,, above code has may commands working with VBA DIM, WITH and Format only this I found different ThisComponent.DocumentProperties.ModificationDate let me check the VBA library ! Apr 4, 2019 at 8:38
0

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.

4
  • Another Q where the OP was using something other than Excel and tagged it with every spreadsheet tag (because they're all exactly the same, right?). Maybe they thought it could be done with formulas. Anyway, they were using OpenOffice Calc, which has its own OOBasic, and that isn't compatible with VBA. On the upside, way more visitors here use Excel than Calc, so if someone else has the same issue, maybe they can use this. It might be worth just adding a disclaimer that this is for the Excel readers and won't work in Calc (so the 1 remaining Calc user doesn't downvote because it didn't work).
    – fixer1234
    Apr 4, 2019 at 7:28
  • @fixer1234, it's good idea to notify where is exactly useful thanks☺ Apr 4, 2019 at 7:30
  • LO Calc uses LOBasic, which also isn't compatible with VBA. I don't know if OOBasic and LOBasic are compatible with each other.
    – fixer1234
    Apr 4, 2019 at 7:32
  • @fixer1234 now is edited !! Apr 4, 2019 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.