Hi, I’m testing out a new process for one of the departments here at my place of work and I’m running into a rather stupid problem that shouldn’t be happening in any scenario.
Basically the whole process starts by reading in 1 file, and not a large one. Maybe about 60 rows in this report today. I started the process and it went along just fine but at some point it failed (most of it is interacting with an AS400 terminal so it was likely the cause of the stop). This isn’t a problem as I can just restart it and the bot can pick up where it left off.
Only problem is that when the bot tries to read in the file, of the same workbook/sheet name (running locally now), it just throws that generic syntax error and gives me no other details. I debugged and checked the properties and both the workbook name and sheet name are correct and visible to the bot, the file itself contains no formulas (it may have contained formulas on the first run, but is has since been written back to so I assume they just disappear and leave the values behind), and the file hasn’t even been moved.
If anyone can help be understand why this type of thing happens I would greatly appreciate it as this has happened to me before with another process. Thank you.
Every time an entry is processed, a workbook write range activity writes the entire datatable back to that file and that was working fine for the first half of the file it was able to process
Does the robot have microsoft excel license? If so, I’d recommend switching to excel read range, as that should fix the issue.
The syntax error can come up when using workbook read range and workbook write range. It is especially problematic when reading/writing to the same workbook many times in a row. If you have an unexpected error during processing it makes it even worse. It usually relates to formulas within the workbook - usually because it is missing the ‘=’ in one or more of the cells.
The reason this happens is because workbook read range isn’t editing it within excel at all. Rather, it is directly editing the internal .xml files. All .xlsx files are essentially just zipped .xml files. When an error occurs in the middle of processing, it’s possible for data to get corrupted or to be left in a format excel doesn’t expect (aka syntax error). If you use excel application scope however, it edits it using excel rather than editing the .xml directly, so those syntax errors are impossible.
What happens if you open the excel file yourself after you get that syntax error? Can you scan the file and see? Does it throw an error right away when opening up the file yourself? It’s possible that excel automatically resolves the error when opening the document though, I am not really sure
The user I run the bot on does have an Excel license. I opened the file in question after I stopped debugging and everything seems to be there as expected. I can switch over to an Excel read range and hopefully that makes a difference.
Let me know if you have issues after switching. I’ve ran into similar problems in the past and that was always the root cause. Make sure you leave the excel application scope open the whole time and only close if/when there is an error or the process is complete. It will save a lot of time and prevent various errors, especially if reading directly from a file server rather than a local file
Something to keep in mind: When using excel application scope for ‘write range’ activities, it will still work without error even if the file is opened by another user. This is a problem because the robot thinks it was successful, but the changes will not be saved. Therefore if multiple robots are used on this process, then you will have to account for this somehow.
Looks like that did it, I kinda was hoping I wouldn’t need to see the Excel open and close with each entry (hence the point of workbook activities), but it’s working and I don’t care enough to complain about it. Thank you for your help!
Just make sure the property “visible” is NOT checked in your excel application scope. It’ll process everything in the background and be waaaay faster
EDIT: Leaving the file open is also much faster than using workbook write range because it doesn’t need to open/close the file multiple times. Using excel application scope is almost always better and faster if it is available
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu