Create new excel (dynamic name) for web scraped information for each seperate web account

Hi,

My question is: How do I create a new excel file as part of an each row function? I.e. looking up and scraping data and then pasting this in a newly created excel file.

I have created an automated process which starts with an Excel file with 500 company codes, using a for each row function it then finds a company code enters this company code into a website, looks up the information on the website for this company and then scrapes the company information from the website. I am looking to add an additional step: for each of the unique company codes I’d like to create a new excel file where the scraped data for that company is then pasted, thus at the end of the process having 500 excel files with unique names and unique scraped company information. Is there a way to do this? The closest solution that I’ve found is: Creating Excel File with Dynamic Names but I can’t quite figure out how to get this to work. Another solution that I’ve seen is a combination between create file and the assign function.

Many thanks in advance to anyone who can help me out!

What’s the structure of the scrapped data? Does it change all the time for different companies? Do you want them to be stored in a single cell value all unstructured?

You can create another DataTable using ‘Build Data Table’ activity and then use ‘Add Data Row’ to add your scrapped data as a new row.

Let me know if that helps.

Thanks,
Rammohan B.

Thank you for your response. The structure of the scraped data is the same for each company. However, it is a lot of data and I want to do separate VBA analysis on each of the companies so I want to store the data for each company in a (automatically newly created) separate Excel file (instead of as a new row), and this is where the trouble lies, I don’t know how to do so.

This is what I’d like my process to look like (in bold the part that I’m still missing):
Look up company code in Excel -> Paste company code in IE -> Look up company info -> Scrape company info -> Create new excel file with unique name (for each loop that is done a new file has to be created)-> Paste company info in newly created excel file -> Save file -> Loop (using for each row).

Thanks,
Merel

In that case, Your for each loop should consist of these activities:

  1. ‘Build Data Table’. By doing this each iteration of the loop will have a new DataTable.
  2. ‘Add Data Row’. This will add your values to specific row within the created DataTable.
  3. ‘Write Range’. This will create a new excel file. Make sure that the name you are giving it unique in each case.

Does that make sense?

Thanks,
Rammohan B.

Thank you for your response. It looks like the write range option does not allow for creating 500 different excel files with a unique name, or is there a way to do so? What I have now is an Excel application scope with a file name that does not yet exists with a write range function, but after it has created the file in the first loop it over writes the data with the next company data.

Thanks,
Merel

As i mentioned previously. You need to give a name that changes for every iteration and should be unique. In this case may be you can append your company code to the excel filename that you are providing within scope activity only if your company code is unique.

Like this,
"RPA-tests"+ company_code +".xlsx" where company_code is the string variable having your company name pulled from that iteration of the row value.

Thanks,
Rammohan B.