Excel RPA task - Complex

Help
, , , ,
1

Hello,

I have a seemingly complex RPA task that I cannot seem to complete. I have done a lot of searching on this forum, Google and Youtube and am still really struggling. There is a lot of work that occurs before and after the excel manipulation which is complete. I am just stuck on this excel piece to complete my project.

Essentially I am trying to combine two complex data sets into one data set. Below you will find the general workflow.

Here is what I am trying to achieve

  1. Open an excel file 1
  2. Identify the Customer name (Cell B6 in the workbook, long string of data, cant determine how to identify the customer name using RPA)
  3. Delete existing data, this is a macro enabled file. I must retain the macro row and leave one blank row in the end
  4. Open excel file 2 (Filter for the Customer name)
  5. Copy the Customer data
  6. Open excel file 1
  7. Copy the macro row for the number of cells I need to copy from excel file 2, (including one blank row at the end)
  8. Paste the Customer data from excel file 2 into excel file 1
  9. Click a check button macro
  10. Route the file into a passed or failed folder

I need to loop this for every customer

Any help would be greatly appreciated …

2

Hi @MF.RPA

It is a slightly complicated task but not too difficult.
Given that this is a macro-enabled file and you need to click the button to run the macro, you might want to consider a hybrid approach, leveraging the excel activities and also UIAutomation activities.

Can you please share if you’re using Studio or StudioX to automate this?

3

Hi, @RPAForEveryone I am using UI Path Studio. Any other suggestions would be greatly appreciated.

4

@RPAForEveryone Would you be able to provide suggestions as to how I can complete my task?

thank you,

5

Hi,
just evaluate, do you really need to paste data in a same excel file? what is a purpose for this action?

I think it is much easier to find customer in excel 1
find customer related information in excel 2
Paste all needed data in excel 3

You can go through each customer using For each row in excel 1
If you need to have updated customer list in excel 1 after completing process, you can overwrite it using data from excel 3.

6

@xagen
I need to copy data from excel file 2 and paste the data into a macro enabled template (excel file 1). I cannot access the macro to replicate the check in excel file 1.

7

Than I don’t understand a flow and the issue.

You need to write data in excel 1 using data from excel 2?
Is place for paste data specific and static, or it is dynamic?

Did you try to Join tables?

8

The challenge is that I need to first determine the number of rows from the first data set, open the excel template and create new rows for the number of rows that I need to paste into the template from the data set.

The data that is pasted is dynamic based on each Customer.

9

Without sharing any sensitive information, will you be able to share a screenshot of the macro-enabled file? Specifically, which button needs to be pressed to run the macro?

10

@RPAForEveryone
Here is a generic screenshot of a sample template.

image
image1635×490 14.5 KB

11

To determine nr of rows you can create additional pivot table in excel 2 with fields “Customer”, “Count Customer”.
Read this table and find how many rows you need for the selected customer.

or after reading table and filtering it for selected customer you can use smthg like row.count

12

Any other tips would be appreciated. I am still suck on this problem.