Excel VBA - How to combine 2 VBA procedures into 1

alpha2007

New Member
Joined
Jun 20, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top