Run Spreadsheet Macro suddenly failing & excel file gets corrupted - Trust Center checked

Help Studio
, ,
1

Hey everyone,

I am facing a weird issue that I cannot solve by myself. I have several automations that use one or more excel files (.xlsm) with macros stored within. Usually, the automation is carried out as intended. All macros are succesfully executed and everything works fine. I use those excels and automations sinces months already and I did not change anything big in the recent past. The error I am about to describe happened once a few weeks ago for the first time but then never again until last week. Since then, it occurs at least once per day…

Sometimes the automation would throw an Error: “Run Spreadsheet Macro: Cannot run the macro ‘X’. The macro may not be available in this workbook or all macros may be disabled.” When this happens, the excel gets corrupted so that when I manually open it, the macros are indeed not shown anymore. When I open the VBA Editor, I still see the modules but I cannot access them. The weird thing is, that when I open the very same corrupted excel file on another machine, the excel works again and all macros are accessible.

When I now copy this file back to my local automation folder and open the excel there again, it is still not working. If I do the same but save the file first on my local machine, the file can be used again on the robot machine as nothing ever happend.

The strucutre is “excel process scope - use excel file - run spreadsheet macro activity” using the current studio version. All Project dependencies are up-to-date including the libraries. The Excel Trust center is allowed (running on the least secure settings). The automation is run from a package stored locally on the robot’s machine. The error erratically occurs in both cases: when running in debug mode from studio as well as when started from orchestrator.

Sometimes the error occurs on the first run after I “repaired” the excel. Other times, It runs 20, 30 times without an issue before it suddenly crashes.

I already searched the forum, used chatgpt and google but I don’t find any solution. Has anyone an idea? Should I try to run the macros externally instead of using it stored within the excel? Is this considered less prone to errors? Could the Cortex Antivirus be responsible for the errors? (Unfortunately I cannot turn it off because it runs on a company guideline).

Thanky for your help!

2

@f.booch-arkossy

Are you using excel in a loop?

Or is it running prettry frequently?

Or there are multiple bots accessing the same file?

Try to use a kill process becore it…and also what is happening in the macro? Are there anything else that are being opened?

Cheers

3

Hey thanks for your answer!

The problem occurs most of the time to the one automation that runs every 10 minutes. Excel is not running in a loop but almost all automations use excel and most of the time, another system would be opened.

From there, data gets posted to excel where 5 to 10 vba macros manipulate the data in quick succession (extracting important data from one to another sheet, deleting old data, checking for new data entries and so on). The data then gets stored in a data table variable to be further used in other systems/excels.

So sometimes 2 or 3 excels are opened at the same time and the automation sometimes also use several “use excel file” acitivities for the same excel within one automation.

However the error occurs even when only one excel is used. The architecture is: Excel Process Scope - 1. Use excel file - If statement (2. Use excel file)

What I figured out now by running the automation several times and checking the excel each time myself: the error seems to occur either on closing the excel or on opening it. So the macros are already gone before the automation tries to use it.

Nonetheless I now unchecked the box with “save changes” instead using one save excel activity at the very end.

Also, I will use a kill excel in the beginning and in the end of each automation.

Interestingly I never had any issues with macros stored in my personal macro sheet. So maybe I should revert back from xlsm files and use the macros from the personal macro sheet. Is that any better?

4

@f.booch-arkossy

Just check with kill process you can move to personal book but the problem would be if you change the machine on which you run you wont have the personal book there

Cheers

5

Thanks again! I will try with the changes I have made and the additional kill process activities and let you know whether the issue still persists :slight_smile:

1 Like
6

Okay so neither the killing excel nor the change in saving the files changed anything… The excel still corrupts every 3 to 4 uses… I also turned off the firewall and the problem still persists.

Right now I hard reset the computer and while this reset is going on I try on another machine.

7

Getting the same error. Is this resolved?

8

Hey! Unfortunately the issue has not been resolved yet. I think there are two possible ways to avoid the issues with the macros.

  1. Build the automation in a way so that the sheet does not need to be saved when the macros are used. E.g., (a) close excel before you run the macros, (b) uncheck the save option for the activity that reopens the xlsm and let the macros run, save the output in variables (c) close the excel without saving it (d) reopen, paste outpout from variables and save excel.

This method worked for me so far but is super clunky.

  1. Rewrite all macros in a way so that you can use it from Personal Macrobook.

I hope the issue gets fixed soon. I tried hours of bug fixing and finally gave up (reinstalling office, resetting windows, resetting UiPath, different version etc.).

9

I am also encountering the issue you have described. For us, this issue started with a low volume in early December and has rapidly grown to around 10% of all Excel files we handle. This is also occurring on many different spreadsheets rather than a single template or file.

In some instances, the solution has not had a change in many months prior to this issue commencing.

To resolve, we have so far trialled multiple Excel Activity packs and repaired the Excel Interop with no noticeable improvement.

1 Like
10

This has also occurred with some of my processes.

11

Try saving your file in Binary format (.xlsb) and see if that works.

12

Any progress with this? Started to happen in our org a week ago. Getting worse, affected more users. Seems to me like some issue after update. Very strange is, that file, that is reported as corrupted to one user is just fine with another user. And even more strange, file that is corrupted with the user one day is again fine a day after for the same user. This is a huge issue. Files are not run in an loop or by any automation process. Users simply open, make some changes and close. Yes, macros are there and used, mostly to make worksheet changes. The files are stored and manipulated on Azure cloud based file server.
Thank you.

13

Ay update on the resolution ?

14

Hey! Unfortunately the issue has not been resolved yet. I think there are two possible ways to avoid the issues with the macros.

  1. MY RECOMMENDATION: Rewrite all macros in a way so that you can use it from Personal Macrobook. Use xlsx files instead.

  2. WORKS AS WELL BUT CLUNKY:

Build the automation in a way so that the sheet does not need to be saved when the macros are used. E.g., (a) close excel before you run the macros, (b) uncheck the save option for the activity that reopens the xlsm and let the macros run, save the output in variables (c) close the excel without saving it (d) reopen, paste outpout from variables and save excel.

This method worked for me so far but is super clunky.

15

Hi, after updating Excel to version 2401 (Build 17231.20236) our problems was solved.

16

I am facing the issue too. Whenever it occurs, we replace the macro file with new one. Works so far. @madd Can you please confirm again if this issue is no longer occurring after extended usage post excel version update?

17

HI, yes the update solved our problems.
We still have some files that fails, proberly because they are damaged from being used before the update. In that case we do the same as you. We “replace” the macros (copy/paste).

18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.