Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
Stefan-Schnell
Active Contributor
5,365
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:

4 Comments
Labels in this area