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: 

dowunload data in different sheet of the same excel file

Former Member
0 Kudos
149

hi all,

i have three internal table and i want to download the data in three different work sheet of same excel file, i am able to down load only one colum of internal table but i want that the work sheet should contain all the colums with the record,

i mean i am havin internal table say it_mara now in this table i have various colums

i want that the hole of the colums should be down loaded in the work sheet.

and i also want to give the heading to ecah of the cell, and after heading data should appear, friends i am very new to OLE concepts please give some importent information regarding my problem,

full points will be allocated to all the useful help,

thanks in advance,

1 ACCEPTED SOLUTION

former_member189059
Active Contributor
0 Kudos
84

Hello,

check this code


report zole123.

INCLUDE ole2incl.
DATA:  count TYPE i,
       application TYPE ole2_object,
       workbook TYPE ole2_object,
       excel     TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, place(50), END OF itab3.


************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.

  APPEND: 'name1' TO itab1, 'surname1' TO itab2,
                              'worli' TO itab3,
            'nam2' TO itab1, 'surname2' TO itab2,
                              'chowpatty' TO itab3,
           'name3' TO itab1, 'surname3' TO itab2,
                              'versova' TO itab3,
            'name4' TO itab1, 'surname4' TO itab2,
                              'grant road' TO itab3,
            'name5' TO itab1, 'surname5' TO itab2,
                              'gaon' TO itab3,

            'name6' TO itab1, 'surname6' TO itab2,
                              'mahim' TO itab3.


*  CREATE OBJECT application 'excel.application'.
*  SET PROPERTY OF application 'visible' = 1.
*  CALL METHOD OF application 'Workbooks' = workbook.
*  CALL METHOD OF workbook 'Add'.

  CREATE OBJECT excel 'EXCEL.APPLICATION'.

  IF sy-subrc NE 0.
    WRITE: / 'No EXCEL creation possible'.
    STOP.
  ENDIF.

  SET PROPERTY OF excel 'DisplayAlerts' = 0.

  CALL METHOD OF excel 'WORKBOOKS' = workbook .

  SET PROPERTY OF excel 'VISIBLE' = 1.


*  Create worksheet
  SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.
  CALL METHOD OF workbook 'ADD'.


  DO 3 TIMES.
    IF sy-index GT 1.
      CALL METHOD OF excel 'WORKSHEETS' = sheet.
      CALL METHOD OF sheet 'ADD'.
      FREE OBJECT sheet.
    ENDIF.
  ENDDO.

  count = 1.
  DO 3 TIMES.

    CALL METHOD OF excel 'WORKSHEETS' = sheet
      EXPORTING
        #1 = count.

*    perform get_sheet_name using scnt sname.
    CASE count.
      WHEN '1'.
        SET PROPERTY OF sheet 'NAME' = 'firstName'.
        CALL METHOD OF sheet 'ACTIVATE'.
        " add header here

        LOOP AT itab1.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab1-first_name.
          SET PROPERTY OF cells 'Value' = itab1-first_name.

        ENDLOOP.

      WHEN '2'.
        SET PROPERTY OF sheet 'NAME' = 'LastName'.
        CALL METHOD OF sheet 'ACTIVATE'.
 " add header here

        LOOP AT itab2.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab2-last_name.
          SET PROPERTY OF cells 'Value' = itab2-last_name.

        ENDLOOP.

      WHEN '3'.
        SET PROPERTY OF sheet 'NAME' = 'place'.
        CALL METHOD OF sheet 'ACTIVATE'.

 " add header here

        LOOP AT itab3.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab3-place.
          SET PROPERTY OF cells 'Value' = itab3-place.

        ENDLOOP.


    ENDCASE.
    count = count + 1.

  ENDDO.


* Save excel speadsheet to particular filename

  GET PROPERTY OF excel 'ActiveSheet' = sheet.
  CALL METHOD OF sheet 'SaveAs'
                   EXPORTING #1 = 'c:tempexceldoc1.xls'     "filename
                             #2 = 1.                          "fileFormat

Note: to make headings, change the -1 to +1 where specified in the above code and add the following where i have mentioned to add it

index = row_max * ( sy-tabix - 1 ) + 1.
 CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
 SET PROPERTY OF cells 'value' = header1.

Message was edited by:

Kris Donald

14 REPLIES 14

kiran_k8
Active Contributor
0 Kudos
84

Sudeep,

MS_EXCEL_OLE_STANDARD_OLE-To download the data in different sheets of the same excel file.

K.Kiran.

former_member189059
Active Contributor
0 Kudos
85

Hello,

check this code


report zole123.

INCLUDE ole2incl.
DATA:  count TYPE i,
       application TYPE ole2_object,
       workbook TYPE ole2_object,
       excel     TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, place(50), END OF itab3.


************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.

  APPEND: 'name1' TO itab1, 'surname1' TO itab2,
                              'worli' TO itab3,
            'nam2' TO itab1, 'surname2' TO itab2,
                              'chowpatty' TO itab3,
           'name3' TO itab1, 'surname3' TO itab2,
                              'versova' TO itab3,
            'name4' TO itab1, 'surname4' TO itab2,
                              'grant road' TO itab3,
            'name5' TO itab1, 'surname5' TO itab2,
                              'gaon' TO itab3,

            'name6' TO itab1, 'surname6' TO itab2,
                              'mahim' TO itab3.


*  CREATE OBJECT application 'excel.application'.
*  SET PROPERTY OF application 'visible' = 1.
*  CALL METHOD OF application 'Workbooks' = workbook.
*  CALL METHOD OF workbook 'Add'.

  CREATE OBJECT excel 'EXCEL.APPLICATION'.

  IF sy-subrc NE 0.
    WRITE: / 'No EXCEL creation possible'.
    STOP.
  ENDIF.

  SET PROPERTY OF excel 'DisplayAlerts' = 0.

  CALL METHOD OF excel 'WORKBOOKS' = workbook .

  SET PROPERTY OF excel 'VISIBLE' = 1.


*  Create worksheet
  SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.
  CALL METHOD OF workbook 'ADD'.


  DO 3 TIMES.
    IF sy-index GT 1.
      CALL METHOD OF excel 'WORKSHEETS' = sheet.
      CALL METHOD OF sheet 'ADD'.
      FREE OBJECT sheet.
    ENDIF.
  ENDDO.

  count = 1.
  DO 3 TIMES.

    CALL METHOD OF excel 'WORKSHEETS' = sheet
      EXPORTING
        #1 = count.

*    perform get_sheet_name using scnt sname.
    CASE count.
      WHEN '1'.
        SET PROPERTY OF sheet 'NAME' = 'firstName'.
        CALL METHOD OF sheet 'ACTIVATE'.
        " add header here

        LOOP AT itab1.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab1-first_name.
          SET PROPERTY OF cells 'Value' = itab1-first_name.

        ENDLOOP.

      WHEN '2'.
        SET PROPERTY OF sheet 'NAME' = 'LastName'.
        CALL METHOD OF sheet 'ACTIVATE'.
 " add header here

        LOOP AT itab2.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab2-last_name.
          SET PROPERTY OF cells 'Value' = itab2-last_name.

        ENDLOOP.

      WHEN '3'.
        SET PROPERTY OF sheet 'NAME' = 'place'.
        CALL METHOD OF sheet 'ACTIVATE'.

 " add header here

        LOOP AT itab3.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab3-place.
          SET PROPERTY OF cells 'Value' = itab3-place.

        ENDLOOP.


    ENDCASE.
    count = count + 1.

  ENDDO.


* Save excel speadsheet to particular filename

  GET PROPERTY OF excel 'ActiveSheet' = sheet.
  CALL METHOD OF sheet 'SaveAs'
                   EXPORTING #1 = 'c:tempexceldoc1.xls'     "filename
                             #2 = 1.                          "fileFormat

Note: to make headings, change the -1 to +1 where specified in the above code and add the following where i have mentioned to add it

index = row_max * ( sy-tabix - 1 ) + 1.
 CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
 SET PROPERTY OF cells 'value' = header1.

Message was edited by:

Kris Donald

0 Kudos
84

hi krish,

thanks a lot now i got the header, but my whole data is coming in one colum,

i mean there are various colum in my internal table and they should come in different colum of the excel sheet also, but they are all coming in one colum of the excel, plz give your golden solution to resolve this problem,

thanks in advance.

0 Kudos
84

hello,

I modified the same code to get 2 columns

search for the ** changed to * end of change comments in the code

that is where i have made the modifications

the logic is as follows..

We need to choose a cell and then write to it

In this case we have stored its position in the variable 'index'

We have a constant row_max which stores the maximum number of rows in the sheet (which is 256 for all the versions of excel i have used till no - though i think it has increased in office 2007)

the index value of the first row cell in the first column is 1

the index value of the first row cell in the second column is 2 and so on

the index value of the second row cell in the first column is 257 etc

the statement " index = row_max * (row - 1) + (col). " is used to calculate the position based on row and column number

therefore row = 1 and col = 1 will give

256 * 0 + 1

= 0 + 1

= 1

incase of internal tables, sy-tabix will give us the row number

report zole123.
 
INCLUDE ole2incl.
DATA:  count TYPE i,
       application TYPE ole2_object,
       workbook TYPE ole2_object,
       excel     TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.
 

** changed
DATA: BEGIN OF itab1 OCCURS 0, 
first_name(10),
field_2(10),
END OF itab1.
** end of change

DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, place(50), END OF itab3.
 
 
************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.
 

** changed
  APPEND: 'surname1' TO itab2,
                              'worli' TO itab3,
            'surname2' TO itab2,
                              'chowpatty' TO itab3,
            'surname3' TO itab2,
                              'versova' TO itab3,
           'surname4' TO itab2,
                              'grant road' TO itab3,
           'surname5' TO itab2,
                              'gaon' TO itab3,
            'surname6' TO itab2,
                              'mahim' TO itab3.
 
