Hello,
i’m trying to achive file conversion task. I need to convert many old XLR ( Microsoft Works Spreadsheet) files into ODS format.
With help of ChatGPT i created this macro:
REM ***** BASIC *****
Sub ConvertToODS
Dim sourceFolder As String
Dim targetFolder As String
Dim filePicker As Object
Dim fileList As Object
Dim file As Object
Dim inputFilePath As String
Dim outputFilePath As String
Dim fileName As String
Dim document As Object
' Specify source and target folders'
sourceFolder = "file:///C:/Import-xlr/"
targetFolder = "file:///C:/Export-xlr/"
' Create a file picker to get the list of files in the source folder'
filePicker = createUnoService("com.sun.star.ucb.SimpleFileAccess")
fileList = filePicker.getFolderContents(sourceFolder, False)
' Loop through each file in the folder'
For Each file In fileList
inputFilePath = file
fileName = filePicker.getFileName(inputFilePath)
' Check if the file is a spreadsheet (looking for .xls or .xlsx files)'
If InStr(fileName, ".xlr") > 0 Then
' Open the spreadsheet document'
document = StarDesktop.loadComponentFromURL(inputFilePath, "_blank", 0, Array())
' Save it in the .ods format in the target folder'
outputFilePath = targetFolder & Left(fileName, Len(fileName) - InStrRev(fileName, ".")) & ".ods"
document.storeAsURL(outputFilePath, Array(MakePropertyValue("FilterName", "calc8")))
' Close the document after saving'
document.close(True)
End If
Next file
End Sub
' Helper function to set properties'
Function MakePropertyValue(n As String, v As Variant) As Object
Dim propValue As Object
propValue = createUnoStruct("com.sun.star.beans.PropertyValue")
propValue.Name = n
propValue.Value = v
MakePropertyValue = propValue
End Function
But when i ran it, i receive this error: BASIC runtime error. Object variable not set.
And process ends on the line “For Each file In fileList”. how can i resolve this?
thank you
