Hi experts,
I am very new to abap and need your help.I have an excel sheet with 7 worksheets. Out of these 7 worksheets, 6 of them have pivot tables and pie charts which depend on the detailed data of worksheet 2. I need to get the updated data from SAP database into this worksheet 2. I've written a piece of code, but it replaces the entire excel sheet. Kindly help me out with this if possible.The sample code is given below.
DATA: emp_name TYPE char80.
DATA: it_itabex TYPE zdb_ex_tty,
it_emp TYPE TABLE OF zis_emp,
it_org TYPE TABLE OF zis_org,
it_pos TYPE TABLE OF zis_pos,
it_pos_alloc TYPE TABLE OF zis_pos_alloc,
it_res TYPE TABLE OF zis_res,
it_prj TYPE TABLE OF zis_prj,
it_res_alloc TYPE TABLE OF zis_res_alloc,
ls_itabex TYPE zdb_ex_s.
DATA: lv_filename TYPE string,
lv_path TYPE string,
lv_fullpath TYPE string,
lv_replace TYPE i.
FIELD-SYMBOLS: <fs_emp> LIKE LINE OF it_emp,
<fs_org> LIKE LINE OF it_org,
<fs_pos> LIKE LINE OF it_pos,
<fs_pos_alloc> LIKE LINE OF it_pos_alloc,
<fs_res> LIKE LINE OF it_res,
<fs_prj> LIKE LINE OF it_prj,
<fs_res_alloc> LIKE LINE OF it_res_alloc.
****************************************************************
** Populate all the tables that have to be exported.
****************************************************************
SELECT * FROM zis_org INTO TABLE it_org.
SELECT * FROM zis_pos INTO TABLE it_pos.
SELECT * FROM zis_pos_alloc INTO TABLE it_pos_alloc.
SELECT * FROM zis_emp INTO TABLE it_emp.
SELECT * FROM zis_res_alloc INTO TABLE it_res_alloc.
SELECT * FROM zis_res INTO TABLE it_res.
SELECT * FROM zis_prj INTO TABLE it_prj.
****************************************************************
** Append the Column Header
****************************************************************
CLEAR ls_itabex.
ls_itabex-ipp_pos_id = 'IPP Pos ID'.
ls_itabex-emp_name = 'Name'.
ls_itabex-dt_of_join = 'JoinedOn'.
ls_itabex-emp_status = 'Status'.
ls_itabex-org_name = 'Org'.
ls_itabex-prj_name = 'Project'.
ls_itabex-mgr_name = 'Line'.
ls_itabex-designation = 'Designation'.
ls_itabex-specialization = 'Specialization'.
APPEND ls_itabex TO it_itabex.
****************************************************************
** Append all the tables into one internal table
****************************************************************
LOOP AT it_pos_alloc ASSIGNING <fs_pos_alloc>.
CLEAR ls_itabex.
ls_itabex-ipp_pos_id = <fs_pos_alloc>-ipp_pos_id.
READ TABLE it_emp ASSIGNING <fs_emp> WITH KEY emp_guid = <fs_pos_alloc>-emp_guid.
IF sy-subrc = 0.
CONCATENATE <fs_emp>-emp_fname <fs_emp>-emp_lname INTO ls_itabex-emp_name SEPARATED BY space.
ls_itabex-dt_of_join = <fs_emp>-dt_of_join.
ls_itabex-emp_status = <fs_emp>-emp_status.
ls_itabex-specialization = <fs_emp>-specialization.
ENDIF.
READ TABLE it_pos ASSIGNING <fs_pos> WITH KEY ipp_pos_id = <fs_pos_alloc>-ipp_pos_id.
IF sy-subrc = 0.
ls_itabex-designation = <fs_pos>-designation.
READ TABLE it_org ASSIGNING <fs_org> WITH KEY org_id = <fs_pos>-org_id.
IF sy-subrc = 0.
ls_itabex-org_name = <fs_org>-org_name.
ls_itabex-mgr_name = <fs_org>-mgr_name.
ENDIF.
ENDIF.
READ TABLE it_res ASSIGNING <fs_res> WITH KEY org_id = <fs_org>-org_id.
IF sy-subrc = 0.
READ TABLE it_prj ASSIGNING <fs_prj> WITH KEY prj_guid = <fs_res>-prj_guid.
IF sy-subrc = 0.
ls_itabex-prj_name = <fs_prj>-prj_name.
ENDIF.
ls_itabex-org_name = <fs_org>-org_name.
ENDIF.
APPEND ls_itabex TO it_itabex.
ENDLOOP.
****************************************************************
** Display Save Dialog window
****************************************************************
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = 'xls'
default_file_name = 'Staffing Excel.xls'
initial_directory = 'C:\Documents and Settings\All Users\Desktop'
CHANGING
filename = lv_filename
path = lv_path
fullpath = lv_fullpath
user_action = lv_replace.
****************************************************************
** Check whether user has cancelled request to overwrite or not
****************************************************************
CHECK lv_replace EQ '0'.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = lv_fullpath
filetype = 'ASC'
write_field_separator = 'X'
TABLES
data_tab = it_itabex[]
EXCEPTIONS
file_open_error = 1
file_write_error = 2
OTHERS = 3.
IF sy-subrc = 0.
MESSAGE s002(zmsg).
* Operation successful.
ELSE.
MESSAGE e014(zmsg).
* Operation failed.
ENDIF.
Thanks a lot in advance,
Preethi
Abap to excel