Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

DOI and Script collection/Macro in Word

Former Member
0 Kudos

Hi experts,

My goal is to upload macro from file and after upload run this macro inside word document.

I try to upload macro from file in Script collection using get_script_collection(class I_OI_DOCUMENT_PROXY) & add_script (class I_OI_SCRIPT_COLLECTION)

After that I want to execute macro using execute_script(class I_OI_DOCUMENT_PROXY) .

I always get error when executing macro that some object is missing. Probably macro is not correct written in file.

Can somebody give me example hove this macro must be written so I can execute him.

Thanks a lot,

Drazen

9 REPLIES 9

Former Member
0 Kudos

Hi Drazen Lovrencic ,

Even I am trying to execute the macro but facing problem. Below is the my code of adding vb script(macro) to the excel spreadsheet and executing it. but stil not working. I am getting error message "Cannot initialize control"

TYPES:

TS_SOURCE_CODE TYPE TEXT1024,

TT_SOURCE_CODE TYPE STANDARD TABLE OF TS_SOURCE_CODE WITH DEFAULT KEY.

TYPES: BEGIN OF TY_SCRIPT,

VALUE(80) TYPE C,

END OF TY_SCRIPT.

DATA: LS_SOURCE_CODE TYPE TY_SCRIPT,"TS_SOURCE_CODE,

LT_SOURCE_CODE TYPE TABLE OF TY_SCRIPT,

L_RETURN_CODE TYPE SOI_RET_STRING.

  • Building the Script - every line has to end with cr/lf

LS_SOURCE_CODE-VALUE = 'Sub Macro1()'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'ActiveSheet.Buttons.Add(234, 57, 109.5, 29.25).Select'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'Selection.OnAction = "button"'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'Range("G9").Select'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'ActiveSheet.PageSetup.PrintArea = ""'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'With ActiveSheet.PageSetup'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = '.Orientation = xlLandscape'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = '.PaperSize = xlPaperA4'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = '.FitToPagesWide = 1'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = '.FitToPagesTall = 1'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'End With'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

LS_SOURCE_CODE-VALUE = 'End Sub'.

CONCATENATE LS_SOURCE_CODE-VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF

INTO LS_SOURCE_CODE-VALUE.

APPEND LS_SOURCE_CODE

TO LT_SOURCE_CODE.

BREAK-POINT.

DATA: L_SCRIPT_COLLECTION TYPE REF TO i_oi_script_collection.

DATA: table_url(256).

DATA: DOC_COOKIE TYPE I.

L_RETURN_CODE = c_oi_errors=>ret_document_not_open.

CALL METHOD c_oi_errors=>create_error_for_retcode

EXPORTING

retcode = L_RETURN_CODE

no_flush = ''

IMPORTING

error = gc_error.

CALL FUNCTION 'DP_CREATE_URL'

EXPORTING

type = ''

subtype = ''

TABLES

data = LT_SOURCE_CODE

CHANGING

url = table_url.

DATA: CONTAINER_CONTROL TYPE REF TO C_OI_CONTAINER_CONTROL_PROXY.

CREATE OBJECT CONTAINER_CONTROL.

CALL METHOD container_control->add_document_script

EXPORTING

NO_FLUSH = 'X'

document_cookie = doc_cookie

script_name = 'Macro'

script_type = '1'

script_url = table_url

IMPORTING

error = gc_error.

APPEND gc_error TO gt_error_table.

    • Calling the script:

CALL METHOD GC_DOCUMENT->EXECUTE_MACRO

EXPORTING

MACRO_STRING = 'Macro_demo_1'

SCRIPT_NAME = 'Macro'

NO_FLUSH = SPACE

IMPORTING error = gc_error.

APPEND gc_error TO gt_error_table.

0 Kudos

Hi Shreyansh,

Same problem i am facing from last one week i am trying to create graph after downloading data in Excel, Data has been dowloaded successfully but when i am using function add_script_from_table error coming that script contains error, Not getting any help from anywhere, Please if u will solve your problem then pease guide me....

