By now you should see the potential in automating strenuous Excel tasks. You should also notice the importance of keeping your data in a standardized way. What steps do you plan to take to make these automations easier?
This was a great lesson. By the way, I see that when I run the solution provided in UiAcademy, “Append range”, the first line is pasted after 1 blank row. Therefore, the final pivot will include a (blank) data which may be misleading. I will paste the screenshot, hope anyone already figured out the reason.
I also had similar issue, I could not figure out the reason
Same here. I checked out the downloadable solution robot for the first time, but that did the same thing.
Same for me.
If you save the Excel File as CSV, you will discover that the second row is actually not empty (Screen Shot below):
There are empty strings (Empty string - Wikipedia).
The solution is to create a new Excel file with the first row (Client Name…) or a new sheet and then it will work perfectly with no blank space.
Sounds interesting and a practical work around!
I am still not sure why the UiPath training solution with “Append range” includes a row with an empty string, but if we forget about that temporarily, it will help many people if you can attach your code to create a new Excel file.
The question I have is that if you create a new Excel file for the first loop, how are you going to process the second loop without using Append Range? Do you need to change the code for the second loop?
I used the same code, followed the same Robot Path suggested in the solution (I used Append range), the only thing I added is I replaced their file input (Inventory Excel File) with a new one I created manually).
To be more precised, I actually created a new Excel File manually (I didn’t do it on StudioX, it can be done in StudioX but that is not the point).
The issue isn’t the solution nor the Robot Path they gave us, it’s the Excel file “Inventory” which contained an empty row as described in my previous answer.
I initially had a blank second row below the headings on the Orders tab but then I changed the formating of the first row to have no borders and this solved the problem. I think in Excel if you have a bottom border on one cell it is the same as an upper border on the cell below. So it seemed to me that this border formating was the content in the second row. I know that when I removed the border formating I no longer had a blank row in my output.
The same happened to me.
This was a great exercise. I did have a similar issue with the blank row.
I managed the exercise ok, but still find the concepts for First/Last row and defining ranges a bit difficult. Best practice for storing data is to keep all columns for the same purpose, without ‘extra’ info in the first few rows, but that of course isn’t the real world!
I found the Autofit Range function and used it on the [Inventory]Orders sheet so that the columns were widened. This gave better visibility of the Part Name in the PDF.
I’m beginning to wonder about having some parts of the automation stored as re-usable bits - or is that more applicable to Studio rather than Studio X? Early days on my learning curve.
Thanks. This tip solved the problem.