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