05-17-2006 3:02 PM
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?
05-17-2006 8:15 PM
05-17-2006 8:46 PM
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.
09-15-2006 10:04 AM
can you please post the way you append your script to the script table?
06-17-2008 9:56 AM
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
06-04-2009 1:41 PM
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
08-25-2009 2:25 PM
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?
09-29-2009 1:55 PM
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
03-19-2010 7:14 AM
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
Br
Lars Bregnedal
06-08-2011 10:59 AM
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.
06-08-2011 5:21 PM
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()'.
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 = 'ActiveSheet.PageSetup.PrintArea = ""'.
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 = 'With ActiveSheet.PageSetup'.
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 = '.Orientation = xlLandscape'.
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 With'.
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.
DATA: table_url(256).
DATA: DOC_COOKIE TYPE I VALUE 1.
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 = ''
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.
Kindly suggest us how we should go ahead. please help us.
01-20-2011 1:29 PM
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,