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, 

[SayeedBinAbdullah]

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.

[shenryifds]

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? 

[SayeedBinAbdullah]

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.

[stepher]

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

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

[shenryifds]

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! 

[shenryifds]

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. 

[Anton__Hosang]

Yeh, Collections are .NET DataTables