I’ve had a recurring issue for a while now and I’m not sure exactly how to fix it. I have a bot that searches various excel schedule files for various places at my work and organizes them into 1 large master file. On the master excel sheet there are multiple tabs at the bottom. Each tab has a month worth of schedules for each person, this requires the bot to look at 2 seperate excel files.
My issue is that the bot will work flawlessly through the first half of the month and gather all of the schedules like it is supposed to… but occasionally when it starts completing the second half of the month, an arbitrary amount of data from the beginning of the month is now gone.
To ensure that I am gathering data, i have “unkown” typed into every cell which then gets replaced with the person’s correct schedule for the day. So the bot will run through 85 people and write data into columns E through R. When it reads the new excel file and starts writing data to cells S through AF, some large percentage of the data in E through R has now reverted back to “unknown”
This doesn’t happen all the time. Some times it runs as planned and all the data is saved as its supposed to. I have found that restarting my computer and re running the automation is sometimes effective, but that doesn’t always solve the issue. Any ideas on why this would be happening, or what I can do to stop it from undoing 15 minutes of work that its done would be greatly appreciated!
Do you think it would be possible to reproduce this on a blank project with some dummy data?
So, in a way, to try and minimize the possible causes so that we can pin point the specific bug.
One thing to try first would be the latest (preview) version of the Excel activity package and see if that helps.
All those unknowns should be replaced with data. I went into my automation and added a save workbook function and a 3 second delay before it moves on to the second half of the month (which entails opening a new excel application scope, copying the data, and then opening back up the main document and pasting the data at the bottom of the sheet where we then start sorting it).
I re ran the program this morning and it was working great. I was hopeful that the addition of a save function would help, however it did not. The bot worked perfect through the first half of the month. I watched as it gathered all the data correctly and placed it cell by cell into the correct spots. It then saved and closed the master sheet, After copying the data it needed from the other sheet, when it re-opened the master sheet, it was in the exact same configuration as the screenshot above. It was like the bot never ran. The bot saves after updating each cell so it somehow reverted back to its original state over nearly 1000 saves prior. I feel like somehow it is saving and reopening the original file instead of the most current saved version of the file.
Like I said, it doesn’t always do this, and it ran through the other 4 tabs without issue, but the bot is useless if I can’t count on it to consistently and accurately getting the data for me.
I went to the manage package section of my bot yesterday and they were all needing updates. I got them all up to the current builds but that, unfortunately, did not fix the issue.
I just tried it 4 more times and the same thing keeps happening. I tried restarting the computer but that did not help. On the last time through. I paused the automation when it completed the first section. I closed the excel sheet, and then navigated to the file on my computer. I opened it up and everything saved correctly and was perfect. I then closed the file and continued the automation. When it reopened the master file, it had reverted back to its initial state. boggles my mind.
the only thing I can think of is that in the excel application scope I use a variable called excelbook which i have defined as the proper path. I have it saved as a sequence wide variable and I dont know if that could cause issues with it remembering the entire file instead of just the path for the duration of the bot.
Im pretty new to automation and visual coding in general so it could be something dumb that I am doing. It just seems like its not something wrong with my logic.
Is this behaviour noticed on the same set of data? What are some differences between a run and the other? Is the data saved or is it simply that the data isn’t processed?
Additionally, what version Studio & Excel are you using? Would it be possible to share with us a sample workflow that we can attempt to repro?