Hello community,
First, I apologize if my first posts do not fully comply with the forum’s etiquette, I’m new here and I will try to limit my posts to a single subject.
Having said that, I think it’s necessary to give a background since I don’t believe this is superfluous information, but it would rather help mods and admins better understand my use cases. I’ll try to keep things short.
Background
I’m a new user switching from Ms Office and I’m facing a few challenges to completely get off Ms platform since I am a power user of Ms Excel, its macros and its Power Query features. Unfortunately however, I’m not a full stack developer so I can’t work on multiple IDEs so I’m totally reliant on whatever IDE the platform provides me.
I do have a lot of automated workflows already on Microsoft Office and I would like to carry them over for me and my staff to Libre Office, since it’s the best I’ve tested thus far. My automations are heavily reliant on Excel’s ListObject object since it provides my data with structure instead of having to scrape through the worksheets and identify ranges, headers and then loading them into arrays, and dictionaries.
I have searched the documentation and help on this matter, but the documentation leaves a lot to be desired for those looking for advanced feature or details.
I thought the Libre Office support forum could point me out to these resources so I can read the documentation for myself in case it exists.
List of Features
-
I noticed that
defined rangeswhich is equivalent to Excel’stablesdo not support relative row references such as[@Column1]. I scanned through the documentation on defined ranges and found nothing on this subject. -
Is there a way for
defined rangesto automatically update their ranges? I know I can -
I see that Libre Office supports JavaScript from
Macros>Tools>Organize Macros. However, I couldn’t figure how to use it and also, the documentation only how to run python but not JavaScript. Are there any extensions that I need to install first? -
I couldn’t find the documentation for
defined rangeobject in the documentation, which leads me to my biggest issue which is next. -
I recorded a macro to learn more about the objects, but the resulting script looks like BASIC isn’t integrated to the runtime but rather, it uses an API of another scripting language (I assume its BeanShell but I could be wrong) and the result is very cryptic:
sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectColumn", "", 0, Array())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "C"
dispatcher.executeDispatch(document, ".uno:InsertCell", "", 0, args4())
end sub
Is there a way to expose the document objects at runtime, or is using this API the only way to interact with calc documents objects?
