Help Regarding OLE

Former Member · ‎01-09-2008

CREATE OBJECT app 'Excel.Application'.

GET PROPERTY OF app 'Visible' = vis.

Here in this case the things are:

automation object is app.

attribute is Visible.

data object is vis.

now can any one tell me where to find all the attribute of Excel application ....

an dhow to use them.......

mnicolai_77 · ‎01-10-2008

hi,

you can find all members of class excell.application.1 here:

open excell.

open VB script of excell.

look in Object list.

here you can find all member attribute property.

For example i give to you an include of a module pool which download in excell format usin OLE integration Purchase order history.

>*----

*

>* INCLUDE ZCS01_MM_REPACQ_FORM_EXCEL *

>*----

*

>*----

*

>* 1 - MACRO -- " CELLA " -- *

>*----

*

>* &1 --> row index *

>* &2 --> column index *

>* &3 --> TRUE FALSE bold or not *

>* &4 --> cell value *

>*----

*

>DEFINE $cella.

call method of worksheet 'CELLS' = cell exporting #1 = &1 #2 = &2.
set property of cell 'VALUE' = &4 .
call method of cell 'FONT' = font .
set property of font 'bold' = &3 .
free object font .
free object cell .

>END-OF-DEFINITION.

>*&----

*

>*& Form download_excel

>*&----

*

>* download in excel format using OLE

>*----

*

>FORM download_excel.

i = 1.
j = 1.

>

>"Creatin an Excell

CREATE OBJECT excel 'EXCEL.application'.

>"Opening file and sheet

SET PROPERTY OF excel 'VISIBLE' = 1. " SALVE E NON APRE IL FILE
vn_nrotb = 1.
SET PROPERTY OF excel 'SHEETSINNEWWORKBOOK' = vn_nrotb.
CALL METHOD OF excel 'WORKBOOKS' = workbooks .
CALL METHOD OF workbooks 'ADD' = workbook .
SET PROPERTY OF excel 'DisplayAlerts' = 0.
CALL METHOD OF workbook 'WORKSHEETS' = worksheets .

>" Naming the sheet

CALL METHOD OF worksheets 'ITEM' = worksheet EXPORTING #1 = 1.
SET PROPERTY OF worksheet 'NAME' = 'Document Flush'.
CALL METHOD OF worksheet 'ACTIVATE' .
PERFORM write_excel.

>

CALL METHOD OF workbook 'SAVEAS' EXPORTING #1 = file .
FREE OBJECT worksheet .
FREE OBJECT worksheets .
FREE OBJECT workbook .
FREE OBJECT workbooks .
FREE OBJECT excel .

>ENDFORM. " download_excel

>*&----

*

>*& Form write_excel

>*&----

*

>* Create excell data

>*----

*

>FORM write_excel.

PERFORM ex_rda_tes.
PERFORM ex_oda_tes.
PERFORM ex_em_tes.
PERFORM ex_fat_tes.
PERFORM Header.
SUBTRACT 1 FROM i.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1 #2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 1.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2'.
SET PROPERTY OF bord 'LINESTYLE' = '7'.
SET PROPERTY OF bord 'WEIGHT' = '3'.
FREE OBJECT bord .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1 #2 = 9.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 9.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2' .
SET PROPERTY OF bord 'LINESTYLE' = '7' .
SET PROPERTY OF bord 'WEIGHT' = '3' .
FREE OBJECT bord .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1 #2 = 12.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 12.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2' .
SET PROPERTY OF bord 'LINESTYLE' = '7' .
SET PROPERTY OF bord 'WEIGHT' = '3' .
FREE OBJECT bord .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .

>

CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1
#2 = 15.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i
#2 = 15.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2' .
SET PROPERTY OF bord 'LINESTYLE' = '7' .
SET PROPERTY OF bord 'WEIGHT' = '3' .

>

FREE OBJECT bord .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1
#2 = 18.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i
#2 = 18.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2'.
SET PROPERTY OF bord 'LINESTYLE' = '7'.
SET PROPERTY OF bord 'WEIGHT' = '3'.
FREE OBJECT bord .
FREE OBJECT range .
FREE OBJECT cell1 .
FREE OBJECT cell2 .

>

>ENDFORM. " scrittura_excel

>*&----

*

>*& Form Header

>*&----

*

>* Print Header

>*----

*

>FORM Header.

DATA: BEGIN OF tab_sint OCCURS 0,
banfn LIKE eban-banfn,
END OF tab_sint.
DATA: n(5) TYPE c.
DATA: riga(5) TYPE c.
DATA: per TYPE i.

>

