12-02-2010 12:51 AM
I am using cl_bcs based functionality to send email to sap inbox with Excel attachment. Everything is Ok, the only problem is the data which I am getting for some fields got trimmed in excel. e.g in internal table i have '00001' and when I see the data in my generated excel sheet, its just '1'...leading zero's are gone.
cl_bcs_convert=>string_to_solix(
EXPORTING
iv_string = lv_string1
iv_codepage = '4103' "suitable for MS Excel, leave empty
iv_add_bom = 'X' "for other doc types
IMPORTING
et_solix = binary_content
ev_size = size ).
I am using above mentioned method for conversion. I am using concatenate statement to move the data to lv_string1 inside a loop.
Could you please suggest the solution.
Thanks,
12-02-2010 2:48 AM
Excel removes the leading zeroes by default. To retain the leading zeroes, add single apostrophe before the number
* Add single apostrophe in the variable
DATA: v_var TYPE C VALUE "'".
* Concatenate v_var to number field v_number
CONCATENATE v_var v_number INTO v_number.
12-02-2010 9:00 PM
Hi Sim,
By doing this, my leading zero are back but single quote is displayed before my value. Please suggest how to get rid of this single quote.
Thanks,
12-03-2010 4:44 AM
If you can see the single apostrophe, then that is the excel problem and you need to re-format cell in excel to text format or create a excel macro to format cell and text.
Select colum and then 'Ctrl' + 1. In the 'Number' tab, Number -> text
Apostrophe solution has worked for me so far. You can try it in excel. by entering '0001 and it will retain the zeroes without apostrophe. Apostrophe in excel is a way to retain the number as string in excel
You can even try the following. I have not used it, but you can tell us if it works too
[Truncate leading zeroes using REPLACE|http://www.sapdev.co.uk/reporting/email/attach_zeros.htm]
12-03-2010 7:52 PM
Did you do some extra settings to get rid of apostrophe or it was automatically without this. Yes I know if I do it manually with apostrophe, it works. But when I pass thru the program - it would't.
I will try the other option and let u know.
Thanks,
12-04-2010 1:13 PM
For me it works without any settings. But I know that it did not work for some people and they had to format their excel cell to text and it worked