open xlsx in libreoffice macro

Question

I am trying to open an excel file from libreoffice calc's macro but I keep coming accross errors. This is my first time using libreoffice macro.

Here is my first attempt, was from a website where someone asked the same question so I tried there code: https://forum.openoffice.org/en/forum/viewtopic.php?f=5&t=17075

Target = "C:\Users\RKerrigan\Documents\Scripts\Mailerreportgenerator\Miller Radiology Mailers Template.xlsx"
    TargetURL = convertToURL(Target)
    Empty() = Array()
    TestDoc = StarDesktop.loadComponentFromURL(TargetURL, "_blank", 0, Empty())

But the error I got was regarding line 6 (Empty() = Array()):

BASIC runtime error.
'382'
This property is read-only.

So then I searched around and found this link from stackoverflow: https://stackoverflow.com/a/65201568/16953756 Which brings you to this example:https://help.libreoffice.org/6.4/en-US/text/sbasic/shared/stardesktop.html

Dim docURL As String
Dim doc As Object, docProperties()
docURL = ConvertToURL("C:\\Users\\RKerrigan\\Documents\\Scripts\\Mailerreportgenerator\\Miller Radiology Mailers Template.xlsx")
Rem com.sun.star.frame.Desktop
doc = StarDesktop.LoadComponentFromURL(docURL, "_blank", 0, docProperties)

But I got another error saying:

BASIC runtime error.
'1'

Type: com.sun.star.lang.IllegalArgumentException
Message: Unsupported URL <file:///C://Users//RKerrigan//Documents//Scripts//Mailerreportgenerator//Miller%20Radiology%20Mailers%20Template.xlsx>: "type detection failed"

Can someone help me open this file in libreoffice macro? "C:\Users\RKerrigan\Documents\Scripts\Mailerreportgenerator\Miller Radiology Mailers Template.xlsx"

I thought it was something quotes so I tried double slashes and that didn't work either.

score 0 · Accepted Answer · answered Oct 25 '21 at 09:20

Please try

    Sub OpenRadiologyMailers()
    Dim sFilename As String 
    Dim oSourceSpreadsheet As Variant 
        sFilename = ConvertToURL("C:\Users\RKerrigan\Documents\Scripts\Mailerreportgenerator\Miller Radiology Mailers Template.xlsx")
        If Not FileExists(sFilename) Then 
            MsgBox("File not found!")
            Exit Sub 
        EndIf 
        
        GlobalScope.BasicLibraries.loadLibrary("Tools")
        oSourceSpreadsheet = OpenDocument(sFilename, Array())
        If IsEmpty(oSourceSpreadsheet) Then 
            MsgBox("The file may be open in another application",0,"Failed to load file")
            Exit Sub 
        EndIf 
    '       ... further actions with the document oSourceSpreadsheet
    End Sub

Thanks to your code, I got it to work! I had to get rid of the if statement `If IsEmpty(oSourceSpreadsheet) Then MsgBox("The file may be open in another application",0,"Failed to load file") Exit Sub EndIf ` As it was causing an error. Thanks again for your help!! — , Nov 10 '21 at 19:18

open xlsx in libreoffice macro

1 Answers1