creating an excel file with a formula in it, how to?

[Former Member]

Hi team ABAP,

in one of our applications we are creating an excel file well a ".SLK"-file to be precise.

My goal now was to add a formula in a specific field of said excel file.

When i open the created slk-file it opens with excel and displays the formula in said field.

When i go to that field and hit enter he recognizes it as formula and calculates stuff.

Am i missing something?

How can i tell excel that there is a formula in that field, which i want to be calculated and not shown?

someone got any tipps for me?

[kesavadas_thekkillath]

Hi Florian,

The OLE method supports it, for example execute the below code, you will find the third row getting summed up( A1+A2 ) and gives the value 3.

INCLUDE ole2incl.
DATA: app   TYPE ole2_object,
      book  TYPE ole2_object,
      sheet TYPE ole2_object,
      cells TYPE ole2_object.
DATA: ind TYPE i.

CONSTANTS:max TYPE i value '256'.

TYPES: BEGIN OF ty,
       val(6) TYPE c,
       END OF ty.
DATA:wa TYPE ty.
DATA:itab TYPE TABLE OF ty.


START-OF-SELECTION.

  wa-val = 1.APPEND wa TO itab.
  wa-val = 2.APPEND wa TO itab.
  wa-val = '=A1+A2'.APPEND wa TO itab.

  CREATE OBJECT app 'excel.application'.
  SET PROPERTY OF app 'visible' = 1.
  CALL METHOD OF app 'Workbooks' = book.
  CALL METHOD OF book 'Add'.
  CALL METHOD OF app 'Worksheets' = sheet
  EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'Test1'.
  LOOP AT itab INTO wa.
    ind = max * ( sy-tabix - 1 ) + 1. 
    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = ind.
    IF sy-tabix NE '3'.
      SET PROPERTY OF cells 'Value' = wa-val.
    ELSE.
      SET PROPERTY OF cells 'Formula' = wa-val.
    ENDIF.
  ENDLOOP.

Other than this you can try executing a macro using OLE too for calculating the values.

[Former Member]

Ok, that was VERY Helpful.

Even tho i didnt solve my problem like you explained, as i already have a working routine which creates the SLK file and i was afraid to change it and make two steps back instead of one forward.

I could use your OLE technique to trigger Excel from SAP.

Anyway, here´s how i did it:

I went to my created slk-file using excel (This one was created without formulas, just amounts.

Then i removed the amount from said field and pout in a formula.

After thet i saved my file again.

Then i viewed my file with notepad and extraced the information excel had generated for that field.

This information i am now assigning to the field instead of the amount, yeah without " at the front and end then.

Now my created files work like intended, one field has a formula in it, which respects the amount of another field. once its changed formula calculates new amount for desired field.