topic Re: MS Excel VBO - Exception has been thrown by the target of an invocation in Product Forum

MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Tue, 30 Sep 2025 14:51:26 GMT

Hi there,

I have an error I've been trying to resolve for some time now and cannot seem to find the cause or resolution.

The error I am getting is "Exception has been thrown by the target of an invocation".

This is happening at the "Write Collection" stage.

Essentially what my process is doing is the Digital Worker is accession a web application (Chorus) and extracting comments from a list of work items. These comments that are extracted are saved to a collection.

When all of the items are completed I'm left with a collection of comments. This collection can have 1,000+ rows. The length of comments can vary.

When writing collection to the Excel file, it is failing.

Let me add that it does not always fail.

What I mean is that I will have 4 DW's running the same process and 1 of the 4 will fail with this error while the other 3 will be successful and I can see the report they've each generated.

It also isn't isolated to any 1 DW. DW 1 can work one time and another run it may be the one to fail.

I've looked this up extensively and also have a ticket open with support.

My recent thought was that the DW was maybe extracting a comment where the character count exceeds the max character count that can be written to Excel.

Excel has a max character count of 32,767 characters or around there.

To test this I manually added a comment to a work item of over 40,000 characters. If I read the comment I get all of the characters saved to my data item and collection but when I go to write my collection to Excel I see that it doesn't write all 40,000 characters. It just writes up to the most characters Excel can hold.

One thing to note is that while in Design Studio, if I manually add text to a data item, it can only hold up to 32,767 characters. It won't allow me to type any more. However if I read text and save it to a data item, it can hold more characters (ie: the 40,000).

I'm not sure if while running in production we'd get the same result.

Looking for any feedback or assistance here.

Thanks a lot,

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

stepher — Wed, 01 Oct 2025 16:46:27 GMT

@shenryifds ,

So, I don't think it is the number of characters, as much as it is likely the specific characters that are contained in the source information. There are two things that have tripped me up in the [recent] past...

The less likely issue is a conversion issue within a data type. For me this was a date value--manually entered, and patently incorrect (i.e., Jan 1, 1022)--which is valid as a Visual Basic/Blue Prism Date, but it is not valid as an Excel Date value.
I do not know if it is a 'dirty little secret', but something I have to keep in mind is that Collections are comma-delimited structures. It is not a comma (Oxford or otherwise) that has caused me many issues, but errant unpaired quotation marks (i.e. single double-quotes). In the 'States', there has been a convention of using the single- and double-quotes as place holders for measure of length. So, ten feet, three inches would be displayed as 10' 3". It seems to be declining in use, but I do run across it with item descriptions.

Specifically locating the source of your problem can be tedious. If you can parse the files in a text editor outside of your process, you should be okay. If you know what you are looking for, you might be able to visually scan the Collection and see the issue. Barring that, I stepped through my process in debug, at a moderate to slow speed, and inserted a "Show" action before the "Write" action. Then I sat back and tried to pay attention as the spreadsheet was populated cell by cell. When it inevitably threw an exception, I was able to identify the offending line. Of course, there could be more than once instance of the issue.

I hope this at least sets you in the right direction. Best of luck,

Red

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Wed, 01 Oct 2025 18:04:03 GMT

Hi Stepher,

Firstly, thank you so much for your input. This really helps.

Funny enough, it seems we're on the same page.

In terms of the data types, my collection columns are all text data types. Even if a date it's still text.

In Excel all of my columns were formatted to general. I've since changed this to text to match my collection. Not sure it matters but still something to try.

I have eliminated the possibility of it being the character length by stepping through the process and purposely writing a string of over 40,000 characters. What I observed here is that Blue Prism will simply write up to the max character count in Excel.

This is a process with high volume (1,000+ items) but to test further and try and replicate this error I've been processing 50-60 items.

Example:

I was actually able to get the same error thrown in Design Studio. I ran it a few times to see a pattern and I could see that it kept happening at row 16 and at the same cell. This cell came right after a date, however this date is coming in as a text format (collection example above). So it did write the date but then the next cell it failed. The next cell however was simply text again but in this case the field was blank.

The expectation here was that it would just write "" to that cell. That's what it did in the above rows when that field was blank.

What I did next was simply put a 0 in that collection field and ran it again. Again it failed at row 16 but this time at a different cell. It was was the last column of the row.

This row is for "comments" which are comments the DW has extracted from a work item in an application. These comments are free form and can contain any character. I noticed there were "=" and what looked like "*". I removed these characters and it the next time it worked.

