topic Re: XML code transffered mail attachment when opening Excel Formulas not working in Application Development and Automation Discussions

XML code transffered mail attachment when opening Excel Formulas not working

ram_sapsolution — Thu, 03 Feb 2022 13:47:41 GMT

Hi Expert,

when opening excel formulas not working and rest of them working fine in mail attachment.

eg:

Re: XML code transffered mail attachment when opening Excel Formulas not working

Sandra_Rossi — Thu, 03 Feb 2022 15:06:27 GMT

It's SAP forum here, your question is about Microsoft Excel...

You should also post your XML and explain what you want to do.

Anyway, solution is very simple, you do it manually in Excel, you save to XML format, and you see what XML Excel has generated, which is the right syntax of course, and you do the same in your own XML...

Re: XML code transffered mail attachment when opening Excel Formulas not working

ram_sapsolution — Thu, 03 Feb 2022 15:35:11 GMT

Thanks Sandra,

Requirement to convert SAP XML program to excel and to attach mail.

formulas comes from internal table then moving to XML to Excel.

Issue noticed zeros between two cell it suppsed to be =round (F2-M2,2) but program generates and to populate dynamically, we can't change value as per XML generation.

How do change formula expected =Round(F2-M2-2).

Re: XML code transffered mail attachment when opening Excel Formulas not working

abo — Thu, 03 Feb 2022 18:20:00 GMT

I'm afraid to ask how the program generates the XML 😉

Re: XML code transffered mail attachment when opening Excel Formulas not working

Sandra_Rossi — Thu, 03 Feb 2022 19:07:44 GMT

Is "removing 000" a good answer? Joking but what else can we answer?

Re: XML code transffered mail attachment when opening Excel Formulas not working

ram_sapsolution — Fri, 04 Feb 2022 17:00:21 GMT

Hi Sandra,

cool, Zeros are populating via variable in abap program, made as single char but still find issue and formulas are not working after excel atttachment in mail.

eg: values are stored as Field1#Field2#=ROUND(F2-M2,2)#=IF(AND(O3="y",P3>=0),S3,"*") in t_pivot_table

CALL FUNCTION 'SAP_CONVERT_TO_TXT_FORMAT'
EXPORTING
i_line_header = c_x
TABLES
i_tab_sap_data = t_pivot_table "Formulas Contains this table
CHANGING
i_tab_converted_data = is_text "Converted as text format

LOOP AT is_text INTO e_text.

CONCATENATE e_text c_newline INTO w_text.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
text = w_text
IMPORTING
buffer = w_xls_data
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc NE 0.
MESSAGE 'Conversion failed' TYPE 'E'.
ENDIF.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = w_xls_data
append_to_table = c_bin_dat
TABLES
binary_tab = t_xls_data
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc NE 0.
MESSAGE 'Conversion failed' TYPE 'E'.
ENDIF.
ENDLOOP.

lo_docu = cl_document_bcs=>create_document( i_type = c_htm
i_text = t_html
i_subject = w_subj ).

CALL METHOD lo_docu->add_attachment
EXPORTING
i_attachment_type = w_doc_type
i_attachment_subject = w_xls_fname
i_attachment_size = w_size
i_att_content_hex = t_xls_data
i_att_content_text = t_html.

DATA: bcs_exception TYPE REF TO cx_bcs.
w_recpt = w_receivers.
SPLIT w_recpt AT ';' INTO TABLE ts_mailid.
CLEAR: w_recpt.
LOOP AT ts_mailid INTO e_mailid.
w_recpt = e_mailid.
TRY.
lo_recv = cl_cam_address_bcs=>create_internet_address( w_recpt ).
*sending email
lo_send->set_document( lo_docu ).
lo_send->add_recipient( EXPORTING i_recipient = lo_recv ).
lo_send->add_recipient( EXPORTING i_recipient = lo_recv i_copy = c_x ).
CALL METHOD lo_send->set_send_immediately
EXPORTING
i_send_immediately = c_x.
lo_send->send( ).
CATCH cx_bcs INTO bcs_exception.
MESSAGE i865(so) WITH bcs_exception->invalid_value.
ENDTRY.
CLEAR: e_mailid.
ENDLOOP.
COMMIT WORK.

Please suggest if any other idea or needs to be done via abap2xlsx

abap2xlsx, where to find related objects in SAP application, unable to find standard objects to use.

Re: XML code transffered mail attachment when opening Excel Formulas not working

Sandra_Rossi — Fri, 04 Feb 2022 19:29:44 GMT

I don't get how it's possible that it goes from =ROUND(F2-M2,2) to =ROUND(F0002-M0002,2). Where is the code which transforms that? Where is the "XML code"? I think that nobody can understand the question.

NB: unfortunately pivot tables are currently not supported by abap2xlsx.

NB: Please edit your comment, select your code and press the button [CODE], which makes the code appear colored/indented, it will be easier for people to look at it. Thank you!