[Solved] Formula with commas instead of semicolons

Creating a macro - Writing a Script - Using the API

[Solved] Formula with commas instead of semicolons

Postby Alt1234 » Mon Nov 11, 2019 8:57 pm

I am currently using getFormulaArray to retrieve a cell's formula. Is there any way to get the formula with comma separated arguments rather than semicolon separated?
Thanks
Last edited by Hagar Delest on Tue Nov 12, 2019 4:04 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 6.0.7.3 on Ubuntu 18.04
Alt1234
 
Posts: 2
Joined: Mon Nov 11, 2019 8:51 pm

Re: Retrieving formula with commas instead of semicolons

Postby Zizi64 » Mon Nov 11, 2019 9:17 pm

Always use the semicolon separator in the Calc cells. The AOO Calc and LibreOffice Calc are not MS Excel. The comma is the localized decimal separator in many languages.

otherwise you can substitute any character to an another one programatically in a string.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8537
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Retrieving formula with commas instead of semicolons

Postby JeJe » Mon Nov 11, 2019 9:49 pm

You can use the SUBSTITUTE function to do the replacement in code

Code: Select all   Expand viewCollapse view
   FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")

   dim arr
   redim arr(2)
   
   arr(0)=";aaa;;;;aa;a;"
   arr(1)=";"
   arr(2) =","
   
   res = FuncAcc.callFunction("SUBSTITUTE",arr())
      
      msgbox res


Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 629
Joined: Wed Mar 09, 2016 2:40 pm

Re: Retrieving formula with commas instead of semicolons

Postby Villeroy » Mon Nov 11, 2019 10:16 pm

print ThisComponent.CurrentSelection.FormulaLocal

The FormulaLocal property of a cell displays the formula as shown in the formula bar. It can not have comma separated arguments when the locale uses the comma as decimal separator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27376
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Retrieving formula with commas instead of semicolons

Postby Alt1234 » Tue Nov 12, 2019 1:07 pm

Thanks for the help everyone!
OpenOffice 6.0.7.3 on Ubuntu 18.04
Alt1234
 
Posts: 2
Joined: Mon Nov 11, 2019 8:51 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests