Execute the OLE Object(Excel) in Background mode

former_member352977 · ‎01-22-2015

Hi All,

I have a doubt regarding the execution of OLE in Background.

There was a requirement in the project where i had to create the excel and upload it into AL11 with some data since the users wanted it as a background job. Now the problem is when i'm uploading it into AL11 they want the sheet name to be in a specified name.

When i browsed through i found out that the only option is to execute using ole object code which i used successfully.

Now i'm able to create an excel name the sheet and download it into my sytem but i'm not able to execute it in background mode at least pass it to AL11 in Background job which i'm not sure how.

I'm pasting the whole code in the window.

TYPE-POOLS: soi,ole2.

TYPES : BEGIN OF tt_marc,

matnr TYPE marc-matnr,

werks TYPE marc-werks,

END OF tt_marc.

Data: gt_marc TYPE TABLE OF tt_marc,

gs_marc TYPE tt_marc.

TYPES: data1(1500) TYPE c,

ty_data TYPE TABLE OF data1.

DATA: sheet TYPE ole2_object,

columns TYPE ole2_object.

DATA: l_rc TYPE i.

* Ole data Declarations

DATA: h_excel TYPE ole2_object, " Excel object

h_workbook TYPE ole2_object, " list of workbooks

h_map TYPE ole2_object, " workbook

h_interior TYPE ole2_object, " Pattern

worksheet TYPE ole2_object,

h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object,

range TYPE ole2_object,

h_font TYPE ole2_object,

h_row TYPE ole2_object.

DATA: deli(1) TYPE c.

DATA: int_sheet4 TYPE ty_data WITH HEADER LINE.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

PERFORM browse_dir CHANGING p_file.

START-OF-SELECTION.

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.

IF sy-subrc <> 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

CALL METHOD OF

h_excel

'Workbooks' = h_workbook.

SET PROPERTY OF h_excel 'DisplayAlerts' = 0.

SET PROPERTY OF h_excel 'Visible' = 1.

SET PROPERTY OF h_excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF

h_workbook

'ADD'.

* SET PROPERTY OF h_workbook 'NAME' = 'Vendor'.

*- Add sheet and activate

CALL METHOD OF

h_excel

'WORKSHEETS' = sheet

EXPORTING

#1 = 1.

CALL METHOD OF

sheet

'ACTIVATE'.

*- Pass the sheet name

SET PROPERTY OF sheet 'NAME' = 'Ven'.

FREE OBJECT sheet.

GET PROPERTY OF h_excel 'ACTIVESHEET' = sheet.

SELECT matnr werks FROM MARC into TABLE gt_marc UP TO 10 ROWS.

deli = cl_abap_char_utilities=>horizontal_tab.

Loop at gt_marc INTO gs_marc.

Concatenate gs_marc-matnr gs_marc-werks INTO int_sheet4 SEPARATED BY deli.

APPEND int_sheet4.

ENDLOOP.

*- Copy into clipboard

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = int_sheet4[]

CHANGING

rc = l_rc

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

not_supported_by_gui = 3

OTHERS = 4.

*

*- Paste clipboard contents into sheet

CALL METHOD OF

sheet

'PASTE'.

GET PROPERTY OF h_excel 'ActiveSheet' = sheet.

FREE OBJECT sheet.

FREE OBJECT h_workbook.

GET PROPERTY OF h_excel 'ActiveWorkbook' = h_workbook.

*

CONCATENATE p_file '\new.xls' INTO p_file.

*

CALL METHOD OF

h_workbook

'SAVEAS'

EXPORTING

#1 = p_file

#2 = 1.

CALL METHOD OF

h_workbook

'CLOSE'.

CALL METHOD OF

h_excel

'QUIT'.

FREE OBJECT sheet.

FREE OBJECT columns.

FORM browse_dir CHANGING p_file.

DATA: lo_gui TYPE REF TO cl_gui_frontend_services,

lv_title TYPE string,

lv_folder TYPE string,

lv_dir TYPE string.

CREATE OBJECT lo_gui.

lv_title = 'Define directory location'.

lv_folder = 'C:'.

CALL METHOD lo_gui->directory_browse

EXPORTING

window_title = lv_title

initial_folder = lv_folder

CHANGING

selected_folder = lv_dir.

p_file = lv_dir.

ENDFORM. " BROWSE_DIR

Please help me by replying with the code to execute it in background (if not possible, at least upload it into AL11)

Thanks !!

Former Member · ‎01-22-2015

Hi,

It's impossible to use SAP GUI functionality in background mode, because there's no GUI session in that mode.

You have to use another approach, this for example: ABAP Blog - Create XLSX/MHTML file from internal table in background

Best regards,

Nick.