itab1-first_name = 'name1'.
itab1-field_2 = 'other1'.
append itab1.

itab1-first_name = 'name2'.
itab1-field_2 = 'other2'.
append itab1.

itab1-first_name = 'name3'.
itab1-field_2 = 'other3'.
append itab1.

itab1-first_name = 'name4'.
itab1-field_2 = 'other4'.
append itab1.

itab1-first_name = 'name5'.
itab1-field_2 = 'other5'.
append itab1.

itab1-first_name = 'name6'.
itab1-field_2 = 'other6'.
append itab1.

* end of change
 
*  CREATE OBJECT application 'excel.application'.
*  SET PROPERTY OF application 'visible' = 1.
*  CALL METHOD OF application 'Workbooks' = workbook.
*  CALL METHOD OF workbook 'Add'.
 
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
 
  IF sy-subrc NE 0.
    WRITE: / 'No EXCEL creation possible'.
    STOP.
  ENDIF.
 
  SET PROPERTY OF excel 'DisplayAlerts' = 0.
 
  CALL METHOD OF excel 'WORKBOOKS' = workbook .
 
  SET PROPERTY OF excel 'VISIBLE' = 1.
 
 
*  Create worksheet
  SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.
  CALL METHOD OF workbook 'ADD'.
 
 
  DO 3 TIMES.
    IF sy-index GT 1.
      CALL METHOD OF excel 'WORKSHEETS' = sheet.
      CALL METHOD OF sheet 'ADD'.
      FREE OBJECT sheet.
    ENDIF.
  ENDDO.
 
  count = 1.
  DO 3 TIMES.
 
    CALL METHOD OF excel 'WORKSHEETS' = sheet
      EXPORTING
        #1 = count.
 
*    perform get_sheet_name using scnt sname.
    CASE count.
      WHEN '1'.
        SET PROPERTY OF sheet 'NAME' = 'firstName'.
        CALL METHOD OF sheet 'ACTIVATE'.
        " add header here
 
        LOOP AT itab1.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 = column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Value' = itab1-first_name.
 
** changed
         index = index + 1.
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Value' = itab1-field_2.
* end of change

        ENDLOOP.
 
      WHEN '2'.
        SET PROPERTY OF sheet 'NAME' = 'LastName'.
        CALL METHOD OF sheet 'ACTIVATE'.
 " add header here
 
        LOOP AT itab2.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab2-last_name.
          SET PROPERTY OF cells 'Value' = itab2-last_name.
 
        ENDLOOP.
 
      WHEN '3'.
        SET PROPERTY OF sheet 'NAME' = 'place'.
        CALL METHOD OF sheet 'ACTIVATE'.
 
 " add header here
 
        LOOP AT itab3.
          index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines
          CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
          SET PROPERTY OF cells 'Formula' = itab3-place.
          SET PROPERTY OF cells 'Value' = itab3-place.
 
        ENDLOOP.
 
 
    ENDCASE.
    count = count + 1.
 
  ENDDO.
 
 
* Save excel speadsheet to particular filename
 
  GET PROPERTY OF excel 'ActiveSheet' = sheet.
  CALL METHOD OF sheet 'SaveAs'
                   EXPORTING #1 = 'c:tempexceldoc1.xls'     "filename
                             #2 = 1.                          "fileFormat

0 Kudos
84

hi krish,

now i can download the data in my excel sheet, but this process is very slow,

i can see each and every step performed by the system, like it first write the header and then bold it and then adjuest the row and fill the data in the cell...

it is very time consumining, please do give me your golden ideas to speed up the processes.

0 Kudos
84

well.. if you have all your data in an internal table, then you can use this method

it sends the data to the clipboard from SAP and then pastes it in the worksheet


*&---------------------------------------------------------------------*
*& Report  ZKRIS_OLE_DL_CLIPBOARD
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
 
REPORT  zkris_ole_dl_clipboard.
 
INCLUDE ole2incl.
.
DATA: w_cell1 TYPE ole2_object,
w_cell2 TYPE ole2_object.
 
*--- Ole data Declarations
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
gs_interior TYPE ole2_object, " Pattern
worksheet TYPE ole2_object,
h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object,
range TYPE ole2_object,
h_sheet2 TYPE ole2_object,
h_sheet3 TYPE ole2_object,
gs_font TYPE ole2_object,
flg_stop(1) TYPE c.
 
*********************************************************************
** Internal table Declaration
*********************************************************************
 
DATA: BEGIN OF t_excel OCCURS 0,
 
vkorg(20) TYPE c, "Sales Org
 
vbtyp(20) TYPE c, "Document Category
 
auart(20) TYPE c, "Document Type
 
ernam(20) TYPE c, "Created By
 
vbeln(20) TYPE c, "Document Number
 
posnr(20) TYPE c, "Item Number
 
erdat(20) TYPE c, "Created Date
 
vdatu(20) TYPE c, "Header Requested Delivery Date
 
