[Solved] Using SUMIF in macro

Creating a macro - Writing a Script - Using the API

[Solved] Using SUMIF in macro

Postby tanglefoot » Thu Feb 08, 2018 7:17 am

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
tanglefoot
 
Posts: 3
Joined: Thu Feb 08, 2018 7:06 am

Re: Using SUMIF in macro

Postby Zizi64 » Thu Feb 08, 2018 9:50 am

Here is an example how to call calc built in functions:
https://forum.openoffice.org/en/forum/v ... 45&t=75288
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7815
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using SUMIF in macro

Postby tanglefoot » Thu Feb 08, 2018 5:08 pm

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
tanglefoot
 
Posts: 3
Joined: Thu Feb 08, 2018 7:06 am

Re: Using SUMIF in macro

Postby FJCC » Thu Feb 08, 2018 5:30 pm

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   Expand viewCollapse view
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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7086
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using SUMIF in macro

Postby Zizi64 » Thu Feb 08, 2018 5:52 pm

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   Expand viewCollapse view
args3(0).Value = "=SUMIF(A2:A5000;" & Chr(34) & "01-jan-2018............." & Chr(34) & ";P2:P5000)"
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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: 7815
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using SUMIF in macro

Postby tanglefoot » Thu Feb 08, 2018 6:06 pm

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
tanglefoot
 
Posts: 3
Joined: Thu Feb 08, 2018 7:06 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests