Invoke VBA in UiPath generated excel

Help Activities
, , ,
1

Hi All,

I have a VBA vode in a text file (contains some formatting steps on a worksheet)
The flow generates az Excel output and I would like to use Invoke VBA to do the formatting.
I got no error but the flow is freezin at the Invoke VBA step and I need to stop manually.
Is that normal behaviour or do I need to enable VBA?
If yes, how to enable VBA in an Excel generated by UiPath?

Thank you.

2

@erika027
Make sure macros are enabled in Excel. To do this, open Excel, go to File → Options → Trust Center → Trust Center Settings → Macro Settings, and select Enable all macros.
Ensure the VBA code is correct and does not contain errors. You can test the VBA code directly in Excel before using it in UiPath

3

This Excel is generated during execution just like Invoke VBA step.
File → Options → Trust Center → Trust Center Settings → Macro Settings, and select Enable all macros. I can not do it during execution.

4

@erika027

In macros what is the operation you performing

5 
> Sub color()
> ActiveSheet.Range("A1:E11").Select
>     With Selection.Interior
>         .Pattern = xlSolid
>         .PatternColorIndex = xlAutomatic
>         .Color = 65535
>         .TintAndShade = 0
>         .PatternTintAndShade = 0
>     End With
> End Sub

kép

6

@erika027

  • Enable all macros (not recommended, potentially dangerous code can run) All macros run without confirmation. This setting makes your computer vulnerable to malicious code.
    In Excel this option is Enable VBA macros (not recommended, potentially dangerous code can run) and it only applies to VBA macros.

This option would enable macros for all file so should work in theory for files genarated by UiPath you can give it a try.

Another way you could do is create a template file with macros enabled copy that file whenever the project executes make your changes to that file. It should execute your macros

7

@erika027

Sub color()
    ActiveSheet.Range("A1:E11").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

image
Your’s is working perfect for me
Manually create an excel and enable trust centre then check colouring the cells in excel

8

Hi @erika027

Try this Invoke VBA code:

Sub color()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace YourSheetName with the name of your sheet
    
    With ws.Range("A1:E11").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Try to place your text file within your project folder. Make sure you give correct method name. If the excel should be created at run time please check the tick mark Create If not Exists and try once.

Let me know if you have issues.

Regards

9

Hi @erika027

For using the Macros in UiPath, you have to enable the macro settings in Excel file.

Open a excel file and follow the steps and enable the macros. Then UiPath Studio can execute the macros in excel file.

Check the below image for your better understanding on enabling Macros in the excel file,

image
image1295×626 38 KB

While during the execution you cannot do this steps, stop your bot in Studio. Open Task manager and kill the excel application. Then give one more try. If you are getting any exception send us the exception details.

Hope you understand!!