downlaod to Excel sheet using ole

[Former Member]

Hi,

I have to use the "TRIM" function of excel sheet in my program .Is there any way

so that i can get the functionality of TRIM function other than using the formula of

TRIM function.

Thanks ,

Bhanu.

[Former Member]

Hi Bhanu,

I don't think there is such functionality available.

Regards,

Atish

[former_member189059]

Hello Bhanu,

You can make it work by putting the code for trim into the cell value itself

such as

  SET PROPERTY OF cells 'Value' = '=TRIM("Name   12")'.

or maybe you could use code such as

concatenate '=TRIM("' value '")' into value.

[Former Member]

hi Kris,

Thanks for immediate reply.

had tried this way.But in my program am sending whole data at a time thru internal table.That is with out any loops.

so the formula used in the following is not working.

DATA deli(1).

concatenate '=TRIM( "Amount ")' ' ' ' ' ' ' into variable separated by deli.

this is not solving the problem as i want to have the first field value when the excel is first dispalyed without expanding the column even if the field name is long also.

Thanks ,

Bhanu.

[former_member189059]

Hello bahnu,

I am not sure if i understood your requirement

If you need the TRIM to be applied on a particular column of your internal table, then you can loop the code that i gave as follows.

loop at itab.
  concatenate '=TRIM("' itab-field '")' into itab-field.
  modify itab.
endloop.

and make sure that ur datatype can accomodate that length of data

or do you want to autofit the column or get rid of data such as '3.42345E+16' displayed in excel ?

[Former Member]

Hi Kris,

loop u suggested is fine for only one field

am using CALL METHOD cl_gui_frontend_services=>clipboard_export

to pass data inthe form of internal table.. to the excel

here the xl_itab contains a character field of 300 characters and append ever thing before passing to the cell. so here the below code

data:deli(1),

begin of xl_itab,

str(300),

end of xl_itab.

concatenate '=trim(itab-field)' ' ' ' ' total into xl_table -str separated by deli.

produces output as -

---

firstcolumn --itab ; and in the 4th cell the total.

that is 'deli' is used as a columnn separater ........

and in this case if the itab-field is long we have to drag the column to see the full name.

conacatenate '=trim(itab-filed)' into xl_table-str.

i can able to see the full name without when xcel is first displayed.

<u><b>if this is not possible is there anyway to set the width of a particular column?</b></u>

Thanks

Bhanu.

[former_member189059]

Ok Bhanu

after a lot of experimenting i finally managed to set the width of a column

DATA: h_columns TYPE ole2_object.

CALL METHOD OF excel 'Columns' = h_columns
  EXPORTING
  #1 = 'A:A'.
* this is for column A.. if u want to put it for A and B, use A:B

set property of h_columns 'ColumnWidth' = 17.

[Former Member]

Thanks a lot Kris.Really appreciate ur effort.

Can u pls tell me where can u c methods for the excel sheets.

Regards,

Bhanu

[former_member189059]

Open any blank excel sheet

Go to Tools -> Macro -> Record New Macro

Put a name and put ok

then do whatever you want to do (such as adjusting the columns)

Click on the Stop button (in the popup that comes once macro recording starts)

Go to Tools -> Macro -> Macros

choose the macro you just made

Click on Edit (4th button on the right)

Then see the vb code

Some of them are methods and some are properties

Use these in your abap code

[Former Member]

thanqqqqqqq

sorry i can award 10 points only once.:-)

Thanks once again,

bye,bhanu.

[former_member189059]

no problem...

but i still cannot manage to left align the cell

it should be as follows according to me

  CALL METHOD OF excel 'Cells' = h_cell
    EXPORTING
    #1 = 14
    #2 = 3.


  SET PROPERTY OF h_cell 'Value' = '1234'.
  set property of h_cell 'HorizontalAlignment' = 'xlLeft'.

but this doesnt seem to work

please let me know if you manage it

[Former Member]

Hi,

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

SET PROPERTY OF EXCEL 'VISIBLE' = 1. "Make excel application visible.

CALL METHOD OF EXCEL 'Workbooks' = WORKBOOK.

PERFORM ERROR_CHECK .

FILEPATH = 'C:\filename.xls'.

• Open the file

CALL METHOD OF WORKBOOK 'OPEN' EXPORTING #1 = FILEPATH.

*Set a particular work sheet

CALL METHOD OF EXCEL 'Worksheets' = WSHEET EXPORTING #1 = 1.

CALL METHOD OF WSHEET 'Activate'.

CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = 4 #2 = 2.

SET PROPERTY OF CELL 'VALUE' = table-fieldname

.

.

.

.

Can Declare No. of fields to be downloaded.

FORM ERROR_CHECK.

IF SY-SUBRC NE 0 .

WRITE : / 'Please load excel before downloading ' .

EXIT .

ENDIF .

ENDFORM.

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

Regards,

IFF

Note: Pls award points if found suitable.