Web Scraping with Excel VBA

Web scraping involves extracting data from websites, and it can be done in Excel VBA using libraries like Microsoft HTML Object Library and Microsoft Internet Controls. The idea is to send a request to a webpage, fetch the HTML content, and then extract the relevant data (such as tables, lists, or other elements).

Requirements:

1. Microsoft HTML Object Library
2. Microsoft Internet Controls

Make sure you enable the necessary references in your VBA editor:

• Go to Developer Tab > Visual Basic > Tools > References.
• Check Microsoft HTML Object Library and Microsoft Internet Controls.

Steps for Web Scraping:

1. Create an Internet Explorer Object: This allows us to interact with a webpage in the background.
2. Navigate to the Website: Use the Navigate method of the Internet Explorer object to load the webpage.
3. Wait for the Page to Load: This ensures the page content is fully loaded before we attempt to scrape it.
4. Extract the HTML Content: Once the page is loaded, we can access the DOM (Document Object Model) to extract specific data.
5. Close the Browser: After scraping the required data, it’s good practice to close the browser.

Web Scraping VBA Code:

Sub WebScrapingExample()
    ' Step 1: Declare variables
    Dim IE As Object
    Dim HTMLDoc As Object
    Dim URL As String
    Dim data As Object
    Dim i As Integer
    ' Step 2: Set the URL of the website to scrape
    URL = "https://example.com" ' Replace with your target URL
    ' Step 3: Create a new Internet Explorer instance
    Set IE = CreateObject("InternetExplorer.Application")
    ' Step 4: Set Internet Explorer to be invisible (no UI)
    IE.Visible = False
    ' Step 5: Navigate to the URL
    IE.Navigate URL
    ' Step 6: Wait for the page to load completely
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents ' Allow the page to load
    Loop
    ' Step 7: Get the document object (HTML content)
    Set HTMLDoc = IE.document
    ' Step 8: Extract data (for example, from a table with the id "data-table")
    Set data = HTMLDoc.getElementsByTagName("tr") ' Adjust selector based on the data you need
    ' Step 9: Loop through the table rows and extract data
    For i = 0 To data.Length - 1
        ' Example: Extracting text from each cell in the row
        Debug.Print data.Item(i).Children(0).innerText ' Column 1
        Debug.Print data.Item(i).Children(1).innerText ' Column 2
        ' Continue for other columns as needed
    Next i
    ' Step 10: Close Internet Explorer
    IE.Quit
    ' Clean up
    Set IE = Nothing
    Set HTMLDoc = Nothing
    Set data = Nothing
End Sub

Explanation of the Code:

1. Variables Declaration:
   • IE: This is the Internet Explorer object used to load the webpage.
   • HTMLDoc: This is the HTML document object that allows us to interact with the page’s DOM.
   • URL: The URL of the webpage that we want to scrape data from.
   • data: An object that stores the HTML elements (in this case, table rows <tr>).
2. Internet Explorer Object:
   • We create a new instance of Internet Explorer using CreateObject(« InternetExplorer.Application »).
   • IE.Visible = False makes the browser invisible so that the scraping process runs in the background.
3. Navigating to the URL:
   • The IE.Navigate URL command sends a request to the specified webpage and loads its content.
4. Waiting for the Page to Load:
   • Do While IE.Busy Or IE.ReadyState <> 4 ensures that the page is fully loaded. The code waits for the browser to finish loading before proceeding.
5. Accessing the HTML Document:
   • After the page is fully loaded, Set HTMLDoc = IE.document stores the DOM of the webpage into the HTMLDoc object, which we will use to access the content.
6. Extracting Data:
   • In this case, we are looking for <tr> elements (table rows). You can adjust the selector depending on the structure of the page you’re scraping.
   • HTMLDoc.getElementsByTagName(« tr ») returns all <tr> elements on the page, which typically represent rows in a table.
7. Looping Through Rows:
   • We loop through each row (data.Length – 1) and extract the text content of each cell in the row using innerText.
8. Closing the Browser:
   • IE.Quit closes the Internet Explorer instance after the scraping process is complete.
9. Cleaning Up:
   • Set objects to Nothing to release memory and resources.

Notes:

• Adjust the Data Extraction: Depending on the structure of the webpage, you may need to adjust the selector (getElementsByTagName) or use other methods like getElementById, getElementsByClassName, or querySelector.
• Error Handling: Add error handling to ensure the code runs smoothly in case the page structure changes or there are network issues.
• Page Load Time: If the page contains dynamic content loaded with JavaScript, you may need to wait for it to finish loading. In such cases, using Selenium might be more effective than Internet Explorer automation.

Advanced Considerations:

1. Scraping Data from Multiple Pages: If you need to scrape data from multiple pages (pagination), you can modify the code to loop through each page URL.
2. Handling Dynamic Content: If the data you need is loaded dynamically with JavaScript, you might want to use Selenium, as Internet Explorer will not render the dynamically loaded data like modern browsers.
3. Saving Data to Excel: After scraping, you can write the extracted data into Excel cells by using something like Cells(i + 1, 1).Value = data.Item(i).Children(0).innerText.