How To Use VBA Recorded Code in ABAP

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

Hello community,

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: