Hi,
I am having a couple of difficulties with Excel which I am not sure how to overcome.
1.
I need to be able to delete first 30 rows in each of few hundreds of XLSX files. Unfortunately, there is no Robotask command 'Excel Delete Rows'. I tried using a macro, but for some reason Robotask cannot find the macro in each file's workbook. And indeed, the macro, although is present on the list of macros for Excel in general, is not added to each Workbook on each file specifically. Obviously, opening each file and adding the macro to each workbook manually defeats the purpose of automation and is not doable for hundreds of files. Also, using a macro which consists of just one action feels unproductive.
Probably, it could work if I had a way to add the code of the macro into the task and then task Robotask with applying it to each file externally, so to speak, not by running already existing macro from within. Or if I could save the macro as a file and provide the file's location. But currently Robotask only takes macro's name.
2.
I need to compile a summary Excel file by copying some of the cells from each of hundreds of Excel files into it. Unfortunately, Robotask can open only one Excel file at a time. So, at each iteration I have to close the summary file and then open it for the next. I think this wastes time and resources. Is there a way to open more than one Excel file at a time?
For now I am thinking of firstly saving each of the source files as CSV and after that read them into that summary XLSX.
Possibly conversion to CSV could help with the first problem above, but I cannot see 'CSV Remove Row', only 'CSV Remove Column' is there.
Excel: operations with rows and columns
Re: Excel: operations with rows and columns
Thanks for the tip. I put it in our ToDo.
You can use TXT Delete line action.
For example, if you want to delete 30 lines at the beginning, you need to delete the 1st line 30 times in the loop
That is:
JSON format allows you to save structured data of any complexity into a one text string. Read about JSON and variable prefixes here
Note that a CSV file is just a text file, but honoring the CSV formattingPossibly conversion to CSV could help with the first problem above, but I cannot see 'CSV Remove Row', only 'CSV Remove Column' is there.
You can use TXT Delete line action.
For example, if you want to delete 30 lines at the beginning, you need to delete the 1st line 30 times in the loop
- Load CSV file to variable
- Remove 30 lines in the loop
- Save text file
You can open only one EXCEL document in one task. But you can open second document in another task.Is there a way to open more than one Excel file at a time?
That is:
- Run a task that extracts the required data from the second document
- The task extracts data and save data in JSON format (for example) in one variable
- and return these data as result. Also you can save data to a temporary file in your own format.
- The master task will retrieve the data and put it into a summary document
JSON format allows you to save structured data of any complexity into a one text string. Read about JSON and variable prefixes here
Oleg Yershov
Re: Excel: operations with rows and columns
Thank you, Oleg. As always, your suggestions are not only effective but elegant as well.
Originally some data in those first 30 useless rows was similar to the data I needed from subsequent rows and was retrieved by search instead of what I needed. That is why I wanted to delete first rows - just to stop them from getting in the way. At the end, after better analysis of the data, I managed to avoid deleting rows and just refined the search conditions. In my case that was probably more effective than inserting another loop into a loop.
I knew CSV was a text file but was afraid that by mistake I could interfere with rows and columns and the data would be in a wrong position after editing and saving CSV as text. I will keep your suggestion in mind and try it next time.
As to using two tasks for working with two Excel files, what a brilliant idea and beautiful solution! I would have never thought of it myself! I need to try it. If I don't get distracted, I'll post a comment about the result - if I run into any difficulties with implementing this solution or any other notes - in a week or so.
I am amazed. Thank you very much for your help!
Sincerely,
Lana
Originally some data in those first 30 useless rows was similar to the data I needed from subsequent rows and was retrieved by search instead of what I needed. That is why I wanted to delete first rows - just to stop them from getting in the way. At the end, after better analysis of the data, I managed to avoid deleting rows and just refined the search conditions. In my case that was probably more effective than inserting another loop into a loop.
I knew CSV was a text file but was afraid that by mistake I could interfere with rows and columns and the data would be in a wrong position after editing and saving CSV as text. I will keep your suggestion in mind and try it next time.
As to using two tasks for working with two Excel files, what a brilliant idea and beautiful solution! I would have never thought of it myself! I need to try it. If I don't get distracted, I'll post a comment about the result - if I run into any difficulties with implementing this solution or any other notes - in a week or so.
I am amazed. Thank you very much for your help!
Sincerely,
Lana