Vbscript open calc ods with loadComponentFromURL Hidden True error

[alexmbra]

When I try to open a calc ods file with the hidden as true, loadComponentFromURL returns “Nothing”.
I need to read some cells values without really openning Libreoffice Calc.
Does anyone knows how to make this to work?

Thanks

 Sub ReadODS()
      Dim objDocument 
       Set objServiceManager = WScript.CreateObject("com.sun.star.ServiceManager") 
       Set StarDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop") 
       Dim args0(0)
       Set args0(0) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue") 
       Set args0(0) = MakePropertyValue("Hidden", true)
       cCSVURL = "file:///C:\validar\25-05-2022.ods"
       Set objDocument = StarDesktop.loadComponentFromURL(cCSVURL, "_blank", 0, args0 )  
       set oSheet = objDocument.getSheets().getByName( "Inspeção" )
       Set objCell  = oSheet.getCellByPosition( 1, 2 )
       msgbox objCell  

    Set objServiceManager = Nothing
    Set StarDesktop = Nothing
    Set oEnum = Nothing
    objDocument.Close (True)
    Set objDocument  = Nothing
    Set oSheet = Nothing
    Set oCell = Nothing
End Sub

Function MakePropertyValue(cName, uValue)
	 Dim oStruct
	 Set oServiceManager = WScript.CreateObject("com.sun.star.ServiceManager") 
	 Set oStruct = oServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
	 oStruct.Name = cName
	 oStruct.Value = uValue
	 Set MakePropertyValue = oStruct
End Function

[mikekaganski]

It’s difficult to say anything when there’s no information what MakePropertyValue is (and why you would need two assignments to args0(0)), or what is in Excelfile (is it proper URL-encoded string, or is it a system path that could (would) be invalid in URL). Please provide enough information so that others could test and repro locally, without the need to guess/reconstruct missing bits, including the excel file itself (could it be specific to the file? even if no, just create an empty test file, that you know to show the problem for you).

FTR: this VBS works for me on Windows 10 (gives first sheet’s A1 text):

Set objServiceManager = WScript.CreateObject("com.sun.star.ServiceManager")
Set StarDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
Dim args0(0)
Set args0(0) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
args0(0).Name = "Hidden"
args0(0).Value = true
''' Use URLTransformer service to build a proper URL from arbitrary input '''
Set UrlTransformer = objServiceManager.createInstance("com.sun.star.util.URLTransformer")
Set Url = objServiceManager.Bridge_GetStruct("com.sun.star.util.URL")
Url.Complete = "D:\My Documents\foo bar.xls"
UrlTransformer.parseSmart Url, ""
Set objDocument = StarDesktop.loadComponentFromURL(Url.Complete, "_blank", 0, args0 )
Set objSheet = objDocument.Sheets.getByIndex(0)
Set objCell = objSheet.getCellByPosition(0, 0)
MsgBox objCell.String
objDocument.dispose()
''' Terminate desktop when there's no components - avoid terminating a GUI session '''
If Not StarDesktop.getComponents().hasElements() Then StarDesktop.terminate()

[alexmbra]

Thanks mikekaganski

I just changed the code on the first post to show everything. And your code helped me to find the problem.

If I use Set args0(0) = MakePropertyValue(“Hidden”, false), the code works fine. It opens and show the ods file and read the cell value. But if I change the false to true, the objDocument returns as nothing.
This is probably a bug.
Now, your code works fine, even if I change “args0(0).Value = true” to “args0(0).Value = false”. So, I found out that the problem is with the URL path. If I pass the URL like this: “file:///C:\validar\25-05-2022.ods”, it works only with the “args0(0).Value = false”, even with your code.

So, using Url.Complete = “D:\My Documents\foo bar.xls”
UrlTransformer.parseSmart Url, “” ; works with the Hidden = true. And now everything is working here, so thank you.