SAP IRPA 2.0: How to use VB script to manipulate Excel on cloud studio

[ylazrak]

Hi

I am writing this blog post to give a feedback and share my experience on SAP IRPA V2 and solutions for the challenges I got on my projects.
On this blog post, I share with you the technical steps on how you can use VB script to be able to do some extra functions to manage Excel.

Part 1: Close Excel of another instance

First, let me introduce my use case. The bot connect to SAP Gui, run the transaction SE16N and export the result of the SAP ALV Grid.

As you know when you export an Excel, SAP open it. Therefore, if you try to read/write this file by the bot you get a concurrent access.

The challenge is how to close this file:

I tried to close it through the Excel API but could not, I guess because it is a different instance. This is why I used a VB script to terminate the process of the other instance.

Below is the code to write in the custom script.

 

function formatEscapCaracters(f) {

    return f.toString().

        replace(/^[^\/]+\/\*!?/, '').

        replace(/\*\/[^\/]+$/, '');

}



irpa_core.core.log('custom script close any opened Excel', irpa_core.enums.logType.Info, 'excelAutomation');



try {

    var MSScrCtrl = irpa_core.activeX.create("MSScriptControl.ScriptControl");

    MSScrCtrl.AllowUI = 1;

    MSScrCtrl.Language = 'VBScript';



    var VBScode = formatEscapCaracters(function () {/*!

        Sub closeAnyOpenedExcel()

            For Each Process In GetObject("winmgmts:").ExecQuery("Select Name from Win32_Process Where Name = 'EXCEL.EXE'")

                Process.Terminate

            Next

        End Sub

    */});



    MSScrCtrl.AddCode(VBScode);

    MSScrCtrl.Run("closeAnyOpenedExcel");



    irpa_core.core.log('custom script close any opened Excel', irpa_core.enums.logType.Info, 'excelAutomation');



} catch (error) {



    irpa_core.core.log('closeAnyOpenedExcel Exception occured:' + error, irpa_core.enums.logType.Error, 'excelAutomation');



}

 

Part 2: How to import Macro’s module to an Excel file and run a Macro

The next challenge was to manipulate the exported Excel file to do sorting data, apply functions like “VLOOKUP” and some conditional formatting.

Below is the code to write in the custom script. I am using the call back function to escape the special craters ‘/*’ & ‘*/’ also to replace “ipSapExportFilePath” string in the variable VBScode with the input parameter “ipSapExportFilePath” of my custom script.

 

function formatEscapCaracters(f) {

    return f.toString().

            replace(/^[^\/]+\/\*!?/, '').

            replace(/\*\/[^\/]+$/, '').

            replace('ipSapExportFilePath', ipSapExportFilePath).

            replace('ipSapExportFilePath', ipSapExportFilePath).

            replace('ipSapExportFilePath', ipSapExportFilePath).

            replace('ipSapExportFileName', ipSapExportFileName).

            replace(/\\\\/, '\\').

            replace(/\\\\/, '\\');

}



irpa_core.core.log('custom script execute Macro', irpa_core.enums.logType.Info, 'excelAutomation');



try {

    var MSScrCtrl = irpa_core.activeX.create("MSScriptControl.ScriptControl");

    MSScrCtrl.AllowUI = 1;

    MSScrCtrl.Language = 'VBScript';  



    var VBScode = formatEscapCaracters(function () {/*!

        Sub executeMacro()

            Set objExcel = CreateObject("Excel.Application")

            Set objWorkbook = objExcel.Workbooks.Open("ipSapExportFilePath\ipSapExportFileName")

            objWorkbook.VBProject.VBComponents.Import  "ipSapExportFilePath\ExcelMarcosModule.bas"

            objExcel.Run "TheNameOfYourMacro"

            objExcel.ActiveWorkbook.Save    

            objExcel.ActiveWorkbook.Close

            objExcel.Quit

        End Sub

    */});



    MSScrCtrl.AddCode(VBScode);

    MSScrCtrl.Run("executeMacro");



    irpa_core.core.log('end custom script execute Macro', irpa_core.enums.logType.Info, 'excelAutomation');



} catch (error) {



    irpa_core.core.log('executeMacro Exception occured:' + error, irpa_core.enums.logType.Error, 'excelAutomation');



}

 

To sum up:

In this blog post, we saw how we could:

• In the first part, use VB script to terminate the process of a concurrent Excel instance.


• Then how to import a Macro’s module and run a macro in the module

 

Hope this blog post helped you to solve your challenge. I will be very happy to read your comments or feedback, either for improving my suggestion or introduce other challenging aspects.