Regards

Shelly Malik

0 Kudos

Hi Shelly,

Kindly refere SAP Note: 764371, hope this helps you..please let me know if you get the solution. For me still not working.

Reason and Prerequisites

Reason:-

The structure of a internal table is not defined and when we use the

dataprovider for transfering a iternal table (script) it fails.

Prerequisites:-

When you call add_script_from_table of i_oi_script_collection use the

below given structure and defined table. Fill the table with the script

and call add_script_from_table with the same.

TYPES: BEGIN OF SCRIPT_ITEM,

VALUE(80) TYPE C,

END OF SCRIPT_ITEM.

TYPES: SCRIPT_TABLE TYPE TABLE OF SCRIPT_ITEM.

NOTE: When adding a statement for script in a table it's required to

add a new line character after each statement.

0 Kudos

Hi Shelly,

Could you please tel me how you are passing the MACRO_STRING to EXECUTE_MACRO method.

Regards,

Shreyansh

Former Member
0 Kudos

Hi Drazen,

Could you please tel me how you are passing the MACRO_STRING to EXECUTE_MACRO method.

Regards,

Shreyansh

0 Kudos

Hi Shreyansh,

I am also still not able to pass macros but attaching my code here:

**      STEP 9: Prepare  Macros
* Build & execute macro to set the color for the text
*  l_rows = l_rows + 1.
*  SHIFT l_rows LEFT DELETING LEADING space.
*  l_newline = cl_abap_char_utilities=>cr_lf.

* Get Macro Collections Object
  CALL METHOD go_document_proxy->get_script_collection
    IMPORTING
      error   = lw_error_object-error_obj
      scripts = lo_scripts.

  APPEND lw_error_object TO gt_error_objects.
  CLEAR lw_error_object.


  CONCATENATE 'Sub Macro1 ()' cl_abap_char_utilities=>cr_lf
         INTO lw_script-value.
  APPEND lw_script TO lt_script.
  CLEAR lw_script.


  concatenate
  'With ActiveSheet.ChartObjects.Add _'
  cl_abap_char_utilities=>cr_lf
     into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.

  concatenate
   '(Left:=100, Width:=375, Top:=75, Height:=225)'
    cl_abap_char_utilities=>cr_lf
       into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.

  concatenate
   '.Chart.SetSourceData Source:=Sheets("Download").Range("A1:D14")'
    cl_abap_char_utilities=>cr_lf
       into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.
*
  concatenate
   '.Chart.ChartType = xlXYScatterLines'
    cl_abap_char_utilities=>cr_lf
       into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.


  concatenate 'End with' cl_abap_char_utilities=>cr_lf
   into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.

  CONCATENATE 'End Sub' cl_abap_char_utilities=>cr_lf
   INTO lw_script-value.
  APPEND lw_script TO lt_script.
  CLEAR lw_script.

  CALL METHOD lo_scripts->add_script_from_table "Getting Error scipt contains error after execution of this method
    EXPORTING
      no_flush     = c_true
      script_name  = 'Macro1_sub'
      script_table = lt_script[]
      script_type  = '1' "VB Scrript
    IMPORTING
      error        = lw_error_object-error_obj.

  APPEND lw_error_object TO gt_error_objects.
  CLEAR lw_error_object.

  CALL METHOD go_document_proxy->execute_macro
    EXPORTING
      macro_string = 'Macro1'
      script_name  = 'Macro1_sub'
    IMPORTING
      error        = lw_error_object-error_obj.
  APPEND lw_error_object TO gt_error_objects.

  LOOP AT gt_error_objects INTO lw_error_object.
    CALL METHOD lw_error_object-error_obj->raise_message
      EXPORTING
        type = c_error.
  ENDLOOP.

  REFRESH gt_error_objects[].

Hope it helps and please post soultion, If found ant way to execute macros.

0 Kudos

Hi Shelly..

Have you created your WA & LT as below.? if not kindly try and let me know.

  • Please check the SAP NOTE which i have mentioned in previous remark.

TYPES: BEGIN OF TY_SCRIPT,

VALUE(80) TYPE C,

END OF TY_SCRIPT.

DATA:LT_SCRIPT TYPE TABLE OF TY_SCRIPT,

LW_SCRIPT TYPE TY_SCRIPT.

ADD_SCRIPT_FROM_TABLE is working fine for me with above TYPE definition.

Still I am struck in executing MACRO. Not succeeded. I suspect there might be something problem in creating MODULE or Adding Macro to spreadsheet. Because, after I execute the report, i am getting report output in excel but could not find any macros attached to the sheet in developer tab(excel).

Please help me if u can execute macro successfully after you adding script to sheet..

Thanks in advance.

0 Kudos

Hi shreyansh,

I have already given same type for lt_scrip and lw_script, now i have coded ur coding part and now i am getting error after execution of follwing code giving error message Document not opened.

L_RETURN_CODE = c_oi_errors=>ret_document_not_open.
  CALL METHOD c_oi_errors=>create_error_for_retcode
    EXPORTING
      retcode  = L_RETURN_CODE
      no_flush = ''
    IMPORTING
      error    = lw_error_object-error_obj.

  CALL METHOD lw_error_object-error_obj->raise_message
    EXPORTING
      type = c_error.

rest i have done coding like this still getting same error Script contains error:

BEGIN OF ty_script_item,
  value(80)  TYPE c,
END OF ty_script_item,

CALL METHOD go_document_proxy->get_script_collection
    IMPORTING
      error   = lw_error_object-error_obj
      scripts = lo_scripts.

  APPEND lw_error_object TO gt_error_objects.
  CLEAR lw_error_object.


  CONCATENATE 'Sub Macro1 ()' cl_abap_char_utilities=>cr_lf
         INTO lw_script-value.
  APPEND lw_script TO lt_script.
  CLEAR lw_script.


  concatenate
  'With ActiveSheet.ChartObjects.Add _'
  cl_abap_char_utilities=>cr_lf
     into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.

  concatenate
   '(Left:=100, Width:=375, Top:=75, Height:=225)'
    cl_abap_char_utilities=>cr_lf
       into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.

  concatenate
   '.Chart.SetSourceData Source:=Sheets("Download").Range("A1:D14")'
    cl_abap_char_utilities=>cr_lf
       into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.
*
  concatenate
   '.Chart.ChartType = xlXYScatterLines'
    cl_abap_char_utilities=>cr_lf
       into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.


  concatenate 'End with' cl_abap_char_utilities=>cr_lf
   into lw_script-value.
  append lw_script to lt_script.
  clear lw_script.

  CONCATENATE 'End Sub' cl_abap_char_utilities=>cr_lf
   INTO lw_script-value.
  APPEND lw_script TO lt_script.
  CLEAR lw_script.

*********************************************************
  L_RETURN_CODE = c_oi_errors=>ret_document_not_open.
  CALL METHOD c_oi_errors=>create_error_for_retcode
    EXPORTING
      retcode  = L_RETURN_CODE
      no_flush = ''
    IMPORTING
      error    = lw_error_object-error_obj.

  CALL METHOD lw_error_object-error_obj->raise_message
    EXPORTING
      type = c_error.

  CALL FUNCTION 'DP_CREATE_URL'
    EXPORTING
      type    = ''
      subtype = ''
    TABLES
      data    = lt_script[]
    CHANGING
      url     = table_url.

  DATA: CONTAINER_CONTROL TYPE REF TO C_OI_CONTAINER_CONTROL_PROXY.

  CREATE OBJECT CONTAINER_CONTROL.
  CALL METHOD container_control->add_document_script
    EXPORTING
      NO_FLUSH        = ''
      document_cookie = doc_cookie
      script_name     = 'Macro'
      script_type     = '1'
      script_url      = table_url
    IMPORTING
      error           = lw_error_object-error_obj.

  CALL METHOD lw_error_object-error_obj->raise_message
    EXPORTING
      type = c_error.

