topic Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO in Application Development and Automation Discussions

Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Wed, 17 May 2006 14:02:51 GMT

I have an application that creates an Excel Inplace workbook with multiple worksheets. It is done with the Office Intergration (OI) classes/interfaces. I want to add functionality to CHART the worksheets within the InPlace session.

My plan was to populate an internal table with VB Script commands to create the charts. I would use the method ADD_SCRIPT_FROM_TABLE in I_OI_SCRIPT_COLLECTION to add the script, and then method EXECUTE_MACRO in I_OI_DOCUMENT_PROXY to run the script/macro.

It does not work. Right now, I am using simple commands in the script. I am just selecting a range and setting it to ITALICS. I have tried different variations of the commands. Either the script won't load (SCRIPT CONTAINS ERRORS) or I get sytnax errors when the EXECUTE_MACRO method is called.

I cannot find any programs in SAP that use the ADD_SCRIPT_FROM_TABLE method. Cannot find any examples on SDN. Cannot find any examples on the internet.

I do not want to use the OLE commands. The workbook needs to remain open in Excel InPlace and I am already using the OI interfaces.

Any ideas?

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Wed, 17 May 2006 19:15:21 GMT

Where are all of the replies?

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Wed, 17 May 2006 19:46:29 GMT

I have more info. When I use the ADD_SCRIPT_FROM_TABLE method, an internal table of VBscript commands is passed.

I just tried using only one command in the script. It works. I built three separate scripts with one command each. This works too.

However, I need to pass a complicated script that contains more than one line.

example-

In two steps, it works.

Sub Sales1()

Dim objExcel

End Sub

Sub Sales2()

ChartType = "Pie"

End Sub

The same code in one script does not work-

Sub Sales1()

Dim objExcel

ChartType = "Pie"

End Sub

In VBscript, the end of line signifies the end of the command. It almost looks like the method is not sure where each command ends. I tried adding a CR or LF to the end of each line. It did not work.

Still looking for ideas, an example, or documentation.

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Fri, 15 Sep 2006 09:04:27 GMT

can you please post the way you append your script to the script table?

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

monasap08 — Tue, 17 Jun 2008 08:56:49 GMT

Hi Norman,

Can you please post the example for one script line code on this thread.

We have been struggling to do this. Have tried all possible combination but no luck.

Please post your code.

Thanks in advance

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

andreas_schreyer — Thu, 04 Jun 2009 12:41:43 GMT

Hi everyone,

I have the same problem when trying to add a script to a Word document. It is no difference if I add one line or several lines to the table, I always get the same error message SOFFICEINTEGRATION 118 meaning "script contains errors".

What I am trying to do is adding a mergefield with the following line:

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField, _

Text:="MERGEFIELD FIELDNAME", _

PreserveFormatting:=True

Unfortunately the interface I_OI_SCRIPT_COLLECTION has no documentation, the "documentation" in online help is not very useful.

Has anyone any clues?

Thanks in advance

Andreas

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

103343 — Tue, 25 Aug 2009 13:25:17 GMT

inspite of no one ever got any answer in this thread, I would like to give it a new try.

I observe the same phenomenons that norman described. When I use ADD_SCRIPT_FROM_TABLE an give only three lines of code I do not get any error. If I give more lines, I get message SOFFICEINTEGRATION 118.

But although the three lines example does not give an error, there is no makro in the excel-sheet at the end. So I doubt, that this method works as desired.

Did anyone of you by now made a go of this method? Or perhaps found another way to put an macro to excel?

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Tue, 29 Sep 2009 12:55:51 GMT

Hi,

after some days trying different approaches, i've finally got a working example.

The following code shows inserting and the calling of one small vba script with one subroutine (okadis_demo_1).

types: 
  ts_source_code  type text1024,
  tt_source_code   type standard table of ts_source_code with default key.

form insert_and_call_macro
using   pptr_script_collection type ref to i_oi_script_collection.

  data: ls_source_code  type ts_source_code,
           lt_source_code  type tt_source_code,
           lsz_return_code type soi_ret_string.

* Building the Script - every line has to end with cr/lf
  ls_source_code = 'Sub okadis_demo()'. "#EC NOTEXT

  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 = 'MsgBox("Huhu1")'. "#EC NOTEXT
  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 = 'MsgBox("Huhu2")'. "#EC NOTEXT
  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'. "#EC NOTEXT
  concatenate ls_source_code cl_abap_char_utilities=>cr_lf
  into ls_source_code.
  append ls_source_code 
           to lt_source_code.

* Saving the subroutines under one module name ("okadis")
  call method pptr_script_collection->add_script_from_table
  exporting  no_flush       = space
          script_name  = 'okadis'   "#EC NOTEXT
          script_type   = pptr_script_collection->script_type_vbscript
          script_table  = lt_source_code
  importing  retcode         = lsz_return_code.

  call method c_oi_errors=>raise_message
  exporting type = 'E'.  

* Calling the script:
  call method pptr_document->execute_macro
  exporting macro_string    = 'okadis_demo_1'                
                  script_name     = 'okadis'               "#EC NOTEXT
                  no_flush           = space
  importing retcode             = lsz_return_code.

  call method c_oi_errors=>raise_message
  exporting type = 'E'.
endform.

Hope this helps.

Regards,

Lars

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Fri, 19 Mar 2010 07:14:27 GMT

Dear Lars Kempkens

It seams that you are the one with the best idea of how to invoke a excel sheet from ABAP with macro.

I have used your example where you mange to have Excel to start a message box from ABAP - and this seams to work perfectly!

But when I try to use the same method on more advanced macro statements I get errors when executing the macros.

here is what I try to start i excel:

ls_source_code = 'Worksheets("Pivot 1").PivotTables("PivotTable1").PivotCache.Refresh'.

CONCATENATE ls_source_code cl_abap_char_utilities=>cr_lf INTO ls_source_code.

APPEND ls_source_code TO lt_source_code.

This code gives me following error :

Error "Type mismatch: 'Worksheets'" occurred during macro execution

Please, please help me Lars K. or anybody else who had solved this issue...

Thanks

Lars Bregnedal

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Thu, 20 Jan 2011 13:29:07 GMT

Hi Lars Bregnedal,

Did you find a fix for this? I have a similar request and can't get passed a sapscript error.

Kind Regards,

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Wed, 08 Jun 2011 09:59:12 GMT

Hi Lars,

Thanks a ton for the example. We are using in the similiar way of the example you have mentioned but we are getting short dump while calling .

Error: "Access via 'NULL' object reference not possible."

Runtime Errors : OBJECTS_OBJREF_NOT_ASSIGNED_NO

Exception : CX_SY_REF_IS_INITIAL

DATA: L_SCRIPT_COLLECTION TYPE REF TO I_OI_SCRIPT_COLLECTION.

CALL METHOD L_SCRIPT_COLLECTION->ADD_SCRIPT_FROM_TABLE

EXPORTING NO_FLUSH = ''

SCRIPT_NAME = 'Macro'

SCRIPT_TYPE = L_SCRIPT_COLLECTION->SCRIPT_TYPE_VBSCRIPT

SCRIPT_TABLE = LT_SOURCE_CODE

IMPORTING ERROR = GC_ERROR

RETCODE = L_RETURN_CODE .

could you please suggest how to call this method.

Thanks in advance.

Re: Need OI Example - ADD_SCRIPT_FROM_TABLE and EXECUTE_MACRO

Former Member — Wed, 08 Jun 2011 16:21:39 GMT

Hi Lars,

I cannot find class implementation for i_oi_script_collection.so i was getting short dump " Access via 'NULL' object reference not possible." Runtime Errors:OBJECTS_OBJREF_NOT_ASSIGNED_NO Exception:CX_SY_REF_IS_INITIAL

I have tried to find using "where used list" but did not get any result.

Please help us how you have implemented the method ADD_FROM_SCRIPT_TABLE ?.

Since i could not find class implementation, i have done it in other way but still its not working. Kindly verify the below code.

TYPES:

TS_SOURCE_CODE TYPE TEXT1024,

TT_SOURCE_CODE TYPE STANDARD TABLE OF TS_SOURCE_CODE WITH DEFAULT KEY.

DATA: LS_SOURCE_CODE TYPE TS_SOURCE_CODE,

LT_SOURCE_CODE TYPE TT_SOURCE_CODE,

L_RETURN_CODE TYPE SOI_RET_STRING.

LS_SOURCE_CODE = 'Sub Macro1()'.