How To Use VBA Recorded Code in ABAP

[stefan_schnell]

With a tiny trick is it easy possible to use recoreded Microsoft Office VBA (Visual Basic for Applications) code in ABAP.

To do that I use only the Microsoft Script Control, with a little preparation for Excel. Then I read the content from an include, which contains the recorded VBA code. I concatenate the VBScript and VBA code and then I execute it - that's all.

The only thing which is in addition to doing, is to set a point in front of each line of the VBA code. This is necessary because I use oExcel object in VBScript code.

Here an example report:

"-Begin-----------------------------------------------------------------

Report zExcelViaVBScript.



  "-Type pools----------------------------------------------------------

    Type-Pools:

      OLE2.



  "-Constants-----------------------------------------------------------

    Constants:

      CrLf(2) Type c Value cl_abap_char_utilities=>cr_lf.



  "-Variables-----------------------------------------------------------

    Data:

      oScript Type OLE2_OBJECT,

      VBCode Type String,

      VBACode Type String.



  "-Main----------------------------------------------------------------

    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'.



    "-Code preparation for Excel VBA------------------------------------

      VBCode = 'Set oExcel = CreateObject("Excel.Application")'.

      VBCode = VBCode && CrLf.

      VBCode = VBCode && 'oExcel.Visible = True'.

      VBCode = VBCode && CrLf.

      VBCode = VBCode && 'Set oWorkbook = oExcel.Workbooks.Add()'.

      VBCode = VBCode && CrLf.

      VBCode = VBCode && 'Set oSheet = oWorkbook.ActiveSheet'.

      VBCode = VBCode && CrLf.

      VBCode = VBCode && 'With oExcel'.

      VBCode = VBCode && CrLf.



      "-Add VBA code----------------------------------------------------

        Call Function 'ZREADINCLASSTRING'

          Exporting I_INCLNAME = 'ZEXCELTEST'

          Importing E_STRINCL = VBACode.

        VBCode = VBCode && VBACode.



      VBCode = VBCode && 'End With'.

      VBCode = VBCode && CrLf.



    "-Execute VBScript code---------------------------------------------

      Call Method Of oScript 'ExecuteStatement' Exporting #1 = VBCode.



    "-Free the object---------------------------------------------------

      Free Object oScript.



"-End-------------------------------------------------------------------

Here the function module to read an include as string:

"-Begin-----------------------------------------------------------------

  Function ZREADINCLASSTRING.

*"----------------------------------------------------------------------

*"*"Local Interface:

*"  IMPORTING

*"     VALUE(I_INCLNAME) TYPE  SOBJ_NAME

*"  EXPORTING

*"     VALUE(E_STRINCL) TYPE  STRING

*"----------------------------------------------------------------------



    "-Variables---------------------------------------------------------

      Data resTADIR Type TADIR.

      Data tabIncl Type Table Of String.

      Data lineIncl Type String Value ''.

      Data strIncl Type String Value ''.



    "-Main--------------------------------------------------------------

      Select Single * From TADIR Into resTADIR

        Where OBJ_NAME = I_InclName.

      If sy-subrc = 0.



        Read Report I_InclName Into tabIncl.

        If sy-subrc = 0.

          Loop At tabIncl Into lineIncl.

            Concatenate strIncl lineIncl cl_abap_char_utilities=>cr_lf

              Into strIncl.

            lineIncl = ''.

          EndLoop.

        EndIf.



      EndIf.

      E_strIncl = strIncl.



  EndFunction.



"-End-------------------------------------------------------------------

Here my VBA example code, which is stored in the include ZEXCELTEST:

.Range("A1").Select

.ActiveCell.FormulaR1C1 = "1"

.Range("A3").Select

.ActiveCell.FormulaR1C1 = "2"

.Range("A5").Select

.ActiveCell.FormulaR1C1 = "3"

.Range("B2").Select

.ActiveCell.FormulaR1C1 = "4"

.Range("B4").Select

.ActiveCell.FormulaR1C1 = "5"

.Range("C3").Select

.ActiveCell.FormulaR1C1 = "6"

.Range("C4").Select

This code is a directly copied and pasted from the VBA IDE (only with a point in front of each line):



 



Here the result in Excel: