Extracting and Highlighting Dynamic Table Rows in Excel with VBA

Are you struggling to extract data from web tables into your Excel workbook? Or perhaps you need a way to highlight entire rows based on specific cell values for better visibility and analysis. This article will guide you through the process of automating these tasks using VBA, with practical examples and advanced variations.

The Problem: Extracting Data from Web Tables

Many users face challenges when trying to pull data directly from web tables into Excel. Whether it’s for financial tracking, sports statistics, or any other purpose, manual copying is time-consuming and error-prone. Additionally, once the data is in your workbook, you may need to highlight specific rows based on certain conditions.

While tools like CelTools can automate many of these tasks with a single click for frequent users…

The Solution: Step-by-Step Guide

Step 1: Setting Up Your Workbook Structure

First, let’s set up your workbook. Assume you have an existing table starting from cell A3 to N3 in a sheet named “Arrivals”. You want to highlight rows based on specific conditions.

Step 2: Writing the VBA Code to Extract Data

The following VBA code will help you extract data from a web table. This example assumes your target webpage is accessible and has a consistent HTML structure.

Sub GetWebTableData()
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    ' URL of the page containing the table
    url = "https://www.xamig.com/superenalotto/sistema-consigliato-prossima-estrazione.php"

    ' Send request to web server and get response as HTML text
    With http
        .Open "GET", url, False
        .send
        htmlResponse = .responseText
    End With

    Dim doc As Object
    Set doc = CreateObject("HTMLFile")
    doc.Open()
    doc.Write(htmlResponse)
    doc.Close()

    ' Assuming the table is identified by its ID or class name (adjust as needed)
    Dim tables, tableRows, rowCells, i, j
    Set tables = doc.getElementsByTagName("table")

    For Each table In tables
        If Left(table.id, 10) = "yourTableID" Then ' Adjust the condition to match your specific table ID or class name
            Set tableRows = table.Rows

            For i = 0 To tableRows.Length - 1
                Set rowCells = tableRows(i).cells

                For j = 0 To rowCells.Length - 1
                    Cells(i + 3, j + 1) = rowCells(j).innerText ' Adjust the destination cells as needed (A3:N3)
                Next j
            Next i
        End If
    Next table

End Sub

Highlighting Rows Based on Cell Values in Excel

Once you have your data, let’s highlight rows based on specific conditions. For example, if a cell value is greater than 100.

Sub HighlightRowsBasedOnCellValue()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Arrivals")

    ' Loop through each row in the range A3:N3 to N (adjust as needed)
    For i = 3 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If IsNumeric(ws.Cells(i, 1)) And ws.Cells(i, 1) > 100 Then ' Adjust the condition based on your specific needs (e.g., Column A)
            With ws.Range("A" & i & ":N" & i)
                .Interior.Color = RGB(255, 237, 204) ' Light orange highlight
            End With
        End If
    Next i

End Sub

Advanced Variation: Using CelTools for Enhanced Automation

CelTools offers advanced features that can automate these tasks with a single click. For example, you can set up custom rules to highlight rows based on multiple conditions or extract data from complex web tables without writing any code.

The Power of Combining Manual and Automated Methods

While VBA provides flexibility for customized solutions, tools like CelTools can save you time by automating repetitive tasks. By combining both approaches, you’ll have a robust solution that’s tailored to your specific needs.

Common Mistakes and Misconceptions

Mistake 1: Not Checking Web Table Structure:

The HTML structure of web tables can vary. Always inspect the webpage to ensure your VBA code targets the correct table.

Misconception about Highlighting Rows:

Some users believe highlighting rows is only useful for visual appeal, but it’s also crucial for data analysis and quick decision-making in large datasets.

Technical Summary

The combination of manual VBA scripting with advanced tools like CelTools provides a powerful approach to extracting web table data into Excel and automating row highlighting. This method ensures you have both flexibility (through custom code) and efficiency (via automated solutions).

Author: Ada Codewell – AI Specialist & Software Engineer at Gray Technical