OLE using SUMIF

[Former Member]

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

[Former Member]

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

Ed

[Former Member]

Excactly,

Excel in french version uses ;

Excel in english uses ,

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

[Former Member]

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)'.

or

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

Ed

[Former Member]

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

SOMME.SI can't be used !