Excel conversion

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
snehal

Excel conversion

Post by snehal » Fri Nov 08, 2002 8:15 am

Hi, is ther any function in SAP through which i can download my internal table data to excel , specifing paper size, and warping text in the excel file. this function like warping text, naming the excel file. giveing page nos at the end. all has to be done through the function itself. Is there any ready made Function module which do teh same in SAP

mlnaras
Posts: 88
Joined: Tue Oct 22, 2002 9:19 am

download function

Post by mlnaras » Fri Nov 08, 2002 12:30 pm

hi

check out GUI_DOWNLOAD, DOWNLOAD.

mlnaras

ocvantol
Posts: 929
Joined: Tue Oct 22, 2002 3:19 am
Location: The Hague

Post by ocvantol » Fri Nov 08, 2002 1:55 pm

check out GUI_DOWNLOAD, DOWNLOAD.
Don't bother, won't work.

I think you have to use OLE functions. Never done it, don't know how to do it, but that should be the general direction.

Keep us informed.

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Post by Rich » Fri Nov 08, 2002 4:31 pm

You can get at them using a variety of functions that generally have the name 'EXCEL' in them somewhere, or you can do the work yourself.....

Code: Select all

*
*    Open the workbook
*
     Create Object Excel 'EXCEL.APPLICATION'.
     Call Method Of Excel 'WORKBOOKS' = W_Book.
     Call Method Of W_Book 'OPEN' EXPORTING #1 = Pu_File.
Then you need to activate thw work sheets.....

Code: Select all

*
*       Select the 5 work books in turn
*
        Loop at t_worksheets.
             Call Method Of Excel
                            'Worksheets' = w_Sheet
                  Exporting #1 = t_worksheets-SheetName.
             Call Method Of W_sheet 'Activate'.
Loop through the cells, getting or setting the values:

Code: Select all

    Clear pc_value.
     Call Method Of Pu_Spreadsheet 'Cells' = Cell Exporting #1 = pu_row
                                                            #2 = pu_col.
     Get Property Of Cell 'Value' = pc_value-val.
     Free Object Cell.
and finally close the work book:

Code: Select all

*
*       Close the work book and free the various objects.
*
        Call Method of W_book 'CLOSE'.
        Call Method of Excel 'QUIT'.
        Free Object w_sheet.
        Free Object w_book.
        Free Object Excel.
To find the methods and proerties, I tend to record what I want to do in VBA and then translate that into ABAP.

Regards,

Rich

Manoj Pingali
Posts: 78
Joined: Wed Oct 23, 2002 5:08 pm
Location: St.Louis, USA

Post by Manoj Pingali » Fri Nov 08, 2002 6:19 pm

You can further refer to the demo program 'RSDEMO01'. This program illustrates how to copy R/3 data to Excel using OLE calls.

Have fun,

Manoj Pingali

kevin
Posts: 147
Joined: Sun Oct 20, 2002 10:18 pm
Location: Mumbai, India
Contact:

Post by kevin » Sun Nov 10, 2002 10:24 pm

Hi all,


rich, In your example code u suggested .

Loop through the cells, getting or setting the values:

Code:
Clear pc_value.
Call Method Of Pu_Spreadsheet 'Cells' = Cell Exporting #1 = pu_row
#2 = pu_col.
Get Property Of Cell 'Value' = pc_value-val.
Free Object Cell.

Is there any other way to populate cells in Excel (i.e. using OLE) .
like using
CLIPBOARD_EXPORT
CLIPBOARD_IMPORT


The cell by cell method seems to take an eternity to complete.

Any Ideas
:)

regards,

Kevin Hill

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Post by Rich » Mon Nov 11, 2002 4:14 am

Haven't gone down that route so I couldn'tn help you, but...... I'm currently working ion an Excel interface so that means I can give it a try without too much conscience.

Rich

blueei
Posts: 11
Joined: Mon Nov 11, 2002 4:40 am
Location: Singapore

Re: download function

Post by blueei » Mon Nov 11, 2002 6:06 am

mlnaras wrote:hi

check out GUI_DOWNLOAD, DOWNLOAD.

mlnaras
:?: does this way of doing suitable for web GUI of SAP?
does this way of doing clears and creates a new file? how does this actually leads to creating a chart automatically?

doesn't actually the function "download" or "gui_download" actually does the job? how come there is a need in OLE function? can someone please kindly explain this?

how does this differentiate from this program : "RSDEMO01"?

please kindly help... :oops: thankz...
-- blueei

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Post by Rich » Thu Mar 20, 2003 10:20 am

ws_download or gui_download only download text based info. There are no formatting codes, macros, or anything. They do not create native XLS files.

Rich

Cake
Posts: 204
Joined: Wed Oct 23, 2002 7:56 am

Post by Cake » Thu Mar 20, 2003 11:00 am

kevin wrote:Is there any other way to populate cells in Excel (i.e. using OLE) .
like using
CLIPBOARD_EXPORT
CLIPBOARD_IMPORT


The cell by cell method seems to take an eternity to complete.
For this kind of stuff I export two file to the presentation server, one that contain the data the other that contain the VBA coding for formatting the sheet. And with OLE call I open the data file add the VBA module and execute it.

Here is the code I use :

Code: Select all

FORM OLE_CALL.
* creating object Excel.Application
  CREATE OBJECT H_APPL 'excel.application'.
  IF SY-SUBRC NE 0.
    WRITE: / 'Error when opening excel.application', SY-MSGLI.
  ENDIF.
* Setting visibility property
  SET PROPERTY OF H_APPL 'Visible' = 1.
* "initialize" Excel with some necessary settings
  CALL METHOD OF H_APPL 'Workbooks' = H_WORK.
  PERFORM ERRORS using '1'.
*  CALL METHOD OF h_work 'Add'.
  CALL METHOD OF h_work 'Open' EXPORTING #1 = file_data.
  PERFORM ERRORS using '2'.
  CALL METHOD OF H_APPL 'Modules' = H_MODULE.
  PERFORM ERRORS using '3'.
  CALL METHOD OF H_MODULE 'Add' = H_NEWMODULE.
  PERFORM ERRORS using '4'.
  CALL METHOD OF H_NEWMODULE 'Activate'.
  PERFORM ERRORS using '5'.
* reading VBA program code
  CALL METHOD OF H_NEWMODULE 'InsertFile'
                         EXPORTING #1 = file_vba.
  PERFORM ERRORS using '6'.
* executing VBA program code
  CALL METHOD OF H_APPL 'Run' EXPORTING #1 = makroname.
  PERFORM ERRORS using '7'.

FREE H_APPL.
FREE H_WORK.
FREE H_MODULE.
FREE H_NEWMODULE.
ENDFORM.                    " OLE_CALL
CaKe

kevin
Posts: 147
Joined: Sun Oct 20, 2002 10:18 pm
Location: Mumbai, India
Contact:

Post by kevin » Thu Mar 20, 2003 10:09 pm

Hi Cake,

Tell me Cake since We are directly opening the text file from the Presentation server, how would we handle columns where leading zeros are required.
I find that the leading zeros get eliminated if I directly open a tab delimited file in excel.

This cause a lot of problems in certain columns, like Material where the elimination of leading zeros change the meaning of the material code.

regards,

Kevin

Cake
Posts: 204
Joined: Wed Oct 23, 2002 7:56 am

Post by Cake » Fri Mar 21, 2003 3:41 am

That's why I export also a VBA macro and execute it.
Moreover if you record the macro in Excel you will have the VBA coding ready for a copy/paste in your ABAP coding. This mean you don't have to find the OLE functions that you need.

Below the code I have to format a four characters number with leading zeroes :

Code: Select all

Columns("D:D").Select
Selection.NumberFormat = "0000"
Another solution is to export such fields as characters with a ' in the first position so Excel won't try to format it.

CaKe

Post Reply