******************************************************
*  CALL METHOD lo_scripts->add_script_from_table
*    EXPORTING
*      no_flush     = c_true
*      script_name  = 'Macro1_sub'
*      script_table = lt_script[]
*      script_type  = lo_scripts->SCRIPT_TYPE_VBSCRIPT "'1' "VB Scrript
*    IMPORTING
*      error        = lw_error_object-error_obj.
*
*  APPEND lw_error_object TO gt_error_objects.
*  CLEAR lw_error_object.

  CALL METHOD go_document_proxy->execute_macro
    EXPORTING
      macro_string = 'Macro1'
      script_name  = 'Macro'
    IMPORTING
      error        = lw_error_object-error_obj.
  APPEND lw_error_object TO gt_error_objects.

  LOOP AT gt_error_objects INTO lw_error_object.
    CALL METHOD lw_error_object-error_obj->raise_message
      EXPORTING
        type = c_error.
  ENDLOOP.

  REFRESH gt_error_objects[].

0 Kudos

Hi Shelly,

Kindly check below code which helps you to create the document. have you initatilised the container control and created spreadsheet ?..cross check with below code and let me know.

  • Classes and data for presentation with office integration

DATA: GC_SPLITTER TYPE REF TO CL_GUI_SPLITTER_CONTAINER,

GC_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,

GC_CONTAINER_1 TYPE REF TO CL_GUI_CONTAINER,

GC_CONTAINER_2 TYPE REF TO CL_GUI_CONTAINER,

GC_TREE TYPE REF TO CL_GUI_SIMPLE_TREE,

GC_CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL,

GC_SPREADSHEET TYPE REF TO I_OI_SPREADSHEET,

GC_DOCUMENT TYPE REF TO I_OI_DOCUMENT_PROXY,

GF_DOCUMENT_TYPE TYPE SOI_DOCUMENT_TYPE VALUE SOI_DOCTYPE_EXCEL_SHEET,

GF_DOCUMENT_FORMAT TYPE SOI_DOCUMENT_TYPE,

GC_ERROR TYPE REF TO I_OI_ERROR,

GT_ERROR_TABLE TYPE TABLE OF REF TO I_OI_ERROR,

GF_SI_AVAILABLE TYPE I.

  • 1. Create Control-Instance

c_oi_container_control_creator=>get_container_control(

IMPORTING

control = gc_control

error = gc_error

).

APPEND gc_error TO gt_error_table.

  • 2. Initialization

gc_control->init_control(

EXPORTING

inplace_enabled = 'X'

inplace_scroll_documents = 'X'

no_flush = 'X'

r3_application_name = 'TEST DOCUMENT'

parent = gc_container_2

IMPORTING

error = gc_error

EXCEPTIONS

javabeannotsupported = 1

OTHERS = 2

).

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE 'A' NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

APPEND gc_error TO gt_error_table.

  • 3. Create the document

gc_control->get_document_proxy(

EXPORTING

document_type = gf_document_type

no_flush = 'X'

IMPORTING

document_proxy = gc_document

error = gc_error

).

APPEND gc_error TO gt_error_table.

gc_document->create_document(

EXPORTING

document_title = 'TEST DOCUMENT TITLE'

no_flush = 'X'

open_inplace = 'X'

ONSAVE_MACRO = 'Module1.Macro1'

IMPORTING

error = gc_error

).

APPEND gc_error TO gt_error_table.

  • 4. Check and get spreadsheet

gc_document->has_spreadsheet_interface(

EXPORTING

no_flush = 'X'

IMPORTING

error = gc_error

is_available = gf_si_available

).

APPEND gc_error TO gt_error_table.

gc_document->get_spreadsheet_interface(

EXPORTING

no_flush = ' '

IMPORTING

error = gc_error

sheet_interface = gc_spreadsheet

).

APPEND gc_error TO gt_error_table.