OLE excel -> teilergebnis

OLE excel -> teilergebnis

hallo allerseits,

ich möchte ein bestehendes excel-file mittels OLE-technik formatieren. das funktioniert soweit ganz gut, nur das mit den teilergebnissen schaffe ich nicht.

nach 2 tagen sucherei und probiererei habe ich aufgegeben.
vielleicht hat sonst jemand eine idee, wie das funktioniert.

um mein problem verständlicher zu machen, habe ich mein programm vereinfacht, damit jeder das coding auf die schnelle testen kann.
anbei für copy + paste oder als txt-file downloadbar von hier:

http://bluemer.antville.org/static/blue ... ng_ole.txt

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

*---------------------------------------------------------------------*
* Report ZVEXCELOLE_TMP
* Author: Matthias Leitner
*---------------------------------------------------------------------*

REPORT zvexcelole_tmp NO STANDARD PAGE HEADING LINE-SIZE 132.

* ------------------------------------------------------------------- *
* datendeklaration *
* ------------------------------------------------------------------- *

DATA: it_filetable TYPE filetable.
DATA: wa_filetable TYPE file_table.

DATA: wa_filename TYPE string,
wa_upload TYPE string,
wa_download TYPE string,
wa_file(255) TYPE c,
wa_rc TYPE i.

*** OLE-Definitionen
INCLUDE ole2incl.

DATA: wa_loesch type i,
o_excel TYPE ole2_object,
o_workbook TYPE ole2_object,
o_columns TYPE ole2_object,
o_autofit TYPE ole2_object,
o_blatt TYPE ole2_object.

* ------------------------------------------------------------------- *
* selection-screen *
* ------------------------------------------------------------------- *

SELECTION-SCREEN BEGIN OF BLOCK z3 WITH FRAME.
PARAMETERS: pa_dir(255) TYPE c LOWER CASE .
SELECTION-SCREEN END OF BLOCK z3.

* ------------------------------------------------------------------- *
* initialization *
* ------------------------------------------------------------------- *

INITIALIZATION.

* standardmässiges download-verzeichnis holen
CALL METHOD cl_gui_frontend_services=>get_upload_download_path
CHANGING
upload_path = wa_upload
download_path = wa_download
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
gui_upload_download_path = 4
upload_download_path_failed = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
MOVE wa_download TO pa_dir.
ENDIF.

* ------------------------------------------------------------------- *
* at selection-screen *
* ------------------------------------------------------------------- *

AT SELECTION-SCREEN.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_dir.

CALL METHOD cl_gui_frontend_services=>file_open_dialog
* EXPORTING
* WINDOW_TITLE =
* DEFAULT_EXTENSION =
* DEFAULT_FILENAME =
* FILE_FILTER =
* INITIAL_DIRECTORY =
* MULTISELECTION =
* WITH_ENCODING =
CHANGING
file_table = it_filetable
rc = wa_rc
* USER_ACTION =
* FILE_ENCODING =
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ELSE.
READ TABLE it_filetable INTO wa_filetable INDEX 1.
MOVE wa_filetable-filename TO pa_dir.
ENDIF.

* ------------------------------------------------------------------- *
* start-of-selection *
* ------------------------------------------------------------------- *

START-OF-SELECTION.

PERFORM excel_format.

MOVE pa_dir TO wa_file.
FORMAT HOTSPOT ON.
WRITE: wa_file+0(132).
FORMAT HOTSPOT OFF.
HIDE wa_file.
CLEAR wa_file.

* ------------------------------------------------------------------- *
* at line-selection *
* ------------------------------------------------------------------- *

AT LINE-SELECTION.

CLEAR wa_file.
READ CURRENT LINE.

IF NOT wa_file IS INITIAL.
MOVE wa_file TO wa_filename.
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
document = wa_filename
* application =
* PARAMETER =
* DEFAULT_DIRECTORY =
* MAXIMIZED =
* MINIMIZED =
* SYNCHRONOUS =
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
bad_parameter = 3
file_not_found = 4
path_not_found = 5
file_extension_unknown = 6
error_execute_failed = 7
OTHERS = 8
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDIF.

* ------------------------------------------------------------------- *
* form excel_format *
* ------------------------------------------------------------------- *
FORM excel_format.

* start excel
CREATE OBJECT o_excel 'EXCEL.APPLICATION'.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. EXCEL.APPLICATION'.
ENDIF.

* excel -> not visible
SET PROPERTY OF o_excel 'Visible' = 0. "visible = 1
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Visible'.
ENDIF.

* Arbeitsblatt auswählen
CALL METHOD OF o_excel 'Workbooks' = o_workbook.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Workbooks'.
ENDIF.

* open existing file
CALL METHOD OF o_workbook 'OPEN'
EXPORTING
#1 = pa_dir.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. OPEN'.
ENDIF.

* SUBTOTALS!!!

* bei allen spalten auf optimale breite setzen
CALL METHOD OF o_excel 'Columns' = o_columns.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Columns'.
ENDIF.

CALL METHOD OF o_columns 'Autofit' = o_autofit.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Autofit'.
ENDIF.

* save and close excel
CALL METHOD OF o_excel 'Worksheets' = o_blatt
EXPORTING
#1 = 1.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. Worksheets'.
ENDIF.

CALL METHOD OF o_blatt 'SaveAs'
EXPORTING
#1 = pa_dir
#2 = 1. "fileFormat
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. SaveAs'.
ELSE.

ENDIF.

CALL METHOD OF o_workbook 'CLOSE'.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. CLOSE'.
ENDIF.

CALL METHOD OF o_excel 'QUIT'.
IF sy-subrc NE 0.
WRITE: / 'OLE-Fehler. QUIT'.
ENDIF.

FREE o_excel.

ENDFORM. "FORM excel_format

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

das zu verändernde excel-file soll nach spalte 6 gruppiert werden und nach spalte 4 summiert.

ich habe auch dieses zum download vorbereitet:
http://bluemer.antville.org/static/blue ... ispiel.xls

ACHTUNG: das hat die teilergebnisse schon eingefügt, um zu zeigen, wie ich das meine. bitte diese löschen, dann kann das file als testfile verwendet werden.

ich hoffe, meine problemstellung ist klar. ansonsten bitte nachfragen.

danke schon mal im voraus!
matthias

unser system:
SAP_BASIS 620
excel 2002 - service pack 3[/url]