Excel Automation with StudioX - Sales Overview

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. blank1

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.

Hello,

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.

1 Like

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?

A very interesting and practical exercise. successfully done.
image

Hello,

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.

Thank you.

1 Like

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.

6 Likes

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.

I didn’t use the Append range method from the solution and used copy/paste range instead. When I did this, I didn’t encounter the blank second row issue. Although now that I know what append row does, I definitely use it in the future, haha. Since I didn’t use it, I had to use an additional Find first/last row activity for the Inventory file to ensure that the data gets copied in the next empty cell instead of in the last row. Thankfully less tricky than the Table extraction exercise, lol.

Hello to everybody

Almost successfuly passed the exercise. The only issue I have noticed is a name of pdf :thinking:
My pdf file name is 'Overview_yyyy-11-26
image

Save Excel File As PDF looks as below:

image

Any advice is more than welcome how to properly write a date in the name of pdf.

Thanks in advance for your support
BR
Maciej

Maciej,
The date value that is part of the PDF file name is coming from the project notebook. The project notebook is automatically managed by Studio X but I suspect somehow it has been corrupted. Create a new project and redo the same exercise and it should solve your problem.
Nanji

1 Like

@ nanji200

Thanks for your advice

Note that all that was done on Excel (except sending email), could have been done using VBA and macros on Excel and formulas.