Excel data processing question

Hello everyone,

I am struggling with an excel data processing problem. I have 400 excel workbooks for different Customers containing no data. I have one excel data file exported from an internal system. For each Customer file I need to paste in the corresponding data from the data file.

Please note: The Customer name is in the title of the Customer excel file and the Customer name is available in column E of my data file for matching purposes.

Lastly, I need to click a macro button and save the file to a specific folder based on pass or failure of the macro check.

Could someone help me get started with this activity?

@MF.RPA

Can you share a sample data excel file?

Thanks

  • Read the exported excel file as datatable
  • for each row, take the name of the matching client in the E column
  • open a workbook over the path : building the path as a sum of the client name and the extension of the file
  • append the row in this workbook

I am a little lost when you say take the name of the matching client in the E column and open workbook over path.

This is as far as I have gotten:

ok assign the name of the client in a variable, for example client_name = row.Item()
then build a path string for example client_excel_path = root_path + "" + client_name + “.xlsx”
then open a workbook with this path, and append the row inside it

There are 400 different clients. Also, I need to add Customer type as a filter in the data set.

I sent you a message with additional details.