LOOP AT t_stampa_sint WHERE flag_scar = 'X'.
MOVE-CORRESPONDING t_stampa_sint TO tab_sint.
APPEND tab_sint.
CLEAR tab_sint.
ENDLOOP.
SORT tab_sint.
DELETE ADJACENT DUPLICATES FROM tab_sint COMPARING ALL FIELDS.
SORT t_stampa_sint ASCENDING BY banfn ebeln belnr fatno.
DELETE ADJACENT DUPLICATES FROM t_stampa_sint COMPARING ALL FIELDS.
DESCRIBE TABLE tab_sint LINES n.
ADD 10 TO n.
LOOP AT tab_sint .
ADD 1 TO riga.
per = trunc( ( ( riga / n ) * 100 ) ).
CLEAR: rda, oda, em, fat.
IF NOT tab_sint-banfn IS INITIAL.
LOOP AT t_stampa_sint WHERE banfn = tab_sint-banfn.
PERFORM scar_intes .
ADD 1 TO i.
ENDLOOP.
ELSE.
LOOP AT t_stampa_sint WHERE banfn = tab_sint-banfn
AND flag_scar = 'X'.
PERFORM scar_intes.
PERFORM stampa_tes_pos.
j = 1.
LOOP AT t_stampa_ana WHERE ebeln = t_stampa_sint-ebeln.
PERFORM scar_pos.
ADD 1 TO i.
ENDLOOP.
ADD 1 TO i.
ENDLOOP.
ENDIF.
j = 1.
DATA:i1 TYPE i.
MOVE i TO i1.
PERFORM posizione USING tab_sint-banfn.

>

CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i1 #2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 17.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.

>

CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .

>

ADD 1 TO i.
LOOP AT i_tab.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i_tab-i
#2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i_tab-i
#2 = 16.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'FONT' = font .
SET PROPERTY OF font 'COLORINDEX' = 3.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ENDLOOP.
$cls i_tab.

>

ENDLOOP.

>ENDFORM. " testata

>*&----

*

>*& Form posizione

>*&----

*

>* form per lo scarico delle posizioni

>*----

*

>FORM posizione USING banfn.

>

PERFORM stampa_tes_pos.
IF NOT banfn IS INITIAL.
LOOP AT t_stampa_ana WHERE banfn = banfn.
PERFORM scar_pos.
ADD 1 TO i.
ENDLOOP.
ENDIF.

>ENDFORM. " posizione

>

>*&----

*

>*& Form stampa_tes_pos

>*&----

*

>* form per la stampa su excel della testata delle posizioni

>*----

*

>FORM stampa_tes_pos.

$cella i 1 'TRUE' 'Pur. Req. Item'.
$cella i 2 'TRUE' 'Cost center'.
$cella i 3 'TRUE' 'G/L Account.'.
$cella i 4 'TRUE' 'Vendor'.
$cella i 5 'TRUE' 'Material'.
$cella i 6 'TRUE' 'Description'.
$cella i 7 'TRUE' 'PR Quantity'.
$cella i 8 'TRUE' 'Net Amount'.
$cella i 9 'TRUE' 'Pur. Ord. Number'.
$cella i 10 'TRUE' 'Pur. Ord. Item'.
$cella i 11 'TRUE' 'PO quantity' .
$cella i 12 'TRUE' 'Good Receive'.
$cella i 13 'TRUE' 'Item'.
$cella i 14 'TRUE' 'GR Quantity'.
$cella i 15 'TRUE' 'Invoice'.
$cella i 16 'TRUE' 'Inveoice Item'.
$cella i 17 'TRUE' 'Gross Amount'.

>

ADD 1 TO i.

>ENDFORM. " stampa_tes_pos

>*&----

*

>*& Form EX_RDA_TES

>*&----

*

>* scrittura e formattazione automatica della testata generale rda

>*----

*

>FORM ex_rda_tes.

>

$cella i j 'TRUE' 'Data: '.
$cella i 2 'TRUE' sy-datum.
ADD 1 TO i.

>

$cella i 1 'TRUE' 'Purchase Requisition'.

>

CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 9.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.

>

CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .

>

>ENDFORM. " EX_RDA_TES

>*&----

*

>*& Form EX_ODA_TES

>*&----

*

>* scrittura e formattazione automatica della testata generale oda

>*----

*

>FORM ex_oda_tes.

$cella i 9 'TRUE' 'Ordine di Acquisto'.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 9.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 11.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .

>ENDFORM. " EX_ODA_TES

>*&----

*

>*& Form EX_EM_TES

>*&----

*

>* scrittura e formattazione automatica della testata generale e.m.

>*----

*

