topic OLE using SUMIF in Application Development and Automation Discussions

OLE using SUMIF

Former Member — Fri, 20 Mar 2009 13:47:26 GMT

Hi experts,

I m making an Excel workbook with OLE object.

I would like to use the SUMIF function of excel but it doesn't work

I dont understand why because it works with the simple SUM function 😕

sample :

(you can copy/paste it)

INCLUDE ole2incl.

DATA application TYPE ole2_object.
DATA excel TYPE ole2_object.
DATA workbook TYPE ole2_object.
DATA cell1 TYPE ole2_object.
DATA cells TYPE ole2_object.

CREATE OBJECT excel 'EXCEL.APPLICATION'.
SET PROPERTY OF excel 'Visible' = 1.
GET PROPERTY OF excel 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add'.
GET PROPERTY OF workbook 'Application' = application.
SET PROPERTY OF application 'SheetsInNewWorkbook' = 1. " nbsheets.

CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 5
  #2 = 3.
SET PROPERTY OF cell1 'Value' = 'ok'.

CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 6
  #2 = 3.
SET PROPERTY OF cell1 'Value' = 2.

CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 7
  #2 = 3.
SET PROPERTY OF cell1 'Value' = 2.

CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 5
  #2 = 6.
SET PROPERTY OF cell1 'Value' = 1.


CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 6
  #2 = 6.
SET PROPERTY OF cell1 'Value' = 2.

CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 7
  #2 = 6.
SET PROPERTY OF cell1 'Value' = 4.



CALL METHOD OF excel 'Cells' = cell1
  EXPORTING
  #1 = 8
  #2 = 6.
SET PROPERTY OF cell1 'Value' = '=SUMIF(C5:C7;"ok";F5:F7)'.

The function works when I use it in the excel after generation that's why I dont understand why it doesn't work during the genration !

For the information my excel is in French but it works with the SUM -> SOMME function.

Any idea ?

Thx

Re: OLE using SUMIF

Former Member — Sat, 21 Mar 2009 04:51:07 GMT

Is there an error in your function '=SUMIF(C5:C7;"ok";F5:F7)'? You have semi-colon ( where my Excel requires comma (,) characters in the function - but maybe this is different in French?

I get a SUMIF formula in cell F8 from a copy of your program if I change the last line to


SET PROPERTY OF cell1 'Value' = '=SUMIF(C5:C7,"ok",F5:F7)'.

best wishes

Re: OLE using SUMIF

Former Member — Mon, 23 Mar 2009 12:09:37 GMT

Excactly,

Excel in french version uses ;

Excel in english uses ,

SOMME.SI(x:y;c;v) bercomes SUMIF(x:y,c,v)

Re: OLE using SUMIF

Former Member — Mon, 23 Mar 2009 14:13:00 GMT

Oh, OK, I understand now.

But the code in the example mixed the syntax. It should be


SET PROPERTY OF cell1 'Value' = '=SUMIF(C5:C7,"ok",F5:F7)'.


SET PROPERTY OF cell1 'Value' = '=SOMME.SI(C5:C7;"ok";F5:F7)'.

If that is really not it, maybe there is some problem similar to that described in [this Microsoft forum post|http://social.microsoft.com/Forums/en-US/vbide/thread/3ff4843c-de78-4575-9f9b-b3f2d9edd917] but as I only have English installed I can't test it myself.

best wishes

Re: OLE using SUMIF

Former Member — Mon, 23 Mar 2009 16:21:17 GMT

The macro by default only understand the english version of formula.

SOMME.SI can't be used !