Macro for filling a table with parts of file names (split names by delimiter)

[raxel09578]

My goal:
Fill a spreadsheet with file names from a directory; during import, the files should be split by a delimiter. In my case, the delimiter is “_”. As a bonus, I’d like the file extension (after 3rd part of file name) removed.
WORKING SOLUTION: SEE REWRITTEN CODE BY JohnSUN BELOW

With the help of ChatGPT, I have gotten a nearly working code (I updated it below with the), which still threw an error at line 21 (sheet.getCellByPosition(3, i + 9).setValue(CStr(parts(2)) ).

All the files I want to handle are named after the same systematic. Parts(1) and (2) of the file names are always numbers, while parts(3) are always text. It seems the code has an issue with the text part. Adding the “CStr” function to the code did not help for parts(3).
With the corrected code, all parts now get imported into separate cells next to each other, as intended.

Here is the updated ChatGPT code (now working, but doesn’t remove file extension yet):

Option VBASupport 1
Sub FillTableFromFileNames()
Dim filePicker As Object
Dim fileNames() As String
Dim i As Integer
Dim sheet As Object
filePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
filePicker.setMultiSelectionMode(True)
filePicker.setTitle("Select Files")
filePicker.appendFilter("All Files", "*.*")
If filePicker.execute() = com.sun.star.ui.dialogs.ExecutableDialogResults.OK Then
    fileNames = filePicker.getFiles()
    sheet = ThisComponent.Sheets(14) ' Use the 14th sheet
    For i = 0 To UBound(fileNames)
        Dim fileOnly As String
        Dim parts() As String
        fileOnly = Mid(fileNames(i), InStrRev(fileNames(i), "/") + 1)
        parts = Split(fileOnly, "_")
        sheet.getCellByPosition(1, i + 9).setString(parts(0)) ' Fill first column
        sheet.getCellByPosition(2, i + 9).setString(parts(1)) ' Fill second column
        sheet.getCellByPosition(3, i + 9).setString(parts(2)) ' Fill third column**
    Next i
End If
End Sub

Note:
I know there is a already working solution for getting file names from a folder via a macro, but I also need the automated splitting of file names into different colums (don’t want to do it manually via “Text to Columns”). Besides, it is convenient to use the file picker instead of hard coding the directory into the macro.
Thank You for any help!

My system infos:
Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: CL threaded

[JohnSUN]

Welcome!
getCellByPosition(3, i + 9).setValue(CStr(parts(2))) must be
getCellByPosition(3, i + 9).setString(parts(2))

[JohnSUN]

If your code is conceived exactly as “in the file selection dialog, select several files and place the name of each of them in the 14th (or 15th?) sheet of the current spreadsheet, starting from the 8th (or 9th?) row, dividing into separate cells by the delimiter underscore”, then the macro may look like this:

Sub FillTableFromFileName()
Dim filePicker As Object
Dim fileNames() As String
Dim fileOnly As String
Dim parts() As String
Dim i As Integer
Dim sheet As Object
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	filePicker = createUnoService("com.sun.star.ui.dialogs.FilePicker")
	filePicker.setMultiSelectionMode(True)
	filePicker.setTitle("Select Files")
	filePicker.appendFilter("All Files", "*_*_*.*")
	If filePicker.execute() Then
    	fileNames = filePicker.getSelectedFiles()
		sheet = ThisComponent.Sheets(14) ' Use the 14th sheet
	    For i = 0 To UBound(fileNames)
	        fileOnly =  GetFileNameWithoutExtension(FileNameoutofPath(ConvertFromURL(fileNames(i))))
	        parts = Split(fileOnly, "_")
	        sheet.getCellRangeByPosition(0,i+8,UBound(parts),i+8).setDataArray(Array(parts))
	    Next i
	End If
End Sub

[raxel09578]

This works perfectly for my intended purpose. Thanks a lot!
(Note: Your solution for the ChatGPT code also works, although your rewrite is better, as no VBAsupport needed & the file extension is being removed.)