[Solved] Using SUMIF in macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
tanglefoot
Posts: 3
Joined: Thu Feb 08, 2018 7:06 am

[Solved] Using SUMIF in macro

Post by tanglefoot »

Total newbie here trying to understand how to format the quoted text in a simple macro I'm attempting to create. I'm receiving a syntax error with the following text value:
args3(0).Value = "=SUMIF(A2:A5000;"01-jan-2018.............";P2:P5000)"
I've attempted to escape the quotes within the function, but so far haven't found anything that works.
TIA!
Last edited by Hagar Delest on Thu Feb 08, 2018 10:37 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.3
Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using SUMIF in macro

Post by Zizi64 »

Here is an example how to call calc built in functions:
viewtopic.php?f=45&t=75288
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
tanglefoot
Posts: 3
Joined: Thu Feb 08, 2018 7:06 am

Re: Using SUMIF in macro

Post by tanglefoot »

Thanks zizi64. I'm not sure this addresses the issue I'm having though. I'm simply trying to have a macro insert a function into a cell. The issue I'm having is with quotation marks within the function which is causing a syntax error. I assume the quotes within the function need to be escaped so the syntax error isn't thrown when the macro tries to assign the function text to the args3(0).Value. Maybe my newbiness is preventing me from seeing the obvious answer.
OpenOffice 4.1.3
Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using SUMIF in macro

Post by FJCC »

In other contexts, multiple quotation marks are used to embed a quotation mark within a string, as in this example from a database query

Code: Select all

sSQL = "INSERT INTO ""AllTran"" (""Account"", ""Date"", ""Amount"", ""Particulars"", ""Code"", ""Reference"","
where every table name and column name has to be quoted.

Also, a cell object has a setFormula() method that would be a cleaner way to do this, but we need to know more details about what you are trying to do.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using SUMIF in macro

Post by Zizi64 »

I'm simply trying to have a macro insert a function into a cell. The issue I'm having is with quotation marks within the function which is causing a syntax error.
Sorry, I misunderstood your problem.

You can try append the string based on its parts, and the code of the " character : & Chr(34) &

Code: Select all

args3(0).Value = "=SUMIF(A2:A5000;" & Chr(34) & "01-jan-2018............." & Chr(34) & ";P2:P5000)"
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
tanglefoot
Posts: 3
Joined: Thu Feb 08, 2018 7:06 am

Re: Using SUMIF in macro

Post by tanglefoot »

Thanks again Zizi! That worked. I had tried something similar, but obviously didn't have it formatted correctly. That helps a lot.
Thanks!
OpenOffice 4.1.3
Windows 10
Post Reply