Find the latest file starting with a string

Here’s what I can’t figure out how to do in a macro:
I have a directory where all files follow a pattern:
[base name].odt
or:
[base name] - Corrected [Number].ods

Knowing the directory and [base name], I need it to find the file name, I need to find the full file name of the file with the highest [Number] or if there are none with corrections, find that out.
I believe the approach should include putting all the file names with the needed [base name] into an array of strings (I’m not sure how to approach this.) and then iterating through the array splitting each string and comparing to the highest [Number] found so far (I believe I can figure this part out.).

Addendum:@ajlittoz suggested below that a shell would be better for this. I’m in Windows 10, with LO 7.0.6. Does that mean a python macro would better? I’ve never succeded in using a python macro in LO.

This task is better done in a shell. Edit your question to tell your OS name because shell languages differ with OSes.

Does that mean a python macro would be better?

No, any macro language could do. But I was rather thinking of some kind of bash (I’m under Linux, so this comes first to my mind). You can also use Perl which has provision to scan directories and may be more user-friendly (meaning more readable and less contorted) than bash.

Alas, I’m stuck with Window$, so no bash. (I run the latest LTS of Ubuntu at home.) I’ve no experience with Perl, so I don’t think I’d be able to figure out how to do it.

You can start:

Sub GetFiles()
  Dim oSFA, path As String, arr

  path="C:\temp"  ' Folder (native OS Format or URL format)
  oSFA=createUnoService("com.sun.star.ucb.SimpleFileAccess")
  arr=oSFA.getFolderContents(ConvertToURL(path), false)     ' true if subfolders needed
  
  ' arr - array containing the filenames 
  ' ...
End Sub

for example:

from pathlib import Path
p = Path('.')#.resolve()
sorted(p.glob('*ods'))

2021-12-07_435x198_scrot

I think I’ve got the macro set up correctly, but for some reason the string split function doesn’t seem to be working correctly.

Dim oSFA as object : oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
Dim stPath as String : stPath = ConvertToUrl("C:/Users/Aaron/Desktop/Files/")
Dim AllFiles() as String : AllFiles = oSFA.getFolderContents(stPath, false)
Dim MatchingFiles() as String
Dim stBasename as String :  stBasename = "a"
Dim iNumber as Integer ' Cast from second Element of stSplit
Dim FileName as String ' File name being examined.
Dim iLastNumber as Integer : iLastNumber = 0
Dim stSplit(2) as String 'array from file name split on " Corrected - "
Dim stBase as String 'first element of stSplit
Dim stLatest as String
Dim stFile as String


For i = 0 to Ubound(AllFiles)
	msgBox AllFiles(i)
	stFile = AllFiles(i)
	If InStr(stFile," - Corrected ")Then ' It's corrected
		stSplit() = split(stFile, " - Corrected ",2)
		stBase = stSplit(0)
		msgBox stBase
		iNumber = Cint(stSplit(1))
		If stBase = stBasename Then 'The base name matches.
			If iNumber>iLastNumber Then
				iLastNumber = iNumber
			End If
		End If
	End If
Next
	If iLastNumber = 0 Then
		stLatest = stBasename
	Else
		stLatest = stBasename & " Corrected - " & Cstr(iLastNumber)
	End If
	
msgBox stLatest 
End Sub

I’m testing it on a directory of .txt files, which reminds me, I need to use another split command to peel off the file extension.
The first two msgBoxes return:

file:///C:/Users/Aaron/Desktop/Files/a%20-%20Corrected%201.txt

So, it’s not being split on the delimiter " Corrected - ". I guess it doesn’t like that delimiter for some reason. :confused:
I don’t see anything at Split Function to suggest that my delimiter would be disallowed. It fits the description “A string of one or more characters length”.

(Perhaps I should use a Replace command to replace that delimiter with a “.”, so that I can peel off the extension at the same time.)

URI (URL) protocol has its own specifics.
You can continue tests by replacing the literal " Corrected - " to sDelimiter.

  Dim sDelimiter As String
  sDelimiter=Replace(" - Corrected ", " ", "%20")

Thanks, but I have already tried replacing the spaces in my delimiter with “%20”. I’m going to try your method of defining a delimiter string ahead of the split command.

@sokol92 It worked! Thank you! I must have missed a space when I manually replaced the spaces in my delimiter in the split command.

Many thanks to @sokol92, who gave me the two hints that got me there.

Sub FindLatest
Dim oSFA as object : oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
Dim stPath as String : stPath = ConvertToUrl("C:/Users/Aaron/Desktop/Files/")
Dim AllFiles() as String : AllFiles = oSFA.getFolderContents(stPath, false)
Dim stBasename as String :  stBasename = stPath & "a"
Dim stNumber as String ' The portion of stFile after Corrected, including the file extension.
Dim iNumber as Integer ' Cast from second Element of stSplit 
Dim iLastNumber as Integer : iLastNumber = 0
Dim stSplit(2) as String 'array from file name split on " Corrected - "
Dim stExtSplit(2) as String 'array from stNumber split on "."
Dim stBase as String 'first element of stSplit
Dim stLatest as String
Dim stFile as String 'File name being examined.
Dim stNoExt as String 'stFile with file extension stripped off
Dim stSplitNoExt
Dim stDelimiter	as String : stDelimiter = Replace(" - Corrected ", " ", "%20")


For i = 0 to Ubound(AllFiles)
	stFile = AllFiles(i)
	'msgBox stFile
	If InStr(stFile,stDelimiter)Then ' It's corrected
		stSplit() = split(stFile, stDelimiter ,2)
		stBase = stSplit(0)
		'msgBox stBase
		stNumber = stSplit(1)
		stExtSplit = Split(stNumber, ".", 2)
		stNoExt = stExtSplit(0)
		'msgBox stNoExt
		iNumber = Cint(stNoExt)
		If stBase = stBasename Then 'The base name matches.
			If iNumber>iLastNumber Then
				iLastNumber = iNumber
				'msgBox "Bumped to " & Cstr(iLastNumber)
			Else
			'msgBox "stBase is " & stBase & "and stBasename is " & stBasename
			End If
		End If
	End If
	'msgBox "Next..."
Next
	If iLastNumber = 0 Then
		stLatest = stBasename
	Else
		stLatest = stBasename & stDelimiter & Cstr(iLastNumber) & ".txt"
	End If
	
msgBox "Result: " & stLatest 
End Sub