06-12-2009 6:41 AM
Hi abapers,
I have a scenario as follows:
In my internal table the records are as follows.
col1 col2 col3
1 a1 b1
2 a2 b1
3 a3 b2
4 a4 b3.
Now want i need is i have to break the records on the basis of col3 . All b1 rows are to be taken and dumped in a excel. b2 rows are to be taken and dumped in an excel and similarly for b3. And the name of excel will should be b1, b2 , b3 respectively.
How do i go for the case?
Help would be appreciated..
Regards,
Navin C
Edited by: navin.c on Jun 12, 2009 7:41 AM
06-12-2009 8:08 AM
put col3 as the first field in your itab.
TYPES:BEGIN OF ty3,
col3(3) TYPE c,
col2(3) TYPE c,
col1(3) TYPE c,
END OF ty3.
DATA:itab3 TYPE TABLE OF ty3.
DATA:it_xl TYPE TABLE OF ty3.
DATA:wa3 TYPE ty3.
DATA:file_name TYPE string.
SORT itab3 BY col3.
LOOP AT itab3 INTO wa3.
APPEND wa3 TO it_xl.
AT END OF col3.
READ TABLE itab3 INTO wa3 INDEX sy-tabix.
CHECK sy-subrc = 0.
file_name = wa3-col3.
PERFORM download_xl USING file_name.
REFRESH it_xl.
ENDAT.
ENDLOOP.
*&---------------------------------------------------------------------*
*& Form download_xl
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_FILE text
*----------------------------------------------------------------------*
FORM download_xl USING p_file type any.
DATA:lv_path TYPE string VALUE 'c:\'.
CONCATENATE lv_path p_file '.xls' INTO p_file.
CONDENSE p_file NO-GAPS.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = p_file
filetype = 'DAT'
write_field_separator = '#'
TABLES
data_tab = it_xl.
ENDFORM. "download_xl
06-12-2009 6:44 AM
loop at internal table where col3 = 'b1'.
append the data in one internal table and dump in excel.
endloop.
same for b2 and b3
loop at internal table where col3 = 'b2'.
append the data in one internal table and dump in excel.
endloop.
loop at internal table where col3 = 'b3'.
append the data in one internal table and dump in excel.
endloop.
or
loop at internal table.
case col3.
when 'b1'.
append to itab1.
when 'b2'.
apend to itab2
when 'b3'.
append to itab3.
endloop.
dump all three itabs in different excels.
06-12-2009 7:29 AM
Hi ,
I am sorry guys for not being clear with my question.
Actually i cannot determine how many b1, b2,b3 ........bn will be there in the internal table.
so i cannot create n no of internal tables for each b1 b2 bn respectively..
How do i go thru for the scenario.
Regards,
navin
06-12-2009 6:50 AM
HI,
try something like this.
constants c_filepath type rlgrap-filename.
loop at itab into wa_tab1.
loop at itab into wa_tab2 "Read all the records from itab with similar values in col3
where col3 = wa_tab1-col3.
append wa_tab2 to itab_new.
endloop.
delete itab where col3 = wa_tab1-col3
concatenate p_fname
'\'
wa_tab1-col3
'.xls'
to p_fname.
call function 'GUI_DOWNLOAD'
exporting
filename = p_fname
filetype = 'ASC'
tables
tab = tab_new
refresh : tab_new[],
p_fname.
endloop.
Hope this helps
06-12-2009 7:03 AM
06-12-2009 7:11 AM
06-12-2009 7:11 AM
You can create all multiple worksheets in single excel file using OLE automation.
and you will have to create only one internal table - new-tab.
06-12-2009 7:28 AM
The real scenario is as ..
I wanted to split the records from the internal table based on b1 b2 b3.... and put in in different excel files and then attach each excel file as attachment in a mail and send it .
Hence i cannot put it in worksheets.
Regards
Navin
06-12-2009 7:11 AM
Hi ,
I am sorry guys for not being clear with my question.
Actually i cannot determine how many b1, b2,b3 ........bn will be there in the internal table.
so i cannot create n no of internal tables for each b1 b2 bn respectively..
How do i go thru for the scenario.
Regards,
navin
06-12-2009 7:20 AM
OLE2_OBJECT class can help you. With this class u can access all sort of excel things like workbooks, cells, font.
06-12-2009 7:40 AM
Hello Navin,
you try the below code.
REFRESH itab1.
SORT itab BY col3.
LOOP AT itab.
APPEND itab TO itab1.
AT END OF col3.
CONCATENATE p_fname itab-col3 '.xls' INTO p_fname.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = p_fname
filetype = 'ASC'
TABLES
tab = itab1.
CLEAR : itab1[],itab, p_fname.
ENDAT.
ENDLOOP.
Hope this helps you.
Regards,
Sachinkumar Mehta.
06-12-2009 8:08 AM
put col3 as the first field in your itab.
TYPES:BEGIN OF ty3,
col3(3) TYPE c,
col2(3) TYPE c,
col1(3) TYPE c,
END OF ty3.
DATA:itab3 TYPE TABLE OF ty3.
DATA:it_xl TYPE TABLE OF ty3.
DATA:wa3 TYPE ty3.
DATA:file_name TYPE string.
SORT itab3 BY col3.
LOOP AT itab3 INTO wa3.
APPEND wa3 TO it_xl.
AT END OF col3.
READ TABLE itab3 INTO wa3 INDEX sy-tabix.
CHECK sy-subrc = 0.
file_name = wa3-col3.
PERFORM download_xl USING file_name.
REFRESH it_xl.
ENDAT.
ENDLOOP.
*&---------------------------------------------------------------------*
*& Form download_xl
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_FILE text
*----------------------------------------------------------------------*
FORM download_xl USING p_file type any.
DATA:lv_path TYPE string VALUE 'c:\'.
CONCATENATE lv_path p_file '.xls' INTO p_file.
CONDENSE p_file NO-GAPS.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = p_file
filetype = 'DAT'
write_field_separator = '#'
TABLES
data_tab = it_xl.
ENDFORM. "download_xl
06-18-2009 11:02 AM