reqdat(20) TYPE c, "Request date
 
condat(20) TYPE c, "Confirm date
 
lifsk(20) TYPE c, "Header Block
 
txt30(30) TYPE c, "Order User Status Description
 
lifsp(20) TYPE c, "Line Block
 
dispo(20) TYPE c, "MRP Controller
 
dsnam(20) TYPE c, "MRP Controller Description
 
vmsta(20) TYPE c, "Material Sales Status
 
kunnr(20) TYPE c, "Sold To
 
cname(35) TYPE c, "Sold To Name
 
regio(20) TYPE c, "State
 
cufd(10) TYPE c, "CUD
 
bstnk(20) TYPE c, "PO#
 
bsark(20) TYPE c, "Ordering Method
 
matnr(20) TYPE c, "Material
 
maktx(35) TYPE c, "Material Description
 
t200(20) TYPE c,                                            "T200
 
vtext(20) TYPE c, "T200 Description
 
matkl(20) TYPE c, "Material Group
 
zzbomind(7) TYPE c, "BOM Indicator
 
ostat(20) TYPE c, "Order Status
 
cmgst(20) TYPE c, "CRD
 
inco1(20) TYPE c, "Incoterms
 
oqty(20) TYPE c, "Order Quantity
 
pqty(20) TYPE c, "Open Quantity
 
unit(20) TYPE c, "UOM
 
onet(20) TYPE c, "Order Value
 
pnet(20) TYPE c, "Open Value
 
curr(20) TYPE c, "Currency key
 
so_bezei LIKE tvkbt-bezei,"Sales Office
 
sg_bezei LIKE tvgrt-bezei,"Sales Group
 
bname(20) TYPE c, "Ordering Party
 
contact(20) TYPE c, "Contact Name
 
telf1(20) TYPE c, "Contact telf1
 
reqqty(20) TYPE c, "Item Request qty
 
reqval(20) TYPE c, "Item Request value
 
conqty(20) TYPE c, "Item Confirm qty
 
conval(20) TYPE c, "Item Confirm value
 
zzrev(02) TYPE c, "Revenue recognition acceptance
 
bezei(20) TYPE c, "Revenue recognition text
 
vgbel(20) TYPE c, "Reference Order for RETURNS
 
0008text(255) TYPE c, "Internal Order Comment Text
 
END OF t_excel.
 
DATA: t_excel_bckord LIKE t_excel OCCURS 0 WITH HEADER LINE,
t_excel_bcklog LIKE t_excel OCCURS 0 WITH HEADER LINE,
t_excel_blkord LIKE t_excel OCCURS 0 WITH HEADER LINE.
 
TYPES: data1(1500) TYPE c,
ty TYPE TABLE OF data1.
 
DATA: it TYPE ty WITH HEADER LINE,
it_2 TYPE ty WITH HEADER LINE,
it_3 TYPE ty WITH HEADER LINE,
 
rec TYPE sy-tfill,
deli(1) TYPE c,
l_amt(18) TYPE c.
DATA: BEGIN OF hex,
tab TYPE x,
END OF hex.
 
FIELD-SYMBOLS: <fs> .
 
CONSTANTS cns_09(2) TYPE n VALUE 09.
 
ASSIGN deli TO <fs> TYPE 'X'.
hex-tab = cns_09.
<fs> = hex-tab.
 
DATA gv_sheet_name(20) TYPE c .
 
*----------------------------------------------------------------------*
* M A C R O Declaration
*----------------------------------------------------------------------*
DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.
 
t_excel_bckord-vkorg = 'ABC'.
t_excel_bckord-vbtyp = 'DEF'.
t_excel_bckord-auart = 'GHI'.
t_excel_bckord-ernam = 'JKL'.
t_excel_bckord-vbeln = 'MNO'.
t_excel_bckord-0008text = 'XYZ'.
APPEND t_excel_bckord.
 
t_excel_bckord-vkorg = 'ABC1'.
t_excel_bckord-vbtyp = 'DEF1'.
t_excel_bckord-auart = 'GHI1'.
t_excel_bckord-ernam = 'JKL1'.
t_excel_bckord-vbeln = 'MNO1'.
t_excel_bckord-0008text = 'XYZ1'.
APPEND t_excel_bckord.
 
t_excel_bckord-vkorg = 'ABC2'.
t_excel_bckord-vbtyp = 'DEF2'.
t_excel_bckord-auart = 'GHI2'.
t_excel_bckord-ernam = 'JKL2'.
t_excel_bckord-vbeln = 'MNO2'.
t_excel_bckord-0008text = 'XYZ2'.
APPEND t_excel_bckord.
 
 
 
t_excel_bcklog-vkorg = 'ABC'.
t_excel_bcklog-vbtyp = 'DEF'.
t_excel_bcklog-auart = 'GHI'.
t_excel_bcklog-ernam = 'JKL'.
t_excel_bcklog-vbeln = 'MNO'.
t_excel_bcklog-0008text = 'XYZ'.
APPEND t_excel_bcklog.
 
