Solved: OLE excel -> subtotals

Former Member · ‎04-10-2006

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:

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

*----

*

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.

------------------------------------------------------------------- *
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):

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

vinod_gunaware2 · ‎04-10-2006

Hi

Check below function modules it may be useful.

<b>EXCEL_OLE_STANDARD_DAT

MS_EXCEL_OLE_STANDARD_DAT

KCD_EXCEL_OLE_TO_INT_CONVERT

ALSM_EXCEL_TO_INTERNAL_TABLE

FTBU_START_EXCEL</b>

regards

vinod

vinod_gunaware2 · ‎04-10-2006

Hi

Check below function modules it may be useful.

<b>EXCEL_OLE_STANDARD_DAT

MS_EXCEL_OLE_STANDARD_DAT

KCD_EXCEL_OLE_TO_INT_CONVERT

ALSM_EXCEL_TO_INTERNAL_TABLE

FTBU_START_EXCEL</b>

regards

vinod

Former Member · ‎04-10-2006

hi vinod,

thank you very much for the reply.

if i get it right the first two function modules don't have any functionallity for making subtotals. the next two just put excel-data into internal format.

the last may be a possibility to use an external excel makro .

but i think it must be possible to do this by OLE.

best regards,

matthias

Former Member · ‎04-12-2006

sorry to push this up once more, but i still haven't got any idea how to solve this.

Former Member · ‎04-20-2007

Hallo Matthias

I have the same problem with OLE - Excel and subtotals in this moment.

Did you find a solution? if yes, can you explain it?

Thanks in advance.

Martin