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

[shenryifds]

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, 

[stepher]

@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...

1. 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.
2. 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

[shenryifds]

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. 

[SayeedBinAbdullah]

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

[shenryifds]

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? 

[SayeedBinAbdullah]

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

[shenryifds]

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! 

[shenryifds]

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. 

[SayeedBinAbdullah]

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?

[shenryifds]

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.