>FORM ex_em_tes.

$cella i 12 'TRUE' 'Entrata merci'.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 12.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 14.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .

>

>ENDFORM. " EX_EM_TES

>*&----

*

>*& Form EX_FAT_TES

>*&----

*

>* scrittura e formattazione automatica della testata generale fatture

>*----

*

>FORM ex_fat_tes.

$cella i 15 'TRUE' 'Fattura'.
$cella 1 15 'TRUE' gen_nome.

>

CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 15.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 17.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .

>ENDFORM. " EX_FAT_TES

>*&----

*

>*& Form scar_intes

>*&----

*

>* form per lo scarico delle intestazioni delle colonne (Generale)

>*----

*

>FORM scar_intes.

>

DATA: var1(20) TYPE c.
DATA: var2(20) TYPE c.

>

IF t_stampa_sint-banfn <> rda.
MOVE t_stampa_sint-banfn TO rda.
CLEAR: var1, var2.
CONCATENATE 'N° :' t_stampa_sint-banfn INTO var1.
$cella i 1 'TRUE' var1.
WRITE t_stampa_sint-badat TO var2 USING EDIT MASK '__.__.____'.
CONCATENATE 'Data :' var2 INTO var2.
$cella i 3 'TRUE' var2.
ENDIF.
IF t_stampa_sint-ebeln <> oda.
CLEAR: var1, var2.
MOVE t_stampa_sint-ebeln TO oda.
CONCATENATE 'N° :' t_stampa_sint-ebeln INTO var1.
$cella i 9 'TRUE' var1.
WRITE t_stampa_sint-bedat TO var2 USING EDIT MASK '__.__.____'.
CONCATENATE 'Data :' var2 INTO var2.
$cella i 11 'TRUE' var2.
ENDIF.
IF t_stampa_sint-belnr <> em.
CLEAR: var1, var2.
MOVE t_stampa_sint-belnr TO em.
CONCATENATE 'N° :' t_stampa_sint-belnr INTO var1.
$cella i 12 'TRUE' var1.
WRITE t_stampa_sint-budat TO var2 USING EDIT MASK '__.__.____'.
CONCATENATE 'Data :' var2 INTO var2.
$cella i 14 'TRUE' var2.
ENDIF.
IF t_stampa_sint-fatno <> fat.
CLEAR: var1, var2.
MOVE t_stampa_sint-fatno TO fat.
CONCATENATE 'N° :' t_stampa_sint-fatno INTO var1.
$cella i 16 'TRUE' var1.
ENDIF.

>ENDFORM. " scar_intes

>*&----

*

>*& Form scar_pos

>*&----

*

>* form per lo scarico delle posizioni

>*----

*

>FORM scar_pos.

DATA: format(17) TYPE c.
CLEAR flag_error_qta.
PERFORM check_qta_rec.
$cella i 1 'FALSE' t_stampa_ana-bnfpo.
$cella i 2 'FALSE' t_stampa_ana-kostl.
$cella i 3 'FALSE' t_stampa_ana-sakto.
$cella i 4 'FALSE' t_stampa_ana-lifnr.
$cella i 5 'FALSE' t_stampa_ana-matnr.
$cella i 6 'FALSE' t_stampa_ana-txz01.
CLEAR format.
WRITE t_stampa_ana-menger TO format UNIT t_stampa_ana-meinsr.
$cella i 7 'FALSE' format.
CLEAR format.
WRITE t_stampa_ana-preis TO format CURRENCY 'EUR'.
$cella i 8 'FALSE' format.
$cella i 9 'FALSE' t_stampa_ana-ebeln.
$cella i 10 'FALSE' t_stampa_ana-ebelp.
CLEAR format.
WRITE t_stampa_ana-mengeo TO format UNIT t_stampa_ana-meinso.
$cella i 11 'FALSE' format.
$cella i 12 'FALSE' t_stampa_ana-belnr.
$cella i 13 'FALSE' t_stampa_ana-buzei.
CLEAR format.
WRITE t_stampa_ana-mengee TO format UNIT t_stampa_ana-meinse.
$cella i 14 'FALSE' format.
$cella i 15 'FALSE' t_stampa_ana-fatno.
$cella i 16 'FALSE' t_stampa_ana-fatpo.
CLEAR format.
WRITE t_stampa_ana-dmbtr TO format CURRENCY 'EUR'.
$cella i 17 'FALSE' format.
IF flag_error_qta = 'X'.
MOVE i TO i_tab-i.
APPEND i_tab.
ENDIF.

>ENDFORM. " scar_pos

bye.

Marco

Former Member · ‎01-10-2008

THANKS DUDE................