Re: Help Regarding OLE

mnicolai_77 — Thu, 10 Jan 2008 00:56:02 GMT

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

topic Re: Help Regarding OLE in Application Development and Automation Discussions

Help Regarding OLE

Re: Help Regarding OLE

Re: Help Regarding OLE