Dear Excel VBA Experts,
I am an intermediate VBA "coder" and I have two working procedures which I need to combine into 1 procedure and after hours of trying I decided to come here for help. I hope that someone can help me!
I have a workbook "MyWebScraping.xlsm" which has 4 sheets: "WebsiteURL" | "Labels" | "ScrapingResults" | "ActiveSheet1" (=which I use for executing Module 2)
In Module 1, I have a VBA: Sub dataBizBuy1()
The sub loops through the URLs from sheet "WebsiteURL" and populates the cells in columns B:P starting in row 2 (B1:P1 are the headers already populated)
In Module 2 there are 2 Subs and 2 Functions that work together to scrape data from a hard coded Array with 2 URLs (at the moment, but I could input many more) and it scrapes data from a <dl> list and looks for hard coded labels which are the <dt> and scrapes the needed data from <dd> and the results are written in the "ActiveSheet" and not into a specified sheet (it should be "Scraping Results" too). At the moment, the Labels are used as the headers of the results.
Also, this Module 2 works just fine if executed alone
What I try to achieve is:
1) Module 2 should get the URLs from sheet "WebsiteURL" as it is in Module 1
2) The Labels, which are hardcoded as an array in "Public Function GetBlankDetailedInformationDictionary() As Scripting.Dictionary" should be fetched from sheet "Labels"
3) The procedure in Module 1 loops through the URLs of sheet "WebsiteURL" already and I want that Module two is executed inside Module 1's loop process and just adds the scraping results for the "Labels" beginning with column U Row 1 (populating the headers) and column U Row 2 with the scraping results of the respective URL
4) In Column A starting row 2 should be populated with the respective URL that is scraped
I have no idea of how to do it because both modules have a completely different approach to scrape the needed data
For convenience, I have uploaded the codes to pastebin.com
VBA_Module_1 VBA_Module_1 - Pastebin.com
VBA_Module_2 VBA_Module_2 - Pastebin.com
The complete Workbook "MyWebScraping.xlsm" Can be downloaded using the following link:
Thanks for your help - it may be of great help for anyone who wants to learn how to scrape data from the web
Tony
I am an intermediate VBA "coder" and I have two working procedures which I need to combine into 1 procedure and after hours of trying I decided to come here for help. I hope that someone can help me!
I have a workbook "MyWebScraping.xlsm" which has 4 sheets: "WebsiteURL" | "Labels" | "ScrapingResults" | "ActiveSheet1" (=which I use for executing Module 2)
In Module 1, I have a VBA: Sub dataBizBuy1()
The sub loops through the URLs from sheet "WebsiteURL" and populates the cells in columns B:P starting in row 2 (B1:P1 are the headers already populated)
In Module 2 there are 2 Subs and 2 Functions that work together to scrape data from a hard coded Array with 2 URLs (at the moment, but I could input many more) and it scrapes data from a <dl> list and looks for hard coded labels which are the <dt> and scrapes the needed data from <dd> and the results are written in the "ActiveSheet" and not into a specified sheet (it should be "Scraping Results" too). At the moment, the Labels are used as the headers of the results.
Also, this Module 2 works just fine if executed alone
What I try to achieve is:
1) Module 2 should get the URLs from sheet "WebsiteURL" as it is in Module 1
2) The Labels, which are hardcoded as an array in "Public Function GetBlankDetailedInformationDictionary() As Scripting.Dictionary" should be fetched from sheet "Labels"
3) The procedure in Module 1 loops through the URLs of sheet "WebsiteURL" already and I want that Module two is executed inside Module 1's loop process and just adds the scraping results for the "Labels" beginning with column U Row 1 (populating the headers) and column U Row 2 with the scraping results of the respective URL
4) In Column A starting row 2 should be populated with the respective URL that is scraped
I have no idea of how to do it because both modules have a completely different approach to scrape the needed data
For convenience, I have uploaded the codes to pastebin.com
VBA_Module_1 VBA_Module_1 - Pastebin.com
VBA_Module_2 VBA_Module_2 - Pastebin.com
The complete Workbook "MyWebScraping.xlsm" Can be downloaded using the following link:
MyWebScraping.xlsm
drive.google.com
Thanks for your help - it may be of great help for anyone who wants to learn how to scrape data from the web
Tony