For Each Loop challenge - Table Extraction and Add Column to Excel

[TAL_TAL]

Let me describe my challenge:

• I have an Excel file (Input.xlsx) that has rows of URLs in the cells.
• I want to ‘Open Browser’ and ’Type into’ Google, each URL one at a time to bring out the website. (I have been using a ‘For Each Row’) to do this.
• Once website is opened, I will do a ‘Table Extraction’ (modern design) of say 3 columns with data. The scrapped data may take up several rows per column header. The data table created is labelled ‘ExtractData’.
• Finally, I want to write the scrapped data to Excel (Final.xlsx) with corresponding headers.
• But the challenge is I require the Excel file (Final.xlsx) to have a column showing each URL as well.

Excel file – Input.xlsx
URL
Amazon.com : iphone14
Amazon.com : samsung s23

Excel file – Final.xlsx
URL	Model	Price	Star
Amazon.com : iphone	Apple iPhone 11, 64GB, Black	$297.00	4
	Apple iPhone XR, 64GB, Black	$217	3
Amazon.com : samsung s23	SAMSUNG Galaxy S23 Ultra Cell Phone	$1179.00	5
	Samsung Galaxy S20+ 5G 128GB	$234.00	3

I tried ‘Add Column’ after Extract Data but had an error message that the column name already exists. I think it has to do with the For Each loop.

Tried using ‘Add Column’ outside the For Each loop but the URLs did not show up in the Excel file. Thank you for your solutions

[Anil_G (Anil Gorthi)]

@TAL_TAL

Use add column inside loop after the extraction and then merge that data to another datatable using merge data table…

After add datacolumn and before merge…use assign to assign the url to all rows in the extracted table

And use build datatable to get all columns in the merged table along with url

Extdt.Columns("Url").Expression = "’" + currentrow("urlcol").Tostring + "’"

Cheers

[TAL_TAL]

Thank you very much Anil. As I am a newbee, could you guide me in the form of screen shots of the activity suggested. I am grateful for your assistance.