EXCEL APPLICATION - WORK SHEET UPDATION

[Former Member]

Hi All,

1. How to open an excel application which is on the application server ?

If not local file.??

2. How to look for a particular worksheet ( Worksheet name is fixed here )?

There are 20 worksheet in my excel file.

3. How to update the particular worksheet ( Columns are fixed ) ?

While updating, today i will update the worksheet1 till 20 rows, tomorrow i want to start from 21st row.

Thanks in advance .

Expecting replies.

Regards

Vijay

[Former Member]

Hi Vijay,

Refer the links. Not sure if it answers all your questions.

http://sap.ittoolbox.com/code/d.asp?d=3127&a=s

or to

http://sap.ittoolbox.com/code/d.asp?d=3126&a=s

or to

http://sap.ittoolbox.com/code/d.asp?d=3027&a=s

you can also check

http://sap.ittoolbox.com/code/d.asp?d=1614&a=s

These links were extracted from ITToolbox Sap source code exchange( http://sap.ittoolbox.com/code/d.asp?whichpage=1&pagesize=10&i=10&a=c&o=&t=&q=&qt= )

This pages are a free resource of code from other abappers who kindly share their knowledge to us.

>

• <b>Multiple excel sheets generation in a workbook</b>

CREATE OBJECT EXCEL 'EXCEL.SHEET'.

GET PROPERTY OF EXCEL 'Application' = APPLICATION.

SET PROPERTY OF APPLICATION 'Visible' = 1.

CALL METHOD OF APPLICATION 'Workbooks' = BOOKS.

CALL METHOD OF BOOKS 'Add' = BOOK.

CALL METHOD OF BOOK 'WORKSHEETS' = SHEET.

CALL METHOD OF SHEET 'ADD'.

• Fill all the sheets with relavant data

PERFORM SHEET1 TABLES ITAB1.

PERFORM SHEET2 TABLES ITAB2.

PERFORM SHEET3 TABLES ITAB3.

PERFORM SHEET4 TABLES ITAB4.

• Quit the excel after use

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT: COLUMN,SHEET,BOOK,BOOKS,APPLICATION,EXCEL. "NO FLUSH.

CLEAR V_SHEET.

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF SHEET 'cells' = CELL NO FLUSH

EXPORTING #1 = ROW #2 = COL.

SET PROPERTY OF CELL 'value' = VAL.

FREE OBJECT CELL NO FLUSH.

ENDFORM. " FILL_CELL

FORM SHEET1 TABLES ITAB1 STRUCTURE ITAB1.

V_SHEET = Sheet Name.

V_NO = V_NO + 1.

CALL METHOD OF BOOK 'worksheets' = SHEET NO FLUSH EXPORTING #1 = V_NO.

SET PROPERTY OF SHEET 'Name' = V_SHEET NO FLUSH.

PERFORM FILL_SHEET1 TABLES ITAB1 USING V_NO V_SHEET.

CALL METHOD OF SHEET 'Columns' = COLUMN.

FREE OBJECT SHEET.

CALL METHOD OF COLUMN 'Autofit'.

FREE OBJECT COLUMN.

ENDFORM.

Repeat above procedure for all sheets you want to add

FORM FILL_SHEET1

TABLES ITAB1 STRUCTURE ITAB1

USING V_NO V_SHEET.

ROW = 1.

PERFORM FILL_CELL USING ROW 1 'Column1 Name'.

PERFORM FILL_CELL USING ROW 2 'Column2 Name'.

PERFORM FILL_CELL USING ROW 3 'Column3 Name'.

ROW = ROW + 1.

LOOP AT ITAB1.

PERFORM FILL_CELL USING ROW 1 ITAB1-Column1.

PERFORM FILL_CELL USING ROW 2 ITAB1-Column2.

PERFORM FILL_CELL USING ROW 3 ITAB1-Column3.

ROW = ROW + 1.

ENDLOOP.

ENDFORM.

Repeat above procedure for all sheets you want to add

<b>UPLOAD EXCEL into Internal Table</b>

Use FM ALSM_EXCEL_TO_INTERNAL_TABLE

TYPES:

BEGIN OF ty_upload,

field1 TYPE c length 12,

field2 TYPE c length 12,

field3 TYPE c length 12,

END OF ty_upload.

DATA it_upload TYPE STANDARD TABLE OF ty_upload WITH DEFAULT KEY.

DATA wa_upload TYPE ty_upload.

DATA itab TYPE STANDARD TABLE OF alsmex_tabline WITH DEFAULT KEY.

FIELD-SYMBOLS: <wa> type alsmex_tabline.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 3

i_end_row = 65535

TABLES

intern = itab.

LOOP AT itab ASSIGNING <wa>.

CASE <wa>-col.

WHEN '0001'.

wa_upload-field1 = <wa>-value.

WHEN '0002'.

wa_upload-field2 = <wa>-value.

WHEN '0003'.

wa_upload-field3 = <wa>-value.

ENDCASE.

APPEND wa_upload TO it_upload.

CLEAR wa_upload.

ENDLOOP.

**********another way*******

TYPE-POOLS truxs.

tables : ztable.

types: begin of t_tab,

col1(5) type c,

col2(5) type c,

col3(5) type c,

end of t_tab.

data : itab type standard table of t_tab,

wa type t_tab.

data it_type type truxs_t_text_data.

parameter p_file type rlgrap-filename.

data ttab type tabname.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

• PROGRAM_NAME = SYST-CPROG

• DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = p_file

.

start-of-selection.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER = 'X'

i_tab_raw_data = it_type

i_filename = p_file

tables

i_tab_converted_data = itab[]

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2

.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

end-of-selection.

loop at itab into wa.

ztable-col1 = wa-col1.

ztable-col2 = wa-col2.

ztable-col3 = wa-col3.

modify ztable.

endloop.

Reward points if this Helps.

Manish