1. OVERVIEW
Integrate Microsoft Office documents in SAP is not a common requirement but as Microsoft Suite has a wide implementation in business processes in some occasions we need to work with Microsoft Office documents in SAP.
We can work with SAP BDS (BC-SRV-BDS) as a repository and read the template from there to load in our program. But in this document I’m going to show how you can open an Excel document from your Local PC in a SAP control inplace.
2. TECHNICAL SPECIFICATIONS
I guess this development can be executed with many SAP GUI versions and Microsoft Office but you should check.
In my case:
SAP GUI: SAP Logon 720 (7200.3.11.1074)
SAP System: SAP ECC 6.0
Microsoft Excel: 14.0.6106.5005 (32-bit)
3. SCREEN SHOTS
Upload, save and activate program ZALF_EXCEL_INPLACE_LOCAL_PC (see code in section 4. CODE)
Execute program
Select Excel document from you Local PC
Excel document is opened inplace.
4. CODE
Important:
- Create Dynpro 100 with a control inside called: ‘CTR_EXCEL’
- Create PF-STATUS ‘100’, just with ‘EXIT’ function
- Create TITLEBAR ‘100’ with some description
*&———————————————————————*
*& Report ZALF_EXCEL_INPLACE_LOCAL_PC
*&
*&———————————————————————*
*& This program shows how to open an Excel file from your Local PC in
*& an “inplace” control
*&———————————————————————*
REPORT zalf_excel_inplace_local_pc.
* Global definitions
DATA:
* OKCODE
gv_okcode TYPE syucomm,
* SAP Desktop Office Integration Interfaces
gc_container TYPE REF TO cl_gui_custom_container,
gc_control TYPE REF TO i_oi_container_control,
gc_document TYPE REF TO i_oi_document_proxy,
gc_spreadsheet TYPE REF TO i_oi_spreadsheet,
gc_error TYPE REF TO i_oi_error,
gt_errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE,
gv_initialized TYPE c.
* Start with the screen
SET SCREEN 100.
*&———————————————————————*
*& Module STATUS_0100 OUTPUT
*&———————————————————————*
* Status
*———————————————————————-*
MODULE status_0100 OUTPUT.
SET PF-STATUS ‘100’.
SET TITLEBAR ‘100’.
ENDMODULE. ” STATUS_0100 OUTPUT
*&———————————————————————*
*& Module EXIT INPUT
*&———————————————————————*
* Exit program
*———————————————————————-*
MODULE exit INPUT.
CASE gv_okcode.
WHEN ‘EXIT’.
LEAVE PROGRAM.
ENDCASE.
ENDMODULE. ” EXIT INPUT
*&———————————————————————*
*& Module CREATE_OBJECTS OUTPUT
*&———————————————————————*
* Create Objects
*———————————————————————-*
MODULE create_objects OUTPUT.
* Local definitions
TYPES:
t_row(2048) TYPE x. “long byte string
DATA:
* Structure and table to retreive file from Local PC
lw_file_table TYPE file_table,
lt_file_table TYPE filetable,
* Variable for local PC file name
lv_filename TYPE string,
* Table to upload file
lt_upload TYPE STANDARD TABLE OF t_row,
* File length
lv_filelength TYPE i,
* Return code
lv_rc TYPE i,
* Return from Method open_document_from_table
lf_retcode TYPE soi_ret_string,
* Spreadsheet available
lv_spread_available TYPE i.
* First open a dialog window to select file from Local PC
REFRESH lt_file_table.
CLEAR lv_rc.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = ‘Open’
* DEFAULT_EXTENSION =
default_filename = ‘*.xls*’
file_filter = ‘*.xls*’
* WITH_ENCODING =
* INITIAL_DIRECTORY =
* MULTISELECTION =
CHANGING
file_table = lt_file_table
rc = lv_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.
* Implement suitable error handling here
LEAVE PROGRAM.
ENDIF.
* Get the SAP DOI interface references.
* This work has just to be done once !
CHECK gv_initialized IS INITIAL.
* First get the SAP DOI i_oi_container_control interface
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = gc_control
error = gc_error.
* Check no errors occured
CALL METHOD gc_error->raise_message
EXPORTING
type = ‘E’.
* Create a control container as defined in dynpro 100
CREATE OBJECT gc_container
EXPORTING
container_name = ‘CTR_EXCEL’.
* Initialize the SAP DOI Container, tell it to run in the container
* specified above and tell it to run Excel in-place
CALL METHOD gc_control->init_control
EXPORTING
r3_application_name = ‘R/3 Basis’ “#EC NOTEXT
inplace_enabled = ‘X’
inplace_scroll_documents = ‘X’
parent = gc_container
register_on_close_event = ‘X’
register_on_custom_event = ‘X’
no_flush = ‘X’
IMPORTING
error = gt_errors.
* Save error object in collection
APPEND gt_errors.
* Ask the SAP DOI container for a i_oi_document_proxy for Excel
CALL METHOD gc_control->get_document_proxy
EXPORTING
document_type = ‘Excel.Sheet’
no_flush = ‘X’
* REGISTER_CONTAINER = ‘X’
IMPORTING
document_proxy = gc_document
error = gt_errors.
* Save error object in collection
APPEND gt_errors.
* Upload file from Local PC to SAP
* Read file name user has choosen
CLEAR: lw_file_table, lv_filename.
READ TABLE lt_file_table INTO lw_file_table INDEX 1.
lv_filename = lw_file_table–filename.
REFRESH lt_upload.
CLEAR lv_filelength.
CALL METHOD cl_gui_frontend_services=>gui_upload
EXPORTING
filename = lv_filename
filetype = ‘BIN’
* HAS_FIELD_SEPARATOR = SPACE
* HEADER_LENGTH = 0
* READ_BY_LINE = ‘X’
* DAT_MODE = SPACE
* CODEPAGE = SPACE
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = ‘#’
* VIRUS_SCAN_PROFILE =
IMPORTING
filelength = lv_filelength
* HEADER =
CHANGING
data_tab = lt_upload
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
not_supported_by_gui = 17
error_no_gui = 18
OTHERS = 19.
IF sy–subrc <> 0.
* Implement suitable error handling here
LEAVE PROGRAM.
ENDIF.
* Now use Method OPEN_DOCUMENT_FROM_TABLE
CLEAR lf_retcode.
CALL METHOD gc_document->open_document_from_table
EXPORTING
document_size = lv_filelength
document_table = lt_upload
document_title = ‘Excel inplace’
no_flush = ‘X’
open_inplace = ‘X’
* OPEN_READONLY = ‘ ‘
* PROTECT_DOCUMENT = ‘ ‘
* ONSAVE_MACRO = ‘ ‘
* STARTUP_MACRO = ”
IMPORTING
error = gt_errors
retcode = lf_retcode.
APPEND gt_errors.
* Check if our document proxy can serve a spreadsheet interface data:
CLEAR lv_spread_available.
CALL METHOD gc_document->has_spreadsheet_interface
EXPORTING
no_flush = ‘X’
IMPORTING
is_available = lv_spread_available
error = gt_errors.
APPEND gt_errors.
CALL METHOD gc_document->get_spreadsheet_interface
EXPORTING
no_flush = ‘ ‘
IMPORTING
sheet_interface = gc_spreadsheet
error = gt_errors.
* Save error object in collection
APPEND gt_errors.
LOOP AT gt_errors.
CALL METHOD gt_errors->raise_message
EXPORTING
type = ‘E’.
ENDLOOP.
FREE gt_errors.
gv_initialized = ‘X’.
ENDMODULE. ” CREATE_OBJECTS OUTPUT
Its’fine !
Thank you.
Could this technique be used to serve a copy of the current-released Excel file to users?
Rather than placing the newer copy on a shared drive for users to find.
Working in a medical device company.
Required to have all excel files, used to make quality decision, shall be validated.
When Document Control releases updates in SAP, to the Excel Template, want users to stop using old copies and start using new versions.
Hi Mark,
I had the same requirement, we had to confirm a central Excel file for all users. I solved this using Business Document Service (BDS), basically transaction OAOR.
I stored the central Excel file in BDS and SAP transaction retrieved this in a similar way I’ve explained in my post but instead to upload from local PC the Excel file was uploaded from BDS.
In my case we had to check Excel data with SAP: WBS codes, currencies, cost center, etc
In order to get/set data between SAP an Excel file I used interface i_oi_spreadsheet with methods set_ranges_data, get_ranges_data…
I managed an internal version of Excel document saving in a protected cell the current version number. An additional requirement was give the option to the users to save in local PC the file they were working on and later on have the option to upload in SAP from local PC and continue working with the file. Comparing the version of BDS file and user file we were able to confirm that version file was OK.
I hope this help you.
Regards,
Alfonso Rodriguez
SAP ABAP Consultant
Hi Alfonso,
I had a question about integrating Macros in this report,
I try to call the macro with the execute_macro method but it is not working, maybe the place where I put it is the problem or I have to write extra code,
the macro exists in the excel file I just want to execute it
do you have any idea regarding this matter
Regards,
Dren
Hi Dren,
I think you should have to do it without problems.
The method belongs to the proxy no to the spreadsheet object.
Check method I_OI_DOCUMENT_PROXY->EXECUTE_MACRO
in my example would be a method of object gc_document.
Kind regards,
Alfonso.
Hi Alfonso,
Yes I am using that but I have the problem that when I pass parameters to the Macro I dont know how to ‘accept’ the parameters in my Macro
Regards,
Dren
Hi Dren,
Sorry, I don’t catch you when you say ‘accept’ parameters in my Macro…
If you call method I_OI_DOCUMENT_PROXY->EXECUTE_MACRO and the macro in Excel is triggered I have to think that the problem must be in Macro code… I’m not expert in Macro develop… what I would do is simplify first the Macro just populating the parameters sent from SAP using MsgBoX (parameter) if parameters arrive to the Macro code the “problem” must be in Macro code itself.
Good luck.
Alfonso.
thank u so much.
hello is it possible to open an excel focusing on a specific row? i though i could manage it with the method show_rows but i couldn’t .
Regards