I tried to reverse this to see if it would fail again. I first removed the 0 I placed in that empty field and ran the process again and it worked. I then tried to add some "=" and other characters to test and it worked again...this confused me.

I think you're right though in that it does have something to do with the characters.

I will continue to test and try and pinpoint exactly what's going on here.

I thank you again. Your help has been much appreciated.

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

SayeedBinAbdullah — Thu, 02 Oct 2025 12:04:49 GMT

Hi @shenryifds

Wanted to check whether or not all 4 DW's are writing data on same excel?

If yes then are you using Environment Locking in your process before writing data to excel?

https://docs.blueprism.com/en-US/bundle/blue-prism-enterprise-6-7/page/helpEnvironmentLocking.htm

Because if all 4 DW's will be writing data to same excel then there are high chances of data loss in case of more than 1 DW trying to update the excel.

Best Regards,
Sayeed Bin Abdullah

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Thu, 02 Oct 2025 13:23:54 GMT

Hi Sayeed,

There are yes but the Excel is a template saved a .xltx so multiple users can open the Excel and save their own report.

When I run the process on 4 DW's 3 of 4 will be successful. The 3 DW's successfully generated their report and saved them to the file path.

The other DW gets the "invocation error".

I've also ran this process using 1 DW to test and would get the same error.

Would it still be worth trying the Environment Lock?

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

SayeedBinAbdullah — Sat, 04 Oct 2025 05:14:40 GMT

It is recommended to use Environment Locking when multiple DWs are working on the same file.

For example, if DW1 opens an Excel file and begins updating it, and at the same time DW2 tries to open the same file, the file will open in read-only mode for DW2. As a result, even if DW2 makes changes, those updates will not be saved because the file was opened as read-only.

Best Regards,
Sayeed Bin Abdullah

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Sat, 04 Oct 2025 15:30:59 GMT

Thanks Sayeed,

I've ran the process using 4 DW's before and 3 of the 4 will be successful.

They will all open the Excel template. It does open as Read Only but they then Save As. When the process is completed I will see all 3 reports saved to the path.

I will try using the Environment Lock to see if that helps.

I've got the same error when running on 1 DW as well so I didn't think that was the problem.

I appreciate your feedback and I will try this.

Thank you!

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Mon, 06 Oct 2025 14:00:30 GMT

Quick update here.

To try and find the bug or what's going on here I decided to try processing the same 1,377 items.

This time though in order for me to try and look at every item, I ran the process in batches of 50.

So I'd run the process on 50 items to extract my comments and then write to Excel and save it.

I would then review the report to see if there were any special characters or anything unusual.

I would find all kind of characters like "=" or "*" for example but they would always write to Excel no problem.

That eliminated the possibility of it being some character or formatting that was causing BP to throw this error.

I did for all 1,377 items in debug mode.

What I ended up with as 28 or so reports.

I was successful all times.

I then got all of these reports into a collection and wrote it to a separate report to combine them.

I did this several times again trying to recreate the error but was successful every time.

So writing that much data all at once also seems fine.

I will trying to run a full end to end process with the DW again today to see what happens this time around.

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

SayeedBinAbdullah — Mon, 06 Oct 2025 16:49:09 GMT

Also if there are separate reports getting created for each DW then Environment Locking will not be required in this case.

However, to be extra sure you can modify your flow to create an individual .xltx file at the beginning of the flow itself with file name like for example ExcelComment_DW1.xltx

And also is there any specific requirement that you are using .xltx instead of .xlsx?

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Mon, 06 Oct 2025 18:24:50 GMT

Hi Sayeed,

When you say to add an individual xltx file at the beginning of the flow, are you saying each DW create their own xltx file?

Regarding the .xltx file, the reason I went this route was because previously I did have it saved as .xlsx however I would get an error that the "application is busy". I took that as an indication that multiple DW's could not access this file at the same time.

My resolution was to use the .xltx file because I had read that multiple users could open the file at the same time without any conflict.

Also, I want to add that I tried running this process again in production and for the first time it worked. All 4 DW's completed the process and successfully generated their Excel report.

You can see reports saved around the same time.

Now why it worked this time vs other times? I am not sure.

I no longer think that the issue is surrounding the character count or any special characters.

It worked in debug mode but running and writing to excel in smaller batches but then I also did the full run in prod on all 4 DW's.

For now I would keep testing to see if I can have multiple successful runs.

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

SayeedBinAbdullah — Tue, 07 Oct 2025 04:55:04 GMT

