topic RE: MS Excel VBO in Product Forum

MS Excel VBO

NikitaSatpathy — Thu, 29 Feb 2024 08:23:00 GMT

My requirement is to read and excel and write only the filtered data based on a condition to another sheet in the same excel.

For example- If Column A = "A particular value", then just write that entire row into another sheet having that column value.

Like if Supplier = "Nutricia" , only write that row into another sheet and ignore rest of the rows.

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

sonuiiml — Thu, 29 Feb 2024 13:19:00 GMT

My approach would be the below sequence of steps

-> Get the whole data as collection
-> Apply filter collection using [Supplier]='Nutricia' and get the filtered collection in a separate collection
-> Create a new sheet in excel
-> Dump the filtered collection

------------------------------
Susamay Halder
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: MS Excel VBO

NikitaSatpathy — Fri, 01 Mar 2024 10:44:00 GMT

Hi Susamay,

Thanks for your reply, I tried the exact same thing, but instead of copying only 1 row, the entire excel data is being copied to the new sheet.

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

sonuiiml — Fri, 01 Mar 2024 13:21:00 GMT

Hi Nikita,

I dont necessarily understand the use of loop in the above diagram. If I were to design, it would look something like this

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: MS Excel VBO

LeonardoSQueiroz — Sun, 03 Mar 2024 21:32:00 GMT

Hello Nikita,

In the scenario presented, it is not necessary to use a loop, in addition, apparently the loop is being performed in the "new" collection, try the flow presented by Susana.

Regards,

------------------------------
Leonardo Soares
RPA Developer Tech Leader
América/Brazil
------------------------------

RE: MS Excel VBO

NikitaSatpathy — Mon, 04 Mar 2024 04:49:00 GMT

Hi Susamay,

Let me try this option, also can you please provide an example as to how to provide value in the "Filtered collection". Please advise if the below condition is correct.

Earlier I was using loop to identify how many rows have the same data.

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

faheemsd — Mon, 04 Mar 2024 08:09:00 GMT

Dear Nikita,

Please try this Expression in the Filter "[Supplier]='Nutricia'"

Collection name not required in the Filter filed and you only need to provide the column name.

Try the filter expression I have given then you will get the expected results.

If you want to store the Filter results in a different collection, then you can create a new collection and store the output result in it.

------------------------------
SYED FAHEEM
RPA Developer
Arab Financial Services
Manama
------------------------------

RE: MS Excel VBO

NikitaSatpathy — Mon, 04 Mar 2024 10:55:00 GMT

Hi Syed, Susamay

I have used the exact same expression, its throwing error

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

faheemsd — Mon, 04 Mar 2024 11:52:00 GMT

Dear Nikita,

Please copy and paste this value in filter expression '[Supplier]="Nutricia"' without any modification.

Kindly check your collection column name, is it exact Supplier or are there any spelling mistakes or spaces are there?

------------------------------
SYED FAHEEM
RPA Developer
Arab Financial Services
Manama
------------------------------

RE: MS Excel VBO

NikitaSatpathy — Mon, 04 Mar 2024 12:51:00 GMT

Hi Syed,

I copied the exact same expression from your chat. I am still getting the same error.

Also there is no spelling mistake or space for Supplier in my excel.

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

sonuiiml — Mon, 04 Mar 2024 12:57:00 GMT

Hi Nikita,

Good Day!

Please be mindful of using single quotes and double quotes. So the value of the attribute which you want to filter should be in single quotes and the whole filter expression should be in double quotes. And also the spaces needs to be removed
So here in this case it would like (refer to the screenshot as well)
"[Supplier]='Nutricia'"

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: MS Excel VBO

faheemsd — Mon, 04 Mar 2024 13:26:00 GMT

Dear Nikita,

Please try this expression "[Supplier]='Nutricia'" hopefully it will work.

Also please try to validate the expression to make sure that you will not encounter any error.

------------------------------
SYED FAHEEM
RPA Developer
Arab Financial Services
Manama
------------------------------

RE: MS Excel VBO

NikitaSatpathy — Tue, 05 Mar 2024 05:19:00 GMT

Hi Susamay,

I tried this exact expression and there was no error.

But the condition did not work and the entire data of first sheet got copied into the new sheet. This was my issue since the beginning

Check the attachment, "Export" sheet is the initial sheet and "Export Result" is my new sheet where i wanted to have only the row having Supplier = "Nutricia" from the first sheet.

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

faheemsd — Tue, 05 Mar 2024 10:21:00 GMT

Dear Nikita,

Could you please try this expression "[Supplier] Like '%Nutricia%'".

------------------------------
SYED FAHEEM
RPA Developer
Arab Financial Services
Manama
------------------------------

RE: MS Excel VBO

NikitaSatpathy — Tue, 05 Mar 2024 10:58:00 GMT

Hi Syed,

I tried the expression,still the same, all the rows are getting copied in the new sheet

------------------------------
Nikita Satpathy
------------------------------

RE: MS Excel VBO

sonuiiml — Tue, 05 Mar 2024 13:01:00 GMT

Hi Nikita,
Please find the attached solution. In the sales by supplier file the bot would make a separate worksheet with Nutirica Data only. These were the below steps performed. Screenshot attached as well

1. Get the data to a collection

2. Filter the collection NOT directly from the excel sheet

3. Write the filtered collection in a new sheet.
Its working good
The process and the source file is attached. Can you please import it and try running it?
If this solution solves the problem the please vote the answer as best solution.

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

RE: MS Excel VBO

faheemsd — Tue, 05 Mar 2024 14:17:00 GMT

Dear Nikita,

Please make sure that you're writing the filtered collection data into the excel using Write collection in MS Excel VBO.

if possible, create a new sheet and write data in newly created Sheet.

------------------------------
SYED FAHEEM
RPA Developer
Arab Financial Services
Manama
------------------------------