[Solved] Identifying The Newest File In A Directory

[zajo]

Dear Forum members,

I would like to open the newest *.csv* file in a directory

I have found one particular VBE solution, but it unfortunately does not work in OO
http://spreadsheetpage.com/index.php/ti ... directory/

probably because of some syntax. I tried my best, but I could not make it work by myself.
Could anybody help me out to make this code to work under OO ??

Code: Select all

Function NewestFile(Directory, FileSpec)
REM Returns the name of the most recent file in a Directory
REM That matches the FileSpec (e.g., "*.csv").
REM Returns an empty string if the directory does not exist or
REM it contains no matching files

    Dim FileName As String
    Dim MostRecentFile As String
    Dim MostRecentDate As Date
    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
    
    FileName = Dir(Directory & FileSpec, 0)
    
    If FileName <> "" Then
        MostRecentFile = FileName
        MostRecentDate = FileDateTime(Directory & FileName)
        Do While FileName <> ""
            If FileDateTime(Directory & FileName) > MostRecentDate Then
                 MostRecentFile = FileName
                 MostRecentDate = FileDateTime(Directory & FileName)
             End If
             FileName = Dir
        Loop
    End If
    
    NewestFile = MostRecentFile
    
    MsgBox(MostRecentFile,0,"MostRecentFile")
    
End Function

I call the function this way NewestFile("c:\files\excel\", "*.csv")

many thanks for your help

with friendly regards

Jozef

[Zizi64]

You must rewrite all of VBA macros for Apache OpenOffice - based on the API functions.

From Andrew Pitonyak's free macro book:

Use the FileExists function to determine if a file or directory exists. Use FileDateTime to return a string with the date and time that a file was created or last modified. The returned string is in a system-dependent format. On my computer, the format is “MM/DD/YYYY HH:MM:SS”. The returned string can be passed directly to the function CDate. The GetFileInfo macro in Listing 8 uses all of the file and directory inspection functions to return information in an easy-to-read format. Also see Figure 2.

https://www.google.hu/url?sa=t&rct=j&q= ... Wm6tOsgmCz

and:
http://www.pitonyak.org/oo.php



Or you can try the LibreOffice. It has a littlebit higher compatibility with the foreign file formats and the VBA macros. Maybe the LO can run your VBA maco without problems... Try it.

[zajo]

I have had a typo in path name so it works

[Lupp]

Since there is no use made of specific VBA constructs instead of the OpenOffice API, the code does well run in AOO.
The issue is the usage of stubborn windows constructs concerning the identification of files, in specific the usage of the backslash instead of an ordinary slash as the path delimiter. I would recommend to generally use FileURL insted of "FileName" or "FilePath" where often the terms aren't even clearly distinguished.

If you want to do such things more than once: Tampering with your files by user code may be risky. DO NOT simply trust in code from Excel-VBA forums or from other sources. You need to be able to trust in your own judgement.

In addition the code contained an unnecessary IF-frame.

You may try:

Code: Select all

Function NewestFile(Optional Directory, Optional FileSpec)
REM Directory = thisDocFolderPath() : FileSpec = "*.csv"       Only for my testing! Using local custom function!
REM Returns the name of the most recent file in a Directory
REM That matches the FileSpec (e.g., "*.csv").
REM Returns an empty string if the directory does not exist or
REM it contains no matching files
    Dim FileName As String
    Dim MostRecentFile As String
    Dim MostRecentDate As Date

Directory = ConvertToURL(Directory)                  REM ConvertToUrl: Use fully qualified FileURL instead of stubborn Win constructs.
  
FileName = Directory & Dir(Directory & FileSpec, 0)  REM Use fully qualified FileURL instead of stbborn Win constructs.

MostRecentDateTime = 0
MostRecentFile     = FileName
'    If FileName <> Directory Then
'        MostRecentFile     = FileName
'        MostRecentDateTime = FileDateTime(FileName)
        Do While FileName <> Directory
            DateTime = FileDateTime(FileName)
            If DateTime > MostRecentDateTime Then    REM Use fully qualified FileURL instead of stbborn Win constructs.
                 MostRecentFile     = FileName
                 MostRecentDateTime = DateTime
             End If
FileName = Directory & Dir()                         REM Use fully qualified FileURL instead of stbborn Win constructs.
        Loop
'    End If
NewestFile = MostRecentFile
MsgBox(MostRecentFile,0,"MostRecentFile")
End Function