Week 3 - Working With Excel

Excel is a widely used Microsoft software. For example, it is used for billing, data management, analysis, inventory, finance, business tasks, complex calculations, etc. What Excel tasks will you automate and in what situations will data extraction become handy?

For any technical question , or to provide ‌feedback , please create a new topic in our Reboot Your Skill category here .

The Book Sales Report exercise is a good way to learn web data scraping + automation. Nice work!

2 Likes

I could complete the Books Sales Report after a failure due to Total computation. Indeed, VLookup fails to bring some prices as long titles have eliipsis. Then the product formulae failed as some cell were empty.
I needed to add a IF to discard row with empty price. The solution doesn’t have such IF element so I guess the scraping was smarter but it seems like the scraping details are not available afterwards. In case, I missed the obvious, I would be happy to know.

3 Likes

Hi @loic.aigon I noticed this behaviour when the books.toscrape website was not closed before the run. Was this your case?

Hi , i have this error on Pivot Table creation

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Seems that there is no issue creating Pivot table manually . I will double check the automation on Pivot Table step

Hi, Getting this error in Vlookup.

Something went wrong with Excel .
Activity VLookup ( V Lookup X ) failed:

Exception from HRESULT: 0x800A03EC Exception from HRESULT: 0x800A03EC

You can find the activity following this path:

Main > Use Excel File > Excel For Each Row > VLookup.

Excel file path: [string.Format("{0}\Dropbox (ServiceSPAN)\UIPath\Practice - Books Sales Report - Working Documents\Books_Report.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.UserProfile))]

I will try to debug why Vlookup is giving this error.

I get the same error. VLookup: Exception from HRESULT: 0x800A03EC. Any ideas why? when i compare my workflow against solution, i didn’t find difference.

thanks

Hi @Bianca.Dragu, indeed, I did rerun the project, carefully checking that the web page was closed and everything went well. Weird. Thanks anyway for the care.

Hello don’t know how to make the substitue formula work in my write cell activity, may someone help me ?

1 Like

You need to use a string that looks like a formula:
image

1 Like

Can you post the screenshot? What’s in the console?


Thanks, but i still have a questions => 1. How can I make Quantity Sold * Price , if price column is empty. 2. Even if i have followed the correction and you advice, i still have the following issue :

Activity Write Cell ( Write Cell X ) failed:

The data you want to write "=77£49.43" has a wrong format, or Excel is busy. If your data is a formula, make sure you use comma as parameters separator. Please check that you are not editing a value and that no dialog windows are opened. Exception from HRESULT: 0x800A03EC*

You can find the activity following this path:

Main > Use Excel File > Data Extraction > Use Application/Browser > Excel For Each Row > Write Cell.

1 Like

You didn’t take correct column. You have to take the price exclusive of currency i.e. Price, not Extracted Price. Once that said, I faced errors with empty price rows but I used a if and that solved the trick. However, I was reported this error may occur with web site open. Indeed I rerun the project with website closed and no if, and everything went fine. I am guessing that empty price is turned into 0 or the calculation simply disregarded.

Again nice practice for training :+1:. Thanks

There is a mistake here:

And in the PDF also, “CurrentRow Total” instead of “CurrentRow Price”

I’ve been in trouble to do the exercice. At the end it didn’t work even if I had the same project as the correction.
Also why we have to put a number for the “column index” in the VLookup instead of an excel value like all the other fields?

Thanks for the Feedback! The VLookup activity works just as the VLookup formula from Excel. That is why you put a number for the column index.
As a non-Exel user myself, your suggestion is more intuitive for me, but I think for an Excel user the similarity between the action and the formula helps.

1 Like

Hi there, I just went crazy with excel. I love access, and programming with access. But excel is just an horror trip. I spend 3 hours because of that formatting of Pound to a normal number, made my sun also get crazy. I got it, but no no no, spend so much time with something like that. You have to convert the column to text. Then on the column price make a formula: =SUBSTITUTE(E2; “£”; “”)
Then and only then it works, because substitute can only handle with text, not with numbers with characters! Hope this will help that other people don’t get crazy.
Cheers.

2 Likes

I am still working on it. Why is it not possible to cast values of cells and convert the format to make mathematic functions with cells that are string-formated?
Also why is it not possible to check if a column name already exists? It is possible to add a new column but not possible to check if it already exists! Please explain me

I’m receiving a similar Write Cell error that states the data I want to write has the wrong format or Excel is busy. The first row works correctly but then it errors out. Any ideas?

Something went wrong with Excel .
Activity Write Cell ( Write Cell X ) failed:

The data you want to write "=50" has a wrong format, or Excel is busy. If your data is a formula, make sure you use comma as parameters separator. Please check that you are not editing a value and that no dialog windows are opened. Exception from HRESULT: 0x800A03EC*

Cool exercise. I used the Product and formula in excel and it worked well. I also tried using the Substitute formula in excel in order to recreate the formula that was in place. Pretty cool.