Opening another file doesn't give me the right object

English
,
#1

Hello,

A few weeks ago, I switched from Excel to LibreOffice Calc. So I’m rewriting my macro’s and until now, I didn’t have to much problems, but I found myself stuck on a macro to retrieve data from another workbook, specifiquely when trying to reach te cells of the other workbook. My original Excel-function, to open another workbook, looks like this:

Function GetWorkBook(ByVal myFileName As String) As Workbook
Dim XLApp As New Excel.Application, cFile As String
On Error GoTo Err
If Dir(myFileName) = vbNullString Then Exit Function
Set GetWorkBook = XLApp.Workbooks.Open(Filename:=myFileName, ReadOnly:=True)
Err:
End Function

My first attempt was to use this function as it is, using the “Option VBASupport 1” statement and surprisingly, it seemed to work. I received an object, but looking at it more closely, dissapointment came in. I couldn’t reach the different sheets in this (workbook?) object. The sheets collection gives me an array of strings, where I expected objects.

Next step was to try to write a LibreOffice equivalent, which resulted in this code:

Function GetAnotherDocument(Byval cFile as String) as Object
Dim ui as variant
If (Not GlobalScope.BasicLibraries.isLibraryLoaded(“ScriptForge”)) Then
GlobalScope.BasicLibraries.LoadLibrary(“ScriptForge”)
End If
Set ui = CreateScriptService(“UI”)
set GetAnotherDocument = ui.Opendocument(cFile, ReadOnly := True, macroexecution := 1)
End Function

Unfortunately, the result was the same. Also, the sheets collection keeps giving me an array of strings, not objects (?). So I still can’t reach the underlying cells, to retrieve values from. I’m afraid I don’t really understand why I’m not getting the right object.

What am I doing wrong?

(LibreOffice 7.5.9.2 on Windows 11)

#2

You have not studied the API functions…
Hwere isa a sample code snippet, how you can reference the actual spreadsheet document, and how to reference an another document:

   Dim oVal(1) As New com.sun.star.beans.PropertyValue
   Dim oDoc, oAnotherDoc as object
   Dim oSheets, oAnotherSheets as object
      
   oVal(0).Name = "Hidden"
   oVal(0).Value = True
   oVal(1).Name = "MacroExecutionMode"
   oVal(1).Value = com.sun.star.document.MacroExecMode.ALWAYS_EXECUTE_NO_WARN
   
   oDoc = ThisComponent
   oAnotherDoc = StarDesktop.loadComponentFromURL("file:///c:/example.ods", " blank", 0, oVal())

   oSheets = oDoc. Sheets
   oAnotherSheets = oAnotherDoc.Sheets

   oSheet = oSheets.getByIndex(0)
   oAnotherSheet =  oAnotherSheets.getByName("Sheet1")
#3

Thank you for your reply. Your code works perfectly (as I could expect. :slightly_smiling_face:).

Due to my lack of experience, I don’t know much about the API functions (yet). But actually, I was doing some further research and had the idea to investigate the ScriptForge library. There I saw that de macro “OpenDocument” uses the function StarDesktop.loadComponentFromURL() to get to the other file.

So I’m left with two other questions:

  • If I can use “StarDesktop.loadComponentFromURL()”, what purpose serves de function “ui.OpenDocument” in the ScriptForge library?

  • I don’t wont to just copy some code, but I always try to understand what is going on. I have noticed that ui.OpenDocument() returns an object which it itself had received from StarDesktop.loadComponentFromURL(). So why don’t I receive the same object whether I use ui.OpenDocument() or StarDesktop.loadComponentFromURL()?

#4

Sorry, I never used it.

#5

Welcome!

This is a very correct approach. This will help you - I’m sure it will really help! - Andrew Pitonyak’s book. There, indeed, the meaning of the given listings is explained briefly (one or two paragraphs), but comprehensively. There you will find examples for solving a wide variety of problems.
And yes, the authors of ScriptForge did a tremendous job creating their library. It’s a pity that it’s like jazz - the real pleasure is felt not by the audience, but by the performer. I don’t know people who have learned all the methods in this library and successfully used them in everyday programming.

2 Likes
#7

Shouldn’t it be simpler?
I think it will work if you omit On Error and xlApp.

