Excel VBA
Code: Select all
Sub ToggleR1C1
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub
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
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. 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.