t_excel_bcklog-vkorg = 'ABC1'.
t_excel_bcklog-vbtyp = 'DEF1'.
t_excel_bcklog-auart = 'GHI1'.
t_excel_bcklog-ernam = 'JKL1'.
t_excel_bcklog-vbeln = 'MNO1'.
t_excel_bcklog-0008text = 'XYZ1'.
APPEND t_excel_bcklog.
 
t_excel_bcklog-vkorg = 'ABC2'.
t_excel_bcklog-vbtyp = 'DEF2'.
t_excel_bcklog-auart = 'GHI2'.
t_excel_bcklog-ernam = 'JKL2'.
t_excel_bcklog-vbeln = 'MNO2'.
t_excel_bcklog-0008text = 'XYZ2'.
APPEND t_excel_bcklog.
 
t_excel_bcklog-vkorg = 'ABC3'.
t_excel_bcklog-vbtyp = 'DEF3'..
t_excel_bcklog-auart = 'GHI3'.
t_excel_bcklog-ernam = 'JKL3'.
t_excel_bcklog-vbeln = 'MNO3'.
t_excel_bcklog-0008text = 'XYZ3'.
APPEND t_excel_bcklog.
 
 
t_excel_blkord-vkorg = 'ABC'.
t_excel_blkord-vbtyp = 'DEF'.
t_excel_blkord-auart = 'GHI'.
t_excel_blkord-ernam = 'JKL'.
t_excel_blkord-vbeln = 'MNO'.
t_excel_blkord-0008text = 'XYZ'.
APPEND t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC1'.
t_excel_blkord-vbtyp = 'DEF1'.
t_excel_blkord-auart = 'GHI1'.
t_excel_blkord-ernam = 'JKL1'.
t_excel_blkord-vbeln = 'MNO1'.
t_excel_blkord-0008text = 'XYZ1'.
APPEND t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC2'.
t_excel_blkord-vbtyp = 'DEF2'.
t_excel_blkord-auart = 'GHI2'.
t_excel_blkord-ernam = 'JKL2'.
t_excel_blkord-vbeln = 'MNO2'.
t_excel_blkord-0008text = 'XYZ2'.
APPEND t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC3'.
t_excel_blkord-vbtyp = 'DEF3'..
t_excel_blkord-auart = 'GHI3'.
t_excel_blkord-ernam = 'JKL3'.
t_excel_blkord-vbeln = 'MNO3'.
t_excel_blkord-0008text = 'XYZ3'.
APPEND t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC4'.
t_excel_blkord-vbtyp = 'DEF4'..
t_excel_blkord-auart = 'GHI4'.
t_excel_blkord-ernam = 'JKL4'.
t_excel_blkord-vbeln = 'MNO4'.
t_excel_blkord-0008text = 'XYZ4'.
APPEND t_excel_blkord.
 
 
LOOP AT t_excel_bckord.
  CONCATENATE
  t_excel_bckord-vkorg
 
  t_excel_bckord-vbtyp
 
  t_excel_bckord-auart
 
  t_excel_bckord-ernam
 
  t_excel_bckord-vbeln
 
  t_excel_bckord-posnr
 
  t_excel_bckord-erdat
 
  t_excel_bckord-vdatu
 
  t_excel_bckord-reqdat
 
  t_excel_bckord-condat
 
  t_excel_bckord-lifsk
 
  t_excel_bckord-txt30
 
  t_excel_bckord-lifsp
 
  t_excel_bckord-dispo
 
  t_excel_bckord-dsnam
 
  t_excel_bckord-vmsta
 
  t_excel_bckord-kunnr
 
  t_excel_bckord-cname
 
  t_excel_bckord-regio
 
  t_excel_bckord-cufd
 
  t_excel_bckord-bstnk
 
  t_excel_bckord-bsark
 
  t_excel_bckord-matnr
 
  t_excel_bckord-maktx
 
  t_excel_bckord-t200
 
  t_excel_bckord-vtext
 
  t_excel_bckord-matkl
 
  t_excel_bckord-zzbomind
 
  t_excel_bckord-ostat
 
  t_excel_bckord-cmgst
 
  t_excel_bckord-inco1
 
  t_excel_bckord-oqty
 
  t_excel_bckord-pqty
 
  t_excel_bckord-unit
 
  t_excel_bckord-onet
 
  t_excel_bckord-pnet
 
  t_excel_bckord-curr
 
  t_excel_bckord-so_bezei
 
  t_excel_bckord-sg_bezei
 
  t_excel_bckord-bname
 
  t_excel_bckord-contact
 
  t_excel_bckord-telf1
 
  t_excel_bckord-reqqty
 
  t_excel_bckord-reqval
 
  t_excel_bckord-conqty
 
  t_excel_bckord-conval
 
  t_excel_bckord-zzrev
 
  t_excel_bckord-bezei
 
  t_excel_bckord-vgbel
 
  t_excel_bckord-0008text
 
  INTO it
  SEPARATED BY deli.
 
  APPEND it.
  CLEAR it.
