Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

OLE excel -> subtotals

hello everybody,

i am trying to format an already existing excel-file with ole-technique.

this works fine so far, but (of course) there is one very important step i can't handle.

i need to make subtotals and after 2 days of trying and searching the net i have no more ideas how it could work.

to make it easier to understand i have made a simple excerpt which everyone should be able to use as local report by copy/paste or you can download the coding as txt-file from here:

<a href="http://bluemer.antville.org/static/bluemer/files/abapcoding_ole.txt">txt-file</a>

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

*----


*

  • 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

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

the excel-file should be grouped by column 6 and sum column 4.

you can download an example of the excel-sheet (already including the subtotals as i need them):

<a href="http://bluemer.antville.org/static/bluemer/files/faktbeilage06_beispiel.xls">xls-file</a>

i hope my question is clear. if i have forgotten anything important - please ask.

our system:

SAP_BASIS 620

excel 2002 - service pack 3

thanks in advance!

matthias

Message was edited by: Matthias Leitner

Tags:

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question