topic Embed Object in Excel using OLE in Application Development and Automation Discussions

Embed Object in Excel using OLE

rajad — Fri, 19 Aug 2016 12:08:46 GMT

Hello Experts,

I am trying to embed an object inside excel and it is not working.

PFB VB and ABAP code used for it.

VB Code:

ActiveSheet.OLEObjects.Add(Filename:= <filepath>, Link:=False, DisplayAsIcon:=False).Select

ABAP Code:

GET PROPERTY OF lo_worksheet 'OLEObjects' = lo_object.

CALL METHOD OF lo_object 'Add'

EXPORTING

#1 = <filepath>

#2 = 0

#3 = 0.

I have passed #2 and #3 as 0, 1 , 2 , '0', '1', '2' and it is not working.

Please help me out!

Thanks,

Raj

Re: Embed Object in Excel using OLE

Sandra_Rossi — Fri, 19 Aug 2016 13:38:40 GMT

If you have a look at its definition, in the VBA object explorer (F2), you'll see that filename, link, displayAsIcon are respectively the parameters number 2, 3 and 4 (Function Add([ClassType], [Filename], [Link], [DisplayAsIcon], [IconFileName], [IconIndex], [IconLabel], [Left], [Top], [Width], [Height]) As OLEObject)

Re: Embed Object in Excel using OLE

rajad — Fri, 19 Aug 2016 17:17:36 GMT

Hi Sandra Rossi,

Thanks for your response!

Even when I pass them as #2 #3 #4, it is not embedding.

CALL METHOD OF lo_object 'Add'

EXPORTING

#2 = <filepath>

#3 = 0

#4 = 0.

Anything else that I am missing?

Thanks,

Raj

Re: Embed Object in Excel using OLE

Sandra_Rossi — Sat, 20 Aug 2016 20:07:38 GMT

I could reproduce the issue, and couldn't find a way to work around the issue using OLE. So, the only way to call OLEobjects.add is to convert the whole code into VBscript, and call it via DOI (method EXECUTE_MACRO of I_OI_SCRIPT_COLLECTION - search the forum and the SAP library Desktop Office Integration).

PS: 0 for false, and 1 for true are correct assumptions. Moreover, I did tests with another method whose first parameter was optional, and I noticed that doing "worksheet.checkSpelling ,true" (second parameter "ignoreUpperCase") with only EXPORTING #2 = 1 was unsufficient, it was working only with EXPORTING #1 = '' #2 = 1. So, I came to the conclusion that all first parameters are to be passed, even if not used, which means that OLEobjects.add may never work because OLEobjects.add '', 'filepath', false, false fails in VBA (the first parameter ClassType must not be passed).

Re: Embed Object in Excel using OLE

Sandra_Rossi — Sun, 21 Aug 2016 15:32:45 GMT

To execute VBscript, instead of DOI, you may try the trick by () :


 Create Object oScript 'MSScriptControl.ScriptControl'.

    Check sy-subrc = 0 And oScript-Handle > 0 And oScript-Type = 'OLE2'.

    "-Allow to display UI elements--------------------------------------
      Set Property Of oScript 'AllowUI' = 1.

    "-Intialize the VBScript language-----------------------------------
      Set Property Of oScript 'Language' = 'VBScript'.



    "-Intialize VBCode here---------------------------------------------
 VBCode = 'line1' && 'line2' && 'line3'...


 ...



    "-Execute VBScript code---------------------------------------------
      Call Method Of oScript 'ExecuteStatement' Exporting #1 = VBCode.


    "-Free the object---------------------------------------------------
      Free Object oScript.

Re: Embed Object in Excel using OLE

rajad — Sun, 21 Aug 2016 15:59:22 GMT

Thanks for your suggestion Sandra Rossi!

I will try the above one and confirm you.

Thanks,

Raj