Yes, I’m referring to the creation of a .xltx file by each DW before starting Excel activities.

If Environment Locking is applied while opening the template file and creating individual DW Excel files, the “application is busy” error will not occur and you will be able to use .xlsx file.

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Tue, 07 Oct 2025 12:30:34 GMT

Thanks Sayeed,

I see what you're saying.

I have 2 successful runs with the .XLTX file.

Is it worth going back to the .XLSX file and using the environment lock? Is there any harm using the .XLTX file?

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

SayeedBinAbdullah — Tue, 07 Oct 2025 13:33:32 GMT

There’s absolutely no harm in continuing with the .xltx format. In fact, it’s often a more reliable option when multiple Digital Workers (DWs) are running in parallel.

However, you can also try using the .xlsx approach to see if the “Application is busy” error still occurs.

If it doesn’t, try generating individual output files for each DW in a single run (rather than running them in batches) to check if you’re able to replicate the issue you encountered earlier.

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

stepher — Tue, 07 Oct 2025 14:02:56 GMT

@shenryifds and @SayeedBinAbdullah ,

I am still not convinced that the issue is due to multiple machines attempting to access your report template file at the same time (whether as an '.xltx' or as an '.xlsx'). The reason for this is that you mentioned identifying a break in the writing of the Collection at a very specific cell location. So, this is occurring after the machine has opened its Indvidual version of the file.

I still favor the 'errant character' theory. I also realize that, as I read back my initial response that I skipped about half-a-thought--which was the bridge between mentioning the text editor and my probably-not-entirely-correct assumption that Collections are comma-delimited structures...

One of the other things that I do to troubleshoot these types of issues is to send my collection to a text file, instead of the Excel workbook. I then use my text editor of choice (NotePad++, but you can use whatever suits you) to review the file. There are two things I look for:

'Spontaneous' Quotation Marks: The first time I did this, I noticed that one of the fields contained a pair of double-quotes. But that was not expected in my dataset. It took me longer than I would like to admit seeing that the data within the double-quotes contained a comma. This led me back to my original Collection, where I confirmed that a comma was present.
Nonprintable Characters: One of the reasons I use NP++ is that it can be configured to highlight formatting and nonprintable characters. I have had nonprintable characters occur when end-users have cut and pasted values from browsers. Additionally, these pop up from time to time when I am parsing email text.

I doubt your specific issue is nonprintable characters, only because I don't think they usually cause Excel much heartburn. But the other benefit I have had to sending my collection to text filers is that I can sometimes catch breaks in expected patterns.

Anyway, it looks like you are making some progress. Again, best of luck,

Red

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Tue, 07 Oct 2025 14:17:20 GMT

Thank you Stepher,

I want to add that I have now had 2 successful runs back to back.

After doing my test in debug mode I haven't since replicated the error. I will be continuing to run the process to see if I can continue to have successful runs.

When I was testing in debug mode and writing to Excel in smaller batches I would be reviewing the collection and would find all kinds of special characters.

I would also purposely add different characters spontaneously throughout the collection trying to force an error but was always successful. I'd add a quotation mark for example but no error.

Quite stumped here.

Regarding the process being successful the last 2 runs, I'm not sure exactly why.

In terms of changes I recall I made sure my Excel columns were formatted to text to match my collection data types. This was me just trying anything at this point.

Also I was initially using an enhanced version of the Excel VBO and then switched to the basic version to test. I've since reverted back to the enhanced version.

I'll continue to test and send updates.

Thank you all!

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

shenryifds — Tue, 07 Oct 2025 19:50:54 GMT

I've now had 4 successful runs with all 4 DW's successfully generating and saving their reports.

Interesting as I'm not sure what the big difference here.

One thing to note is that the previous runs that were failing are from last week. What this process does at the start is run a SQL query to extract data.

We also updated the SQL query but I don't think that would matter.

The query is still pulling the same data at the end of the day. I'm really just trying to think of all the possibilities.

I'm happy I'm having successful runs but I still don't understand the original root cause. I'm worried I can see this error again at some point.

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

Anton__Hosang — Wed, 08 Oct 2025 15:48:10 GMT

Yeh, Collections are .NET DataTables

Re: MS Excel VBO - Exception has been thrown by the target of an invocation

naveed_raza — Fri, 28 Nov 2025 13:17:17 GMT

Target Invocation error mostly when BP lost connection with Excel.
for huge data i always prefer to export in XML file , xml file open easily in excel file. BP will not lose connection with XML in exporting and reading from XML.

you can think about using XML files