Need help with Desktop Office Integration (DOI)

[Former Member]

Hi all,

i need help to read an Excelsheet into an int. table.

Its the first time, that i use the SAP DOI. I copy different coding into my Report to get connection to an existing Excelsheet.

Here is my Coding:

* first get the SAP DOI i_oi_container_control interface
  CALL METHOD c_oi_container_control_creator=>get_container_control
                    IMPORTING control = gr_control
                              error   = gr_errors.
  APPEND gr_errors.

* create a control container as defined in dynpro 100
  CREATE OBJECT gr_container
            EXPORTING container_name = 'CONTAINER'.

* initialize the SAP DOI Container, tell it to run in the container
* specified above and tell it to run Excel in-place
  CALL METHOD gr_control->init_control
    EXPORTING
      r3_application_name      =    'Data'
      inplace_enabled          = ' '
      inplace_scroll_documents = 'X'
      parent                   = gr_container
      register_on_close_event  = 'X'
      register_on_custom_event = 'X'
      no_flush                 = 'X'
    IMPORTING
      error                    = gr_errors.
* save error object in collection
  APPEND gr_errors.

* ask the SAP DOI container for a i_oi_document_proxy for Excel
  CALL METHOD gr_control->get_document_proxy
                       EXPORTING document_type = 'Excel.Sheet'
*                       EXPORTING document_type = 'Word.Document'
                                no_flush = 'X'
*                                REGISTER_CONTAINER = 'X'
                      IMPORTING document_proxy = gr_document
                                error          = gr_errors.
  APPEND gr_errors.

Then i open the Document from lokal PC.

CALL METHOD gr_document->open_document
      EXPORTING
*      document_title   = ld_filenc
        document_url     = ld_verzc
*      NO_FLUSH         = ' '
*      OPEN_INPLACE     = ' '
*      open_readonly    = ' '
*      PROTECT_DOCUMENT = ' '
*      STARTUP_MACRO    = ''
*      USER_INFO        =
*      ONSAVE_MACRO     =
      IMPORTING
        error            = gr_errors
*      RETCODE          =
        .
  APPEND gr_errors.

Now i start the spreadsheet interface:

*...check if our document proxy can serve a spreadsheet interface  data:
  DATA: pd_has TYPE i.
  CALL METHOD gr_document->has_spreadsheet_interface
                    EXPORTING no_flush = 'X'
                    IMPORTING is_available = pd_has
                              error = gr_errors.
  APPEND gr_errors.
  CALL METHOD gr_document->get_spreadsheet_interface
                    EXPORTING no_flush = ' '
                    IMPORTING
                              sheet_interface = gr_spreadsheet
                              error = gr_errors.
  APPEND gr_errors.

* now loop through error collection because
* Get_spreadsheet_interface flushed and synchronized
* the automation queue !
  LOOP AT gr_errors.
    CALL METHOD gr_errors->raise_message
                    EXPORTING  type     = 'I'
                    EXCEPTIONS message_raised = 1
                               OTHERS         = 2.

    IF sy-subrc = 1.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      pd_kz_fehler = 'X'.
      EXIT.
    ENDIF.

  ENDLOOP.
  FREE gr_errors.

Ok, now i can open a Excelsheet, and i can mark a range in the sheet

rows is a constant.

CALL METHOD gr_spreadsheet->set_selection
      EXPORTING
        left     = 1
        top      = 2
        rows     = rows
        columns  = 18
*    NO_FLUSH = ' '
*    UPDATING = -1
    IMPORTING
      error    = gr_errors
*    RETCODE  =
        .

my first problem: Excel is really open, and the user can see the sheet. I dont want, that excel is visible...is there a way to start excel in no_visible mode?

second problem: The sheet have makro aktiv...at start from excel, there is a popup, which ask 'makros activate' oder not activate... i dont want this popup... is there a way, to say it from abap, that makros always active?

third problem: i see, that the content Table have this components:

TYPES: BEGIN OF SOI_GENERIC_ITEM,

ROW(4) TYPE C,

COLUMN(4) TYPE C,

VALUE(256) TYPE C,

END OF SOI_GENERIC_ITEM.

But my excelsheet have more then 10000 lines....

forth (and biggest) problem: i need the selected data into an int. tabelle. The table have the components:

ROW

COL

VALUE

