Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

in excel sheet tab name is not coming-urgent

Former Member
0 Kudos

hi all,

one small rewquirement. if u run this test program it opens a excel sheet which contains signle tab. here tab name is not coming. i dont no hot to display tabname here.anybody can make the changes and send me the code.

i am sending my code below.

thanks,

maheedhar.t

REPORT ytestvij MESSAGE-ID zv.

************************************************************************

TABLES sscrfields.

TYPE-POOLS: icon.

TYPES : BEGIN OF zfnames_ds,

reptext TYPE reptext,

END OF zfnames_ds.

TYPE-POOLS ole2 .

DATA: wa_fntxt TYPE smp_dyntxt.

DATA : wa_t75_booking TYPE zvt75_booking_h,

t_t75_booking TYPE STANDARD TABLE OF zvt75_booking_h.

DATA : wa_fields TYPE dfies,

t_fields TYPE STANDARD TABLE OF dfies.

DATA : wa_fnames TYPE zfnames_ds,

t_fnames TYPE STANDARD TABLE OF zfnames_ds.

  • handles for OLE objects

DATA: h_excel TYPE ole2_object, " Excel object

h_mapl TYPE ole2_object, " list of workbooks

h_map TYPE ole2_object, " workbook

h_zl TYPE ole2_object, " cell

h_f TYPE ole2_object. " font

DATA h TYPE i.

DATA : lin TYPE i.

data: excel type ole2_object,

application type ole2_object,

books type ole2_object,

book type ole2_object,

sheet type ole2_object,

cell type ole2_object,

column type ole2_object.

************************************************************************

PARAMETERS : p_input TYPE localfile.

  • Add button to application toolbar

SELECTION-SCREEN FUNCTION KEY 1. "Will have a function code of 'FC01'

************************************************************************

INITIALIZATION.

  • Add displayed text string to buttons

wa_fntxt-icon_id = icon_xls.

wa_fntxt-icon_text = 'Input File template'.

wa_fntxt-quickinfo = 'T75 Header Data'.

sscrfields-functxt_01 = wa_fntxt.

************************************************************************

AT SELECTION-SCREEN.

IF sscrfields-ucomm = 'FC01'.

  • do nothing

PERFORM open_excel.

ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_input.

PERFORM get_filename USING p_input.

************************************************************************

START-OF-SELECTION.

*set pf-status 'ONE'.

************************************************************************

END-OF-SELECTION.

WRITE : lin.

************************************************************************

&----


*& Form GET_FILENAME

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM get_filename USING p_file TYPE rlgrap-filename . "localfile.

DATA : w_rc TYPE i.

DATA : wa_file_table TYPE file_table ,

t_file_table TYPE STANDARD TABLE OF file_table.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

  • EXPORTING

  • WINDOW_TITLE =

  • DEFAULT_EXTENSION =

  • DEFAULT_FILENAME =

  • FILE_FILTER =

  • INITIAL_DIRECTORY =

  • MULTISELECTION =

CHANGING

file_table = t_file_table[]

rc = w_rc

  • USER_ACTION =

EXCEPTIONS

file_open_dialog_failed = 1

cntl_error = 2

error_no_gui = 3

OTHERS = 4

.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

LOOP AT t_file_table INTO wa_file_table.

p_file = wa_file_table-filename.

ENDLOOP.

ENDFORM. " GET_FILENAME

&----


*& Form open_excel

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM open_excel.

  • SELECT * FROM ZVT75_BOOKING_H

  • INTO TABLE t_t75_booking

  • UP TO 10 ROWS.

  • start Excel

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.

PERFORM err_hdl.

SET PROPERTY OF h_excel 'Visible' = 1.

PERFORM err_hdl.

  • get list of workbooks, initially empty

CALL METHOD OF h_excel 'Workbooks' = h_mapl.

PERFORM err_hdl.

  • add a new workbook

CALL METHOD OF h_mapl 'Add' = h_map.

PERFORM err_hdl.

  • output column headings to active Excel sheet

PERFORM fill_cell USING 1 1 1 'Financial year'.

PERFORM fill_cell USING 1 2 1 'Financial quarter'.

PERFORM fill_cell USING 1 3 1 'Customer number'.

PERFORM fill_cell USING 1 4 1 'Booking Year'.

PERFORM fill_cell USING 1 5 1 'Financial quarter'.

PERFORM fill_cell USING 1 6 1 'Contract type'.

PERFORM fill_cell USING 1 7 1 'Sub Contract type'.

PERFORM fill_cell USING 1 8 1 'Customer purchase order number'.

PERFORM fill_cell USING 1 9 1 'Booking Amount'.

PERFORM fill_cell USING 1 10 1 'Currency Key'.

  • LOOP AT t_t75_booking into wa_t75_booking.

    • copy items to active EXCEL sheet

  • H = SY-TABIX + 1.

  • PERFORM FILL_CELL USING H 1 0 wa_t75_booking-BOOKYEAR.

  • PERFORM FILL_CELL USING H 2 0 wa_t75_booking-BOOKQTR.

  • PERFORM FILL_CELL USING H 3 0 wa_t75_booking-.

  • PERFORM FILL_CELL USING H 4 0 wa_t75_booking-BOOKYEAR.

  • PERFORM FILL_CELL USING H 5 0 wa_t75_booking-BOOKQTR.

  • ENDLOOP.

  • disconnect from Excel

FREE OBJECT h_excel.

PERFORM err_hdl.

ENDFORM. " open_excel

&----


*& Form ERR_HDL

&----


  • outputs OLE error if any *

----


  • --> p1 text

  • <-- p2 text

----


FORM err_hdl.

data test type sy-subrc.

test = sy-subrc.

IF test <> 0.

Message e000(ZV) with 'Error in OLE-Automation:'.

STOP.

ENDIF.

ENDFORM. " ERR_HDL

----


  • FORM FILL_CELL *

----


  • sets cell at coordinates i,j to value val boldtype bold *

----


FORM fill_cell USING i j bold val.

CALL METHOD OF h_excel 'Cells' = h_zl EXPORTING #1 = i #2 = j.

set property of sheet 'Name' = 'T75'.

PERFORM err_hdl.

SET PROPERTY OF h_zl 'Value' = val .

PERFORM err_hdl.

GET PROPERTY OF h_zl 'Font' = h_f.

PERFORM err_hdl.

SET PROPERTY OF h_f 'Bold' = bold .

PERFORM err_hdl.

ENDFORM.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi,

Look at the below thread, i posted complete code in this one, just copy that Program and past in your SAP and run the Program, it will create 3 sheets with the names also, then look at the Sheet name in the code, you will understand where to add the code

Regards

Sudheer

4 REPLIES 4

Rashid_Javed
Contributor
0 Kudos

I think in your program between these two lines

CALL METHOD OF h_mapl 'Add' = h_map.

PERFORM err_hdl.

you should add the following line

set property of h_map 'Name' = 'T75'.

Hopefully it will work

RJv

Former Member
0 Kudos

Hi,

You need to add the below code

get property of h_excel 'ACTIVESHEET' = worksheet.
set property of worksheet 'Name' = gv_sheet_name .

Regards

Sudheer

0 Kudos

where i have add these two lines of code in my program.

thanks,

maheedhar.t

Former Member
0 Kudos

Hi,

Look at the below thread, i posted complete code in this one, just copy that Program and past in your SAP and run the Program, it will create 3 sheets with the names also, then look at the Sheet name in the code, you will understand where to add the code

Regards

Sudheer