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.

[former_member693923]

Hi Mohamed Yassine,

Thank you for explaining the calling of macro in cloud studio.
I have doubts regarding why you have called formatEscapeCaracters() and what is the paramtere f you are passing. I wanted to call the macro only in custom script and defined the path in workflow but its giving me error. Can you please help me with that.

 

 

Regards,

Sujata Jena

[janv7306]

Hi everybody,

 

does it work? I try it without success.

JV

[lingyiitech_ljc]

Hi JV,

 

Yeah, for me it works.

I use it to control the SAP components.

 

Regards,

Charles

[janv7306]

Hello Charles,

Thanx for reply. I have found a workaround using a different excel sheet with all macros in it. Then referencing and running it above the worksheet within automation. It works smoothly and allows keeping VBA outside of IRPA project. (may have + and - ;-)).

Regards, JV