I use excel workbook activity and many write cells, it throws error in anyone of the write cell randomly. Any idea about it?
Try to use Excel application scope with Close workbook activity. Then this issue may not occur. Also try to use Kill Process for excel.exe before do the excel operation.
Ya I did like 50 write cells now unable to change to excel application scope and i used like 5 kill process for every 5 write cells
As a best practice we need to use Excel Application Scope activity. Cant you write all the values using write range.
No its not possible as i retrieve each value from multiple mails so write range will not be an activity to use.
Keep delay like 3 to 5 seconds for each and every write range and see if we are facing that issue. it is not best practice. give a try. thanks.
Where do you store the Excel file? If it is some kind of synced SharePoint location, it might happen that the bot tries to access the file too quickly and it has sync conflicts with itself.
No its in the project folder only
Can you try workbook activities - Write Cell activity to write into the excel sheet
Still you are facing the issue, then before write cell, use Kill Process activity and In properties write process name as EXCEL
Hope this may help you
Am using like 60 write cells I cant use 60 kill process as it is not a best practice.
Lemme try this bro
Sure Bro. please try and let me know. thanks.
No need of 60 kills, just place before the write cell and use Workbook activities
Why are you using 60 write cells, Can’t you use for each or some looping concept, or some conditioned-based looping.
60 write cell is definitely not a good choice.
The chances of failure are really high… You are opening a file 60 times back to back. this will end up either corruption excel or error that you are facing
Yeah. Good thought. He can store the excel file path in some excel and he can retrieve one by one using loop concept.
As a design practice, ideally one should avoid using open and close excel in loops. It is always going to open your automation to some unknown factors for causing some kind of an error. You can perhaps get each of the value first into a data table from all your emails and then try to write it at once using “Write Range” activity.
In case if you still want to use the write cell approach, I would first suggest kill the excel application before opening excel, give some delay value after excel gets opened by using Delay activity as the first activity in your Excel Application Scope and then use some iterative logic using counter to write to a particular cell.
However, the first approach where I suggested Data Table is always a recommended way from my prior experience. As regularly opening and closing excel many times can result in file corruption.
Having read the previous posts, maybe the following approach will be useful (I used it in some of my automations):
Group the values that are to be written to the excel - they can be grouped in a few groups, it doesn’t have to be only one group.
In case the values in a group are scattered in Excel file, for example (yellow cells are to be updated)
You can first read the data table (or even one data row only) and update just the yellow cells in data table.
Then use write range to write whole data (old data that was already in excel + updated). In that approach you use far less write range/write cell activities.
I would not recommend using hardcoded delays 3-5sek between write cells since it may happen that from time to time it will not be enough and then your workflow will end in Exception.
Hope this helps you!