MS Excel VBO::Write Collection - 'Could not execute code' Error

[stepher]

I have one process that seems to have intermittent issues with the [MS Excel VBO::Write Collection] action.  The best I can tell is that it occurs when there is a comma (,) in any text field.  I have manually removed the offending characters from the collection before starting into the Excel steps, but once the error has occurred I cannot get past it.  I must be missing something.  I have checked for other 'suspects' ([CR], [LF], [NBSP], etc), but the source data seems otherwise clean of special characters.

I did try the [MS Excel VBO::Write Collection (Fast)] action, but that gives me an 'out of memory' error.  The issue does not seem to be size-related.  My source collection is not that large (today was under 900 lines , by maybe 15 columns). 

Coincendentally, we moved from v10.2 to v10.3.6 a couple of weeks back.  I was hoping that maybe it would address the issue.  I am fine moving to v10.3.8 (the latest version), but I am not seeing anything in the Release Notes that would give me hope.

Open to most any ideas.  Thanks,

Red

[GeoffHirst]

Hi Robert,

Thank you for being an SS&C Blue Prism customer, and for using Blue Prism. 

I have taken a look at the code for the asset you mention, and while I have version 10.3.8, I don't really see anything in the code that would cause a problem with commas.

However, we have seen that sometimes, non-printable characters can cause all sorts of havoc in situations like these. We have been using this tool https://www.soscisurvey.de/tools/view-chars.php to examine text that might be causing a problem.

If you have already explored this path, please forgive me. Let me know if you need this looking into any further. We may need some kind of sample data so we can replicate the problem, it doesn't need to be 'real' just something that shows the issue.

With regards the other issue you mention the out of memory with the Write Collection (fast) this action actually uses an in memory array, that gives it its 'fast' so to speak. The size of array you are talking about doesn't seem excessive, how much memory is available in that machine and would it be possible to maybe try the procedure on a machine that has more memory?

Please let us know how you get on.

regards

[Michael_S]

Hi all,

With apologies, @GeoffHirst's excellent response was sent to purgatory by an overly aggressive robot.

Rest assured, the robot responsible has been chided for its actions and is now being provided with less power supply as punishment. 

Thanks for your patience @stepher - and thank you for letting us know @GeoffHirst !

[stepher]

Thanks, Michael,

I am sure the Bot in question is suitably abashed...and probably a little hungry now.

Geoff was also kind enough to direct message me his response, so we had a quick exchange, but I will pick back up on the main thread.

Thanks so much,

Red

[stepher]

Argh... I hate when I am an idiot.  It happens often enough that you think I would be used to it by now... but, no, it still stings.

Anyway... The comma was in a red herring (but what other kind of herring would I have?).  But I do want to add onto Geoff's response.  I took the offending Collection to a pipe-delimited text file and scoured it for nonprintable characters.  I did not see any.  I tried the site/tool that Geoff mentioned, but it balked at taking the whole file.  I don't blame it.  So, I searched for a way to identify these characters using Notepad++ (my text editor of choice).  My buddy, MS Copilot mentioned that someone named Shahab had a method (https://www.shahabjafri.com/2020/03/01/how-to-find-non-ascii-unprintable-characters-using-notepad-plus-plus/).  In a nutshell, search for the text '[\x00-\x08\x0B\x0C\x0E-\x1F\x7F-\x9F]', using the Regular Expression option.  I do not know if this is an exhaustive search, but the text can be modified, and it is less limited by the source file size.  But, as I said, helpful but not pertinent to my particular issue.

What did end up being pertinent to my particular issue is to under stand that the earlest valid date value for Visual Basic (and, therefore, I am assuming for BP's 'IsDate' validation function) is January 1, 100.  For MS Excel, the earliest valid date is January 1, 1900.  So, if someone were to put a birthdate March 13, 1194, it would pass the validation check I put into place, but not be accepted by Excel.

So, this was a bit of a basic issue, but the way I ended up figuring this out may help others.  I put a [MS Excel VBO::Show] action in the path... and then I watched it.  That allowed me to see what element was causing it to trip up, and what was now obviously wrong with the value.  And I found two other instances of the same issue.  The troubleshooting may have been as basic as the initial problem, but sometimes I need to be reminded to take the basic path.

Okay, I am going to go off an nurse my ego back to health.  I hope you all have a great weekend.

Red

[GeoffHirst]

Hi Red,

I am so pleased you have your solution. It was pretty much guaranteed to be a data issue, but finding them can sometimes be a nightmare.

At least you know that non-printables can cause issues too, and now, how to deal with those.

You have a good weekend sir, I am off now for a week heading up into Northern Scotland.

regards