Function GetWorkBook(ByVal myFileName As String) As Workbook

If Dir(myFileName) = vbNullString Then Exit Function
Set GetWorkBook =Workbooks.Open(Filename:=myFileName, ReadOnly:=True)

End Function

#9

@ JohnSUN

Please excuse me if I have given the (wrong) impression that I do not appreciate the work of the authors of the libraries. I certainly do. You can consider me as someone from the audience who certainly enjoys the jazz music.

Nevertheless, I would be happy if someone can explain to me why I receive different objects whether I use ui.OpenDocument() or StarDesktop.loadComponentFromURL()?

#10

@YOKOMAYA3

I don’t have a problem with my VBA function. It works very well. It was with the LibreOffice function that I had a problem, which now has been solved by Zizi64’s code.

#11

Of course, I answered it as a librecalc function. I answered that it will work properly in librecalc if you exclude objects and properties that do not exist in librecalc.

#12

Oh, I see now what you mean… Excuse me.
Well, the reason for using xlApp is to make the whole thing happen in the background without the user noticing it. When the document opens in the current session, it becomes active. Also, I think an error handler is necessary because the file format may be unsupported. We never know.
However, I tried your solution, but unfortunately, I can’t reach the sheets of the object that is returned.
Thanks anyway.

#13

I understand that you want to run EXCEL in the background.

However, you aren’t using stepping to debug your code, are you?

If you trace it by step execution
Set xlApp=
When the line is exceeded, xlapp has only a variable type called Excel.Application in the observer.
It is given and there is nothing inside.

In other words, LibreCalc does not have a type called Excel.Application, so the type name is just registered.
At least As New Excel.Application doesn’t work.
So I deleted it.

On Error GoTo is used for code that has not been confirmed to work like this.
I think this greatly impedes the ability to identify the cause and location of errors and debug them.

By the way, when it says that you are unreachable, the code below is
Would you like to display the first sheet name?

dim wb as Workbook
set wb= GetWorkBook(“yourfilepath”)
msgbox wb.worksheets(1).name

If not, what is the format of the book?
I confirmed that it opens with xlsx, xlsm, and ods.

#14

Do not confuse Basic objects and UNO objects. ScriptForge is a library/API that creates Basic (and Python) objects which simplify the work of the user by - but not only ! - encapsulating UNO objects and providing higher level features.

To come back to your specific question … :
ui is a Basic variable that refers to a Basic object with its own set of methods and properties.
StarDesktop refers to a UNO object.
doc = ui.OpenDocument(...) returns another Basic object with, again, its own set of methods and properties. One of those properties is XComponent.
doc.XComponent returns the same UNO object as StarDesktop.loadComponentFromURL(...).

#15

In other words:
Script-Forge is a superfluous and useless invention which confuses beginners more than it helps them, and is already rejected by experienced users because of its twisted syntax!

#16

Well, this is your opinion …

However I would like to emphasize

  • that the LibreOffice UNO API has never confused any new user so far :smile:, he simply has to dig in Pytoniak’s (btw excellent) book which is only 677 pages long or in Marcelly’s one which is 926 pages long (maybe french is more verbose than english ?).
  • that ScriptForge has not the pretentiousness to replace UNO but to be complementary. While UNO is designed to make users code as if they were programmers of LO source code, for many common or repeatable actions, they could find useful to have an alternative and start faster.
  • After your remark about the “twisted syntax” of ScriptForge, I conclude that you a priori prefer to struggle with the +/- 20,000 entries in the UNO API. Personally I let this privilege to “experienced users”.
#17

How you will know and use the lots of properties, methods, interfaces of the ScriptForge objects, if you can not use the pure UNO/API objects?

#18

Let’s be concrete first : you find a list of “the lots of properties, methods, interfaces of the ScriptForge library” WITH THIS LINK.

An example: to store in an array the content of a table, query or sql statement, you can use the GetRows() method found in the database service. The cake is baked simply with:

    db = CreateScriptService("database", "/home/.../mydb.odb")
    a = db.GetRows("SELECT fld1, fld2 FROM ... ORDER BY ...", Header := True)

