Problem Copy worksheet using i_oi_document_proxy excel in place

[Former Member]

Hi,

I have a task, where I like to copy a temp plate sheet into multiple sheet.

My case is that I have a excel workbook where I am going to create one spreadsheet per country, in some case 2 pcs. other cases more than 15. I have a temp plate with a preset of text and formula for the data, therefor I like to copy the temp plate and rename the spreadsheet for each country and then insert the data.

I am using i_oi_document_proxy and i_oi_spreadsheet, the temp plate I have loaded into repository as a mime object, the process to read MIME and open excel sheet in place is working fine..

I have tried some solution, but I can not get it to work..

By the way I am using office 2007.

1. First I made it create a new spreadsheet and then try to copy the contents from temp plate to the new sheet, but the copy method on document proxy result in some internal error some thing with no_flush I have tried with and without I cannot get it to work.

2. I created a Macro in the excel document, doing the copy spreadsheet this is working fine when I use the macro, but when calling macro via proxy it result in syntax error message in excel.

-> the macro I have tried to call via script i_oi_script_collection and also by document proxy method, but same result.

Dose anyone have some idea how to do.

Best Regards

Jørgen Jensen

Gavdi

* Test example 1 - try to copy contents via clipboard
  CALL METHOD lo_spreadsheet->add_sheet
    EXPORTING
      no_flush = ' '
      name     = 'TestJJE'
    IMPORTING
      error    = lo_error
      retcode  = l_retcode. "-> return OK
  CALL METHOD lo_spreadsheet->select_sheet
    EXPORTING
      no_flush = ' '
      name     = 'TempPlate'
    IMPORTING
      error    = lo_error
      retcode  = l_retcode. "-> return OK
  CALL METHOD lc_document->select_all
    EXPORTING
      no_flush = ' ' 
   IMPORTING
      error    = lo_error
      retcode  = l_retcode. "This return internal error flush!
  CALL METHOD lc_document->paste_clipboard
    EXPORTING
      no_flush = ' '
    IMPORTING
      error    = lo_error
     retcode  = l_retcode.
* The result I get a excel open inplace with 2 spreadsheet
* -> Tempplate and TestJJE but nothing is copied!

* Test example 2.a - try to copy contents via Macro
  CALL METHOD lc_document->execute_macro
    EXPORTING
      macro_string = 'CopySheet'
*      script_name  = 'Module1'
      no_flush     = ''
    IMPORTING
      error        = lo_error
     retcode      = l_retcode.

* This is the macro in EXCEL
*Sub CopySheet()
*' CopySheet Macro
*    Sheets("TempPlate").Select
*    Sheets("TempPlate").Copy after:=Sheets(1)
*End Sub
*It is working fine if I use it in the excel in place manualy !
* The result I get a excel open inplace 
* -> But a message macro syntax error.

* Test example 2.b - try to copy contents via script Macro 
  CALL METHOD lc_document->get_script_collection
    EXPORTING
      no_flush = ''
    IMPORTING
      scripts  = l_scripts
      error    = lo_error
      retcode  = l_retcode.

  ls_source_code = 'Sub CopySheet_1()'.
  CONCATENATE ls_source_code cl_abap_char_utilities=>cr_lf
  INTO ls_source_code.
  APPEND ls_source_code
           TO lt_source_code.

  ls_source_code = 'Sheets("TempPlate").Select'.
  CONCATENATE ls_source_code cl_abap_char_utilities=>cr_lf
  INTO ls_source_code.
  APPEND ls_source_code
           TO lt_source_code.

  ls_source_code = 'Sheets("TempPlate").Copy After:=Sheets(1)'.
  CONCATENATE ls_source_code cl_abap_char_utilities=>cr_lf
  INTO ls_source_code.
  APPEND ls_source_code
           TO lt_source_code.

  ls_source_code = 'End Sub'.
  CONCATENATE ls_source_code cl_abap_char_utilities=>cr_lf
  INTO ls_source_code.
  APPEND ls_source_code
           TO lt_source_code.

  CALL METHOD l_scripts->add_script_from_table
    EXPORTING
      no_flush     = space
      script_name  = 'CopySheet'
      script_type  = l_scripts->script_type_vbscript
      script_table = lt_source_code
   IMPORTING
      retcode      = lsz_return_code.

  CALL METHOD lc_document->execute_macro
    EXPORTING
      macro_string = 'CopySheet_1'
      script_name  = 'CopySheet'
      no_flush     = space
    IMPORTING
      retcode      = lsz_return_code.

* The result I get a excel open inplace 
* -> But a message macro syntax error same as 2.a

Edited by: Jørgen Jensen on May 12, 2011 8:34 PM

[Former Member]

JJ,

Not sure I follow all of this - but could you not place the Macro logic into the actual spreadsheet image - and then call the Macro instead of building the Macro in ABAP?

[Former Member]

JJ,

Not sure I follow all of this - but could you not place the Macro logic into the actual spreadsheet image - and then call the Macro instead of building the Macro in ABAP?

[Former Member]

Hi,

That is the case i tried also in 2.a I build the macro in the temp plate, this is working fine when running the macro manual, but when try to run the macro via execute_macro method it result in syntax error message in excel document.

Jørgen Jensen

[Former Member]

What is the Excel error?

[Former Member]

Hi John,

I found the error was not actual from EXCEL, but deep in from the Proxy service return error 118.

In the mean time I found the problem 🐵

I have by mistake typed in macro wrong !

CALL METHOD lc_document->execute_macro
    EXPORTING
*      macro_string = 'CopySheet.Module1' -> Wrong call!
      macro_string = 'Module1.CopySheet'
*      script_name  = ''
      no_flush     = ''
    IMPORTING
      error        = lo_error
      retcode      = l_retcode.

Thanks for your help my code are working fine now 🐵

Jørgen Jensen

[Former Member]

Excellent!!

Have a great weekend.