ENDLOOP.
 
 
LOOP AT t_excel_bcklog.
  CONCATENATE
  t_excel_bcklog-vkorg
 
  t_excel_bcklog-vbtyp
 
  t_excel_bcklog-auart
 
  t_excel_bcklog-ernam
 
  t_excel_bcklog-vbeln
 
  t_excel_bcklog-posnr
 
  t_excel_bcklog-erdat
 
  t_excel_bcklog-vdatu
 
  t_excel_bcklog-reqdat
 
  t_excel_bcklog-condat
 
  t_excel_bcklog-lifsk
 
  t_excel_bcklog-txt30
 
  t_excel_bcklog-lifsp
 
  t_excel_bcklog-dispo
 
  t_excel_bcklog-dsnam
 
  t_excel_bcklog-vmsta
 
  t_excel_bcklog-kunnr
 
  t_excel_bcklog-cname
 
  t_excel_bcklog-regio
 
  t_excel_bcklog-cufd
 
  t_excel_bcklog-bstnk
 
  t_excel_bcklog-bsark
 
  t_excel_bcklog-matnr
 
  t_excel_bcklog-maktx
 
  t_excel_bcklog-t200
 
  t_excel_bcklog-vtext
 
  t_excel_bcklog-matkl
 
  t_excel_bcklog-zzbomind
 
  t_excel_bcklog-ostat
 
  t_excel_bcklog-cmgst
 
  t_excel_bcklog-inco1
 
  t_excel_bcklog-oqty
 
  t_excel_bcklog-pqty
 
  t_excel_bcklog-unit
 
  t_excel_bcklog-onet
 
  t_excel_bcklog-pnet
 
  t_excel_bcklog-curr
 
  t_excel_bcklog-so_bezei
 
  t_excel_bcklog-sg_bezei
 
  t_excel_bcklog-bname
 
  t_excel_bcklog-contact
 
  t_excel_bcklog-telf1
 
  t_excel_bcklog-reqqty
 
  t_excel_bcklog-reqval
 
  t_excel_bcklog-conqty
 
  t_excel_bcklog-conval
 
  t_excel_bcklog-zzrev
 
  t_excel_bcklog-bezei
 
  t_excel_bcklog-vgbel
 
  t_excel_bcklog-0008text
 
  INTO it_2
  SEPARATED BY deli.
 
  APPEND it_2.
  CLEAR it_2.
ENDLOOP.
 
 
LOOP AT t_excel_blkord.
  CONCATENATE
  t_excel_blkord-vkorg
 
  t_excel_blkord-vbtyp
 
  t_excel_blkord-auart
 
  t_excel_blkord-ernam
 
  t_excel_blkord-vbeln
 
  t_excel_blkord-posnr
 
  t_excel_blkord-erdat
 
  t_excel_blkord-vdatu
 
  t_excel_blkord-reqdat
 
  t_excel_blkord-condat
 
  t_excel_blkord-lifsk
 
  t_excel_blkord-txt30
 
  t_excel_blkord-lifsp
 
  t_excel_blkord-dispo
 
  t_excel_blkord-dsnam
 
  t_excel_blkord-vmsta
 
  t_excel_blkord-kunnr
 
  t_excel_blkord-cname
 
  t_excel_blkord-regio
 
  t_excel_blkord-cufd
 
  t_excel_blkord-bstnk
 
  t_excel_blkord-bsark
 
  t_excel_blkord-matnr
 
  t_excel_blkord-maktx
 
  t_excel_blkord-t200
 
  t_excel_blkord-vtext
 
  t_excel_blkord-matkl
 
  t_excel_blkord-zzbomind
 
  t_excel_blkord-ostat
 
  t_excel_blkord-cmgst
 
  t_excel_blkord-inco1
 
  t_excel_blkord-oqty
 
  t_excel_blkord-pqty
 
  t_excel_blkord-unit
 
  t_excel_blkord-onet
 
  t_excel_blkord-pnet
 
  t_excel_blkord-curr
 
  t_excel_blkord-so_bezei
 
  t_excel_blkord-sg_bezei
 
  t_excel_blkord-bname
 
  t_excel_blkord-contact
 
  t_excel_blkord-telf1
 
  t_excel_blkord-reqqty
 
  t_excel_blkord-reqval
 
  t_excel_blkord-conqty
 
  t_excel_blkord-conval
 
  t_excel_blkord-zzrev
 
  t_excel_blkord-bezei
 
  t_excel_blkord-vgbel
 
  t_excel_blkord-0008text
 
  INTO it_3
  SEPARATED BY deli.
 
  APPEND it_3.
  CLEAR it_3.
ENDLOOP.
 
 
 
IF h_excel-header = space OR h_excel-handle = -1.
* start Excel
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.
 
* PERFORM err_hdl.
 
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
* PERFORM err_hdl.
SET PROPERTY OF h_excel 'Visible' = 1.
 
* add a new workbook
CALL METHOD OF h_mapl 'Add' = h_map.
* PERFORM err_hdl.
*GV_SHEET_NAME = '1st SHEET'.
 