i dont know, how can i do this...

please help me

Sorry for my bad english.

Greetings

Markus

[Former Member]

Hi,

May be this link is useful

/people/thomas.jung3/blog/2005/05/11/using-classic-activex-controls-in-the-abap-control-framework

Also Check out report SAPRDEMOEXCELINTEGRATION2.

check the links

http://www.esnips.com/doc/741a848e-f49a-4436-bec4-e21950f6c94c/desktop-office-integration.pdf

http://www.esnips.com/doc/2080a9ec-64f9-49c4-bd03-d9f56bc2437c/MSWord--Excel-with-ABAP.pdf

Regards,

Raj.

[Former Member]

Hello Raj,

thanks for your answer. But i cant solve my problems, with this links. I have not found any information, how i get Data from an exist Excelfile into an int. Table.

I dont want create an ExcelFile...i will open an exist Excelfile and get the Data from it into an int. Table.

With the Coding from my first Post, i can open an exist Excelfile. I can mark the data range, but i cant get the Data into an int. Table

What can i do? What have i to do?

Need help!

Thanks for any help.

Regards

Markus

[michaelmerny]

Hello,

If you don't want to see Excel during the process, you have to set the flag inplace_enabled to 'X'

CALL METHOD gr_control->init_control

EXPORTING

r3_application_name = 'Data'

inplace_enabled = ' '

inplace_scroll_documents = 'X'

parent = gr_container

register_on_close_event = 'X'

register_on_custom_event = 'X'

no_flush = 'X'

IMPORTING

error = gr_errors.

• save error object in collection

APPEND gr_errors.

[Former Member]

Hello Michael,

thank you for your answer, but Excel still start in 'visible mode'.

Can you assist me with the question...how can i read the data from an exist Excelfile into an internal table with DOI?

I posted my question in different Forums...but no one can help my. Maybe i posted my question in wrong Forum (bulletin board).

Thanks

regards

Markus

Edited by: Markus Wendel on Apr 22, 2008 1:47 PM

[michaelmerny]

Markus,

I don't know if it's possible to do it with DOI.

I have used DOI to create Excel document with data from SAP.

The solution I could see for your problem is:

- Save your Excel document in formated text (.prn)

- Transfer this new file to SAP

- Write a program to read this file and save the data in SAP.

Maybe it's not the best solution, but I did it like this in the past without any problem.

Regards,

Michaël.

[Former Member]

Hi Michael,

thanks for your answer. I have just tested to save my excel file into an *.prn file. But the result is not usable, maybe the Excelfile is to big. Over the half data are missing...

There are any other possibilities, to read data from an exist excelfile into a internal table?

Thx for help.

Regards

Markus

[michaelmerny]

Markus,

If you also set the flag OPEN_INPLACE for CALL METHOD gr_document->open_document, do you still have the problem ?

Regards,

Michaël.

[Former Member]

That´s it, Michael

Excel are 'blind'

Now i only have to get data from Excelsheet

Thx

Regards

Markus

[Former Member]

The last time I used DOI extensively was about 4 years ago, under 4.0B, when you had to get handles to the workbook, worksheet, and then each cell and invoke "GET PROPERTY OF LH_CELL_01 'VALUE' = L_VALUE_01" to read the content of a cell, so I'll have to read up on the newer DOI methods... but do you actually need to use DOI for your requirement?... I've seen other postings that have suggested using function TEXT_CONVERT_XLS_TO_SAP for reading in simple sheets from Excel, but I've not tried that myself... if nothing else, the code in the function may give you some pointers as to how to do the "get data" you want.

Jonathan

[michaelmerny]

Markus,

I have never used it, but you can have a look in function group SFES --> there is a function called GUI_UPLOAD (File transfer from frontend PC to an internal backend table).

Maybe it could help !

Regards,

Michaël.

[Former Member]

I've now had a look in an ECC system, and the demo program SAPRDEMO_SPREADSHEET_INTERFACE probably has the answers you need (see form getdata). But that function module I mentioned above TEXT_CONVERT_XLS_TO_SAP may be easier for you - it uses the spreadsheet interface and packages up all the opening and reading for you.

Jonathan

[Former Member]

I check this actually. When i have a result, i will post here my solution.

regards

Markus

[former_member775662]

hi

Did You solve it? I met the same problem as you