Under the hood, GetRows() will create a statement from the database connection, examine the database and columns metadata to know the names and types of the columns, set parameters (EscapeProcessing …), get the data with the appropriate getXXX() UNO method, insert the headers, close what needs to be closed, … In other words, GetRows() is a generic function that is designed to work for any SELECT SQL targeting any RDBMS.

You are completely free to jump into UNO if this has your preference and write a similar code by yourself, f.i.

    db = CreateScriptService("database", "/home/.../mydb.odb")  ' ... or use CreateUnoService ...
    conn = db.XConnection
    stat = conn.createStatement(...)
    ' etc ...

=> ScriptForge is a collection of such simple or complex generic functions that are often relevant in the context of Office automatic tools. ScriptForge coexists with and rests on UNO, of course, because UNO encompasses all features of LO. But not all of them need to be commonly automated.

NB: above codes are written in Basic. Minor changes make them executable in Python.

#19

Thanks to all for your replies.

I see that my question about a particular object has evolved into a discussion between pros and cons of the LO libraries. This might be a discussion without an end because there will always be people who are for it and others who are against it. Although interesting, it was not the intention of my question.

Allow me to return to my original question, please. Firstly, I want to emphasize that I don’t have an Excel problem. I only posted my Excel Function for informational purposes. I switched to LO, so when I’m finished rewriting my Excel macro’s into LO macro’s (which may take some time), Excel will be history.

My problem is (was) in fact rather simple: I’m running a macro and somewhere on my HD there is another file Temp.ods from which I want to retrieve some data into the macro. That’s it.


Eventually this code, which is based on Zizi64's code, does the job:

Function GetAnotherDocument(Byval cPath As String) as Object Dim oDesk as Object Dim args(1) as new com.sun.star.beans.PropertyValue 
args(0).Name = "ReadOnly"
args(0).Value = True
args(1).Name = "MacroExecutionMode"
args(1).Value = com.sun.star.document.MacroExecMode.NEVER_EXECUTE

oDesk = createUnoService("com.sun.star.frame.Desktop")
GetAnotherDocument = oDesk.LoadComponentFromURL(ConvertToURL(cPath), "_blank" ,0, args())
End Function

The function I tried to use before was this one:

Function GetMeSomeDocument(Byval cPath As String) As Object
Dim oDoc As object, ui As Object
With GlobalScope.BasicLibraries
If Not .IsLibraryLoaded(“ScriptForge”) Then
.LoadLibrary(“ScriptForge”)
End If
End With
Set ui = CreateScriptService(“UI”)
set oDoc = ui.Opendocument(cPath, ReadOnly := True, macroexecution := 1)
set GetMeSomeDocument = oDoc
End Function

So I ran some tests, which where these:

'Test with UNO-object.
Sub test1
Dim oWb As Object, cPath As String
cPath = “d:\onedrive\LibreOffice\temp.ods”
set oWb = GetAnotherDocument(cPath)
msgbox oWb.sheets.getByName(“Sheet2”).getCellRangeByName(“B3”).String
end sub


'Test with OpenDocument function from Scriptforge library.
sub test2
Dim oWb As Object, cPath As String
cPath = “d:\onedrive\LibreOffice\temp.ods”
set oWb = GetMeSomeDocument(cPath)
msgbox oWb.sheets.getByName(“Sheet2”).getCellRangeByName(“B3”).String
end sub

While Sub test1 works fine, Sub test2 throws a runtime error “Object variable not set”. In my opinion, as a beginner, the received object should be the same in both cases. Screenshots showing the inspection of the received objects are added.

So I had a look at the OpenDocument function in the library. On lines 847 to 851 I found the following code:

Set oComp = StarDesktop.loadComponentFromURL(sFile, “_blank”, 0, vProperties)
If Not IsNull(oComp) Then Set oOpen = CreateScriptService(“SFDocuments.Document”, oComp)
Finally:
Set OpenDocument = oOpen



This means that the OpenDocument function uses the same loadComponentFromURL-method, which is consistent with the remark from JPLED when he says “ScriptForge coexists with and rests on UNO…”.


Nevertheless, with the Scriptforge function I do not receive the document-object I was hoping for, while with the UNO-service I do.


Stays a mystery to me…



screenshot test1
screenshot test11600×817 85.8 KB

screenshot test2
screenshot test21600×817 80.9 KB

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.