gv_sheet_name = 'Back Orders'.
 
 
 
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = gv_sheet_name .
 
 
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
    #1 = 1
    #2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
    #1 = 1
    #2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
  EXPORTING
    #1 = w_cell1
    #2 = w_cell2.
*CALL METHOD OF gs_cells 'Select' .
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
 
 
DATA l_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it[]
  CHANGING
    rc                   = l_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    OTHERS               = 4.
 
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
    #1 = 1
    #2 = 1.
 
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
    #1 = 1
    #2 = 1.
* PERFORM err_hdl.
 
CALL METHOD OF h_excel 'Range' = range
  EXPORTING
    #1 = w_cell1
    #2 = w_cell2.
 
CALL METHOD OF range 'Select'.
* PERFORM err_hdl.
CALL METHOD OF worksheet 'Paste'.
* PERFORM err_hdl.
 
* CALL METHOD OF h_excel 'QUIT'.
 
*GV_SHEET_NAME = '2ND SHEET'.
gv_sheet_name = 'Backlog'.
 
 
GET PROPERTY OF h_excel 'Sheets' = h_sheet2 .
 
CALL METHOD OF h_sheet2 'Add' = h_map.
SET PROPERTY OF h_map 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
 
 
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
    #1 = 1
    #2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
    #1 = 1
    #2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
  EXPORTING
    #1 = w_cell1
    #2 = w_cell2.
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
 
CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it_2[]
  CHANGING
    rc                   = l_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    OTHERS               = 4.
 
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
    #1 = 1
    #2 = 1.
 
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
    #1 = 1
    #2 = 1.
* PERFORM err_hdl.
 
CALL METHOD OF h_excel 'Range' = range
  EXPORTING
    #1 = w_cell1
    #2 = w_cell2.
 
CALL METHOD OF range 'Select'.
* PERFORM err_hdl.
CALL METHOD OF worksheet 'Paste'.
 
 
 
*GV_SHEET_NAME = '3rd SHEET'.
gv_sheet_name = 'Blocked Orders'.
 
GET PROPERTY OF h_excel 'Sheets' = h_sheet3 .
 
CALL METHOD OF h_sheet3 'Add' = h_map.
SET PROPERTY OF h_map 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
 
 
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
    #1 = 1
    #2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
    #1 = 1
    #2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
  EXPORTING
    #1 = w_cell1
    #2 = w_cell2.
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
 
CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it_3[]
  CHANGING
    rc                   = l_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    OTHERS               = 4.
 
CALL METHOD OF h_excel 'Cells' = w_cell1
  EXPORTING
    #1 = 1
    #2 = 1.
 
CALL METHOD OF h_excel 'Cells' = w_cell2
  EXPORTING
    #1 = 1
    #2 = 1.
* PERFORM err_hdl.
 
CALL METHOD OF h_excel 'Range' = range
  EXPORTING
    #1 = w_cell1
    #2 = w_cell2.
 
CALL METHOD OF range 'Select'.
* PERFORM err_hdl.
CALL METHOD OF worksheet 'Paste'.
 
 
*--- disconnect from Excel
FREE OBJECT h_zl.
FREE OBJECT h_mapl.
FREE OBJECT h_map.
FREE OBJECT h_excel.

0 Kudos
84

hi kris,

i am sending you my code, it is working fine with the old method that was very slow but when i used the method clipboard_export i am getting an rfc error.

CALL METHOD CL_GUI_CFW=>FLUSH

EXCEPTIONS

CNTL_SYSTEM_ERROR = 1

CNTL_ERROR = 2

others = 3.

IF SY-SUBRC <> 0 OR RC = -1.

RAISE CNTL_ERROR.

ENDIF.

above method is called inside the clipboard_export method and this method is returning sy-subrc 1 and rc = -1.

***********my code*********************

SET PROPERTY OF sheet 'NAME' = 'COMPLETE VIEW'.

CALL METHOD OF sheet 'ACTIVATE'.

index = row_max * ( sy-tabix + 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

call method of cells 'COLUMNS' = column.

call method of column 'ColumnWidth' = value1.

loop at gt_fieldcat into ls_seltext.

index = row_max * ( sy-tabix + 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = i.

SET PROPERTY OF cells 'Value' = ls_seltext-seltext_l.

get property of cells 'FONT' = font.

set property of font 'BOLD' = 1.

get property of cells 'INTERIOR' = inside.

set property of inside 'COLORINDEX' = 15.

set property of cells 'ColumnWidth' = 33.

i = i + 1.

endloop.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT

IMPORTING

DATA = gt_out_chng

CHANGING

RC = l_rc

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

NOT_SUPPORTED_BY_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.

call method of sheet 'PASTE'.

data is not coming on the sheet, with this method .

can you please solve this problem.

null

0 Kudos
84

Hi Sudeep,

Couple of things..

first make sure of the declaration of the internal table

it has to be of the type supported by the function

TYPES: data1(1500) TYPE c,
ty TYPE TABLE OF data1.

DATA: itab TYPE ty WITH HEADER LINE.

next, when you are calling the function module, pass the table as itab[] as shown...

CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = itab[]
  CHANGING
    rc                   = l_rc.

0 Kudos
84

hi krish,

i had modify code as shown below but i am still not able to paste the data in the sheet.

loop at gt_out_chng into wa_output_tab.

it_all_user-bname = wa_output_tab-bname.

it_all_user-modda = wa_output_tab-modda.

it_all_user-modti = wa_output_tab-modti.

it_all_user-counter = wa_output_tab-counter.

it_all_user-modbe = wa_output_tab-modbe.

it_all_user-action = wa_output_tab-action.

it_all_user-old_val = wa_output_tab-old_val.

it_all_user-old_text = wa_output_tab-old_text.

it_all_user-new_val = wa_output_tab-new_val.

it_all_user-new_text = wa_output_tab-new_text.

it_all_user-tcode = wa_output_tab-attrbt.

append it_all_user.

endloop.

loop at it_all_user.

CONCATENATE

it_all_user-bname

it_all_user-modda

it_all_user-modti

it_all_user-counter

it_all_user-modbe

it_all_user-action

it_all_user-old_val

it_all_user-old_text

it_all_user-new_val

it_all_user-new_text

it_all_user-tcode

INTO it

SEPARATED BY deli.

APPEND it.

CLEAR it.

ENDLOOP.

CONSTANTS: row_max TYPE i VALUE 256.

DATA: index TYPE i.

  • i type i value '257'.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

SET PROPERTY OF excel 'VISIBLE' = 1.

SET PROPERTY OF excel 'DisplayAlerts' = 0.

CALL METHOD OF excel 'WORKBOOKS' = workbook .

SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF workbook 'ADD'.

get property of excel 'ACTIVESHEET' = sheet.

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

call method of excel 'CELLS' = cell1

exporting

#1 = 1

#2 = 1.

call method of excel 'CELLS' = cell2

exporting

#1 = 1

#2 = 50.

call method of excel 'RANGE' = Cells

exporting

#1 = cell1

#2 = cell2.

get property of cells 'FONT' = font.

set property of font 'BOLD' = 1.

data l_rc type i.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT

IMPORTING

DATA = it[]

CHANGING

RC = l_rc

  • EXCEPTIONS

  • CNTL_ERROR = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_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.

call method of excel 'CELLS' = cell1

exporting

#1 = 1

#2 = 1.

call method of excel 'CELLS' = cell2

exporting

#1 = 1

#2 = 1.

call method of excel 'RANGES' = RANGE

exporting

#1 = cell1

#2 = cell2.

call method of range 'SELECT'.

call method of sheet 'PASTE'.

0 Kudos
84

Ok im not too sure why you are getting the error

you can try a few things

1. check that you have put the delimiter code

rec TYPE sy-tfill,
deli(1) TYPE c,
l_amt(18) TYPE c.
DATA: BEGIN OF hex,
tab TYPE x,
END OF hex.

FIELD-SYMBOLS: <fs> .

CONSTANTS cns_09(2) TYPE n VALUE 09.

ASSIGN deli TO <fs> TYPE 'X'.
hex-tab = cns_09.
<fs> = hex-tab.

2. find out where the error lies

after execution, open notepad and choose Paste

this will check if the data was copied properly

3. put a breakpoint before calling the export to clipboard function

your internal table should looks something like this


ABC#DEF#GHI#JKL#MNO#############################################XYZ
ABC1#DEF1#GHI1#JKL1#MNO1#############################################XYZ1
ABC2#DEF2#GHI2#JKL2#MNO2#############################################XYZ2
ABC3#DEF3#GHI3#JKL3#MNO3#############################################XYZ3
ABC4#DEF4#GHI4#JKL4#MNO4#############################################XYZ4

0 Kudos
84

hi krish,

every thing is working fine till

CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT

my internal table is having data in the format you shown below,

problem comes when "call method of sheet 'PASTE'." is called error occurs

this function module returns sy-subrc value 2,

CALL FUNCTION 'AC_FLUSH_CALL'

EXPORTING

SYSTEM_FLUSH = ' '

IMPORTING

MESSAGE_NR = rc

MESSAGE_TEXT = msgli.

which means Control Framework : Communication error in the RFC

why it is coming i don't know same logic is working fine in my IDES 6.4,

is there any some customization issues (just a guess) ?????

i hope i am not bothering you, actually i am very new in this technology,

0 Kudos
84

so you mean that you can paste it in notepad ?

0 Kudos
84

hi krish ,

thanks a lot for all your valuable help,

every thing is working fine, thanks for all your early response,

thanks again frnd.

god bless you,

null

Former Member
0 Kudos
84

Use XML.

Create a very simple excel workbook with three sheets, save as XML, and look at the result in Notepad. You can create the same structure using ABAP.

I have written a wili about this, or search for Excel XML on the forum.

Michael