How to switch the reference style (formula syntax) in macro?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

How to switch the reference style (formula syntax) in macro?

Post by eeigor »

In the end, @Villeroy, help me figure out this macro.

Excel VBA

Code: Select all

Sub ToggleR1C1
    Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub
Brevity is the sister of talent.

I want this macro in LibreOffice Calc.

The problem is that the corresponding macro does not reload the configuration file. DOES NOT WORK. RESTART REQUIRED.
Everything works through the options window (see screenshot).

Code: Select all

'	<item oor:path="/org.openoffice.Office.Calc/Formula/Syntax"><prop oor:name="Grammar" oor:op="fuse"><value>2</value></prop></item>
'	Calls GetRegistryKeyContent()
Sub DisplayFormulaSyntax()  'Grammar
	Dim sNodePath As String  'Syntax

	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	sNodePath = "/org.openoffice.Office.Calc/Formula/Syntax"
	MsgBox "Property Value:  " _
	 & GetRegistryKeyContent(sNodePath).getByName("Grammar") _
	 , MB_ICONINFORMATION, "Formula Syntax • Grammar"
End Sub

Sub Test_SetFormulaSyntax()
	Call SetFormulaSyntax(2)  '0=Calc A1; 1=Excel A1; 2=Excel R1C1
End Sub

Sub SetFormulaSyntax(nValue As Integer)  'Grammar
	Dim oConfig As Object, oRegKey As Object
	Dim aProps(0) As New com.sun.star.beans.PropertyValue

REM	GlobalScope.BasicLibraries.LoadLibrary("Tools")  'GetRegistryKeyContent()

	aProps(0).Name  = "nodepath"
	aProps(0).Value = "/org.openoffice.Office.Calc/Formula/Syntax"

'	Doesn't lead to the desired result.

'	ReDim Preserve aProps(1)
'	' If False, the cache must operate in write-through mode,
'	' where updates are written to persistent storage at once –
'	' that is before com.sun.star.util.XChangesBatch.commitChanges() returns.
'	aProps(1).Name  = "enableasync"
'	aProps(1).Value = False


	oConfig = createUnoService("com.sun.star.configuration.ConfigurationProvider")
'	Xray oConfig
	oRegKey = oConfig.createInstanceWithArguments("com.sun.star.configuration.ConfigurationUpdateAccess", aProps())
'	Xray oRegKey
	oRegKey.setPropertyValue("Grammar", nValue)  'IT WORKS!
	oRegKey.commitChanges()  'DOES NOT WORK!
'	oConfig.flush()  'DOES NOT WORK!
End Sub
An ancient mistake. And so with many properties. When will the workaround appear?

UPD:
Run the SetFormulaSyntax. The value in the options window will be changed, but the sheet will remain in the same style. However, if you click the Apply or OK button in the Options window, the style on the sheet will change.
Снимок экрана от 2021-07-10 08-08-08.png
There is a problem with the GetRegistryKeyContent function and with the ConfigurationProvider service that it uses: it changes options in the configuration file, but does not update the application. In the above example, even the value in the options window changes, but not on the sheet.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to switch the reference style (formula syntax) in ma

Post by Villeroy »

eeigor wrote:Brevity is the sister of talent.
In this partiular case it is the daughter of money. VBA is a huge, fat layer, more like a second office suite rather than a collection of interfaces.
Please use MS Excel if it serves you so well. I have no clue why anybody wants to switch the formula syntax during runtime. If you like R1C1, then turn it on. If not, turn it off. The ADDRESS function can return R1C1 and INDIRECT can interprete R1C1.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: How to switch the reference style (formula syntax) in ma

Post by eeigor »

No not like this. This code is nothing more than an eloquent example. There are many properties to check/toggle.
Some are included in "com.sun.star.sheet.GlobalSheetSettings", but all others have a problem. We solve it.

Why does the "Apply" button of the options window work, but the Tools.GetRegistryKeyContent() function does not? Does it do anything useful at all?

Code: Select all

oRegKey.commitChanges()  'DOES NOT WORK!
What am I doing wrong?

MS Excel has served me well. But this is in the past. Now it only helps me to study LO Calc. It really helps: look there, and it almost always works here. With a few exceptions. For example, a question in this thread.

RESTART REQUIRED. That is a problem! Who can solve it? Change any of the many options.

UPD:
@Villeroy, really LO Calc is Excel's little brother. Otherwise why is it trying to be so similar to it (in terms of compatibility)?
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to switch the reference style (formula syntax) in ma

Post by Villeroy »

eeigor wrote:@Villeroy, really LO Calc is Excel's little brother. Otherwise why is it trying to be so similar to it (in terms of compatibility)?
Under the hood is a completely different application. They are no brothers. They are aliens form different galaxies wrapped in similar space-suites. Excel's footprint is larger than the footprint of the whole LO suite with databases and 2 macro languages. THIS IS NOT A MULTI-BILLION DOLLAR PROJECT!

Try this:

Code: Select all

Function setSetupValue(sNodePath$, sProperty$, val) as Boolean
on error goto exitErr
Dim bReturn as Boolean, xconfig, xAccess
dim aArgs(0) as  Object
dim aPropValue as new  com.sun.star.beans.PropertyValue
	xconfig = CreateUnoService("com.sun.star.configuration.ConfigurationProvider")
	aPropValue.Name = "nodepath"
	aPropValue.Value = sNodePath
	aArgs(0) = aPropValue
	xAccess = xconfig.createInstanceWithArguments("com.sun.star.configuration.ConfigurationUpdateAccess",aArgs())
	xAccess.setPropertyValue(sProperty, val)
	xAccess.commitChanges()
	bReturn = True
exitErr:
	setSetupValue = bReturn
End Function
It is a 12 year old helper routine of mine and returns True in case of success.
setSetupValue("/org.openoffice.Office.Calc/Formula/Syntax", "Grammar", 1) actually changes the value in registrymodifications.xcu which the built-in macro does not.
A restart of the Calc component (close all spreadsheets) is required anyway.
For read-only access:

Code: Select all

Function getSetupNode(sNodePath$)
Dim aConfigProvider, oNode, args(0) As new com.sun.star.beans.PropertyValue
	aConfigProvider = createUnoService("com.sun.star.configuration.ConfigurationProvider")
	args(0).Name = "nodepath"
	args(0).Value = sNodePath
	getSetupNode = aConfigProvider.createInstanceWithArguments("com.sun.star.configuration.ConfigurationAccess", args())
End Function

Function getSetupValue(sNodePath$,sProperty$)
Dim oNode
	oNode = getSetupNode(sNodePath)
	getSetupValue = oNode.getbyname(sProperty)
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to switch the reference style (formula syntax) in ma

Post by Villeroy »

And this is how to utilize the built-in macro:

Code: Select all

	globalscope.basiclibraries.loadlibrary("Tools")
	key = Tools.Misc.GetRegistryKeyContent("/org.openoffice.Office.Calc/Formula/Syntax", True)
	key.Grammar = 2
	key.commitChanges()
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: How to switch the reference style (formula syntax) in ma

Post by eeigor »

Villeroy wrote:A restart of the Calc component (close all spreadsheets) is required anyway.
That's the problem. Thank you, of course.

Andrew Pitonyak described my problem exactly in 2006:
http://openoffice.2283327.n4.nabble.com ... 68586.html

> If I use the API, however, I must restart OOo before the change is seen.
That's because OOo internally uses a number of caches for often-used
configuration settings (originally for performance reasons, not sure if
this still holds). Unfortunately, those caches do not notify changes of
a third party. Vote and lobby for
http://www.openoffice.org/issues/show_bug.cgi?id=51626 to change this.

But there is no result. Strange, however.

Please, who knows, comment on this question.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: How to switch the reference style (formula syntax) in ma

Post by JeJe »

You could become a developer and contribute to the project by fixed these issues you have. The adage being if you want something done do it yourself. It may be a very long wait otherwise...
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: How to switch the reference style (formula syntax) in ma

Post by JeJe »

Failing that, presumably XRestartManager will allow you to restart by macro.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: How to switch the reference style (formula syntax) in ma

Post by eeigor »

Another example. But everything is about the same...
https://bugs.documentfoundation.org/sho ... ?id=132145
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to switch the reference style (formula syntax) in ma

Post by Villeroy »

It is not quite the same. You can access this property through service GlobalSheetSettings which takes effect immediately.

Code: Select all

Sub toggleExpandReferences()
Dim b As Boolean
gss = createUnoService("com.sun.star.sheet.GlobalSheetSettings")
'oMri = createUnoService("mytools.Mri")
'oMri.inspect(gss)
b = Not gss.ExpandReferences
gss.ExpandReferences = b
msgbox "Expand references now is "& b
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply