Skip to Content

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

003.JPG

 

/wp-content/uploads/2016/08/002_1019776.jpg

Here the result in Excel:

001.JPG

Enjoy it.

Cheers
Stefan

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Stefan Schnell Post author

    Hello community,

    here a tiny modification:

    "-Begin--------------------------------------------------
    Report zExcelViaVBScript.
    
      Call Function 'ZEXCELVIAVBSCRIPT'
        Exporting
          I_INCLNAME = 'ZEXCELTEST'.
    
    "-End----------------------------------------------------
    

    "-Begin--------------------------------------------------
    
    Function ZEXCELVIAVBSCRIPT.
    *"-------------------------------------------------------
    *"  Local Interface:
    *"  IMPORTING
    *"     VALUE(I_INCLNAME) TYPE  SOBJ_NAME
    *"-------------------------------------------------------
    
      "-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 'ZREADVBAINCLASSTRING'
              Exporting
                I_INCLNAME = I_INCLNAME
              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.
    
    EndFunction.
    
    "-End----------------------------------------------------
    

    The function module zReadVBAInclAsString deletes leading and trailing spaces and adds now the leading point in each line automatically. So be careful if you use underscores to break VBA code lines.

    "-Begin--------------------------------------------------
      Function ZREADVBAINCLASSTRING.
    *"-------------------------------------------------------
    *"  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.
                Condense lineIncl.
                Concatenate strIncl '.' lineIncl
                  cl_abap_char_utilities=>cr_lf Into strIncl.
                lineIncl = ''.
              EndLoop.
            EndIf.
    
          EndIf.
          E_strIncl = strIncl.
    
      EndFunction.
    
    "-End----------------------------------------------------
    

    With this tiny modification you can copy and paste your VBA code direct into the include, without any adjustments.

    Enjoy it.

    Cheers

    Stefan

    (0) 
  2. Sandra Rossi

    Excellent !

    But I could see many other adaptations needed:

    1. Excel constants must be changed into their integer value
    2. In VBS, method calls accept only positional arguments (while VBA accept named arguments)
    3. Statements spanned over several lines (underscore at the end of VBA line) are not accepted in VBS
    4. If we have “With Selection.Interior” of course the “point” must be positioned in front of “Selection”, not at the beginning of the line.

    For instance:

    ActiveWorkbook.SaveAs Filename:="C:\Users\Myself\Documents\Classeur3.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    is to be converted into:

    .ActiveWorkbook.SaveAs( "C:\Users\myself\Documents\Classeur3.xlsx", 51, , , , False )


    Maybe, do you know if there’s a free tool to convert VBA to VBS somewhere in the web?

    (1) 
  3. Stefan Schnell Post author

    Hello Sandra,

    thanks for your comment. You are right with your remarks. For this kind of cases it is necessary to edit the VBA code manually or it is not possible to process the code on this way.

    I don’t know and can’t find a tool to convert VBA to VBS, but many posts and questions about this topic.

    Cheers
    Stefan

    (0) 

Leave a Reply