LibreCalc macro: How to batch rename files

[Donnie901]

How do I make a batch rename macro that batch renames files I have in a specific directory?

I have made a macro that grabs all filenames of all files of a specific folder (directory string referenced in F3) and lists them into the A column.

Now I have all renames in the column next to it.
(Eg. I want to rename “file1.file” in my directory to “newfile1.file”
I have the string “file1.file” listed in cell A1 and “newfile1.file” as listed in cell B1, same goes for the strings in A2 until A999 and B2 till B999 respectively)

Here is the macro that grabs the filenames by the way:

Sub GetFileNames

Dim oSheet As Object
Dim iCounter As Integer
Dim stFileName As String
Dim stPath As String

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(5,2)

oSheet=ThisComponent.CurrentController.ActiveSheet

'Change MYPATH to the name of the directory where files are to be obtained'
'Directories within this path will not be listed'
stPath = Cell.string  & GetPathSeparator()
stFileName = Dir(stPath, 0)

'This For loop clears Col A rows 2 through 999'
For iCounter = 1 to 999
'Change here if Column,Row of files is different - iCounter is Row'
    oSheet.getCellByPosition(0,iCounter).String = ""
    
Next iCounter   
iCounter = 0

Do While (stFileName <> "")
    iCounter = iCounter + 1
'File names will start in Col `A` (signified by 0) Row 2 (iCounter)'
    oSheet.getCellByPosition(0,iCounter).String = stFileName
    stFileName = Dir()
    
Loop

End Sub

[JohnSUN]

Do you mean “How to apply Name statement to my list”?

It could be something like this:

Sub RenameAllFiles
Dim oSheet As Variant
Dim oCursor As Variant
Dim nEndRow As Long
Dim i As Long 
Dim oCellRangeByPosition As Variant
Dim oDataArray As Variant
Dim sPath As String, sSourceName As String, sTargetName As String
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
	sPath = oSheet.getCellRangeByName("F3").getString()
	If Right(sPath, 1) <> GetPathSeparator() Then sPath = sPath + GetPathSeparator()
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	nEndRow = oCursor.getRangeAddress().EndRow
	oCellRangeByPosition = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow)
	oDataArray = oCellRangeByPosition.getDataArray()
	For i = 0 To UBound(oDataArray)
		sSourceName = oDataArray(i)(0)
		sTargetName = oDataArray(i)(1)
		If Not FileExists(sPath + sSourceName) Then 
			MsgBox("File " + sSourceName + " not found",0,"Skipped")
		ElseIf FileExists(sPath + sTargetName) Then 
			MsgBox("File " + sSourceName + " already exists",0,"Skipped")
		Else
			Name sPath + sSourceName As sPath + sTargetName 
		EndIf 
	Next i
End Sub

But I know the way a little easier.
Write the formula in C2

="ren """ & $F$3 & A2&""" """ & B2 & """"

Extend it to your entire list. Copy the resulting cells and paste into NotePad. Save the file with the .bat extension and execute it

[Donnie901]

Oh wow both work like a charm. Thank you so much. This is a huge time saver as I will be using this a lot in the upcoming weeks.

[JohnSUN]

@Donnie901 By the way, to get a list of files, you could use the dir / B> filelist.csv command and then open the file in a spreadsheet with a command, for example, Sheet - Insert Sheet from File. This will do without macros.

If you want to use the macro exactly, then look at the ReadDirectories() function in the module UCB of the standard library Tools - this function is written a little more in detail than the code you quoted