When I use the Dir (Path) command in a macro, I can include a * character in Path argument to specify which files I want to choose. I try to replace Dir with the method getFolderContents using Simple File Access, e.g. s() = oSFA.getFolderContents(Path, False), but this is not accepted if I include * in Path. Is there an analogue of the Dir command in the SFA that allows the use of *? I understand that it is possible to process the array s() accordingly, but is there a simple solution?
Hallo
use python instead basic and for the particular task https://docs.python.org/3/library/pathlib.html#pathlib.Path.glob
Thanks, Karolus.
Here’s (almost) the same in Basic. Works fast.
Option Explicit
Option Compatible
' Finds file names that match a pattern.
' - folder Can be specified in URL or operating system format. Optionally may end with a path separator.
' - pattern Specifies a filter for the file name. May contain the characters listed below.
' Characters in pattern:
' ? Any single character.
' * Zero or more characters.
' # Any single digit (0-9).
' [charlist] Any single character in charlist.
' [!charlist] Any single character not in charlist.
' For specific features of specifying a template, see [here](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/like-operator)
Function FolderFiles(ByVal folder As String, Optional ByVal pattern As String="")
Dim arr, i as Long, j As Long, sep As String
arr = CreateUnoService("com.sun.star.ucb.SimpleFileAccess").getFolderContents(ConvertToUrl(folder), False)
j=-1
sep=GetPathSeparator()
If pattern<>"" Then
pattern=Lcase(folder & Iif(Right(folder, 1)=sep, "", sep) & pattern)
End If
For i=0 To Ubound(Arr)
arr(i)=ConvertFromUrl(Arr(i))
If Lcase(arr(i)) Like pattern Then
j=j+1
If j<i Then
arr(j)=arr(i)
End If
End If
Next i
If j=-1 Then
arr=Array()
ElseIf j<Ubound(Arr) Then
ReDim Preserve arr(j)
End If
FolderFiles=arr
End Function
Sub TestFolderFiles
Dim arr, t As Long
t=GetSystemTicks()
arr=FolderFiles("C:\temp", "*a*.ods")
Msgbox "Time: " & (GetSystemTicks()-t) & " Count: " & (Ubound(arr)+1)
End Sub
How fast??
%%timeit
from pathlib import Path
p = list(Path.home().glob("Downloads/*.ods"))
## 2.7 ms ± 526 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
## len(p) ⇒ 262
## disclaimer This is a tiny Raspberrypi4b
1 Like
Win 10. Folder on shared Linux (Samba), 1563 files in total.
Time: 159 Count: 1297
Win 10, Folder C:\temp, 4429 files in total.
Time: 144 Count: 749
From a practical point of view, good.
1 Like