Web Scraping With Excel VBA and PowerShell

A no-code route for real tables is Power Query (Data > Get Data > From Web); web scraping with Excel walks through it.

QueryTables.Add is Excel's built-in web query. It takes a connection string that starts with URL;, a destination cell, and which tables to import. This macro imports the 25-row hockey table of a practice sandbox into Sheet 1:

Sub ImportWebTable()
    Dim qt As QueryTable
    Set qt = ThisWorkbook.Sheets(1).QueryTables.Add( _
        Connection:="URL;https://www.scrapethissite.com/pages/forms/", _
        Destination:=ThisWorkbook.Sheets(1).Range("A1"))
    With qt
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "1"                      ' the first <table> on the page; "1,2" imports two
        .WebFormatting = xlWebFormattingNone  ' values only, no page styling
        .Refresh BackgroundQuery:=False       ' nothing is fetched until Refresh
    End With
End Sub

What to expect: 9 columns (Team Name, Year, Wins, Losses, OT Losses, Win %, Goals For, Goals Against, + / -) and 25 team rows. Rerun qt.Refresh to update the same range. WebTables takes a comma-separated list of table index numbers or table id names, and it applies only when WebSelectionType is xlSpecifiedTables (Microsoft's QueryTable.WebTables and QueryTables.Add reference).

The limit is the one in the question: a QueryTable imports only <table> elements that are in the HTML the server sends. Point it at the sandbox's JavaScript page (/pages/ajax-javascript/) and the source holds an empty <tbody> hidden with display:none: the films only arrive after a script calls a JSON URL. That is what macro 3 reads.

When the data is not in a table, fetch the HTML and walk it. This macro reads the 250 countries of a practice sandbox (each in a div.country) into Sheet 1. It uses late binding (CreateObject), so you don't need to add references under Tools > References.

Sub ScrapeCountries()
    Dim http As Object, doc As Object, divs As Object, card As Object
    Dim ws As Worksheet, r As Long, i As Long

    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "GET", "https://www.scrapethissite.com/pages/simple/", False
    http.setRequestHeader "User-Agent", "Mozilla/5.0"
    http.send
    If http.Status <> 200 Then
        MsgBox "HTTP " & http.Status
        Exit Sub
    End If

    Set doc = CreateObject("htmlfile")
    doc.body.innerHTML = http.responseText

    Set ws = ThisWorkbook.Sheets(1)
    ws.Range("A1:C1").Value = Array("Country", "Capital", "Population")
    r = 2
    Set divs = doc.getElementsByTagName("div")
    For i = 0 To divs.Length - 1
        Set card = divs.Item(i)
        If InStr(" " & card.className & " ", " country ") > 0 Then
            ws.Cells(r, 1).Value = Trim(card.getElementsByTagName("h3").Item(0).innerText)
            ws.Cells(r, 2).Value = card.getElementsByTagName("span").Item(0).innerText   ' capital
            ws.Cells(r, 3).Value = card.getElementsByTagName("span").Item(1).innerText   ' population
            r = r + 1
        End If
    Next i
End Sub

What each part does:

• ServerXMLHTTP.6.0 sends the request synchronously (False in Open) and gives you Status and responseText. Unlike MSXML2.XMLHTTP, it doesn't use the Windows internet cache, and it has its own proxy methods (below).
• htmlfile is the MSHTML document object. Assigning the response to body.innerHTML gives you a document you can search with getElementsByTagName and getElementById. Its document mode is old, so newer methods such as querySelectorAll may be missing; filtering by className, as above, works everywhere.
• Collections start at 0 and are read with .Item(i).
• Write to cells in one pass. For thousands of rows, fill a VBA array and assign it to a range at the end; writing cell by cell is the slow part of most VBA scrapers.

Expected result: 250 rows under the header. The macros rely on the page structure (each country's capital and population in its first two <span> elements). We wrote the three macros against Microsoft's documented objects on a Linux server, where VBA does not run; test them in Windows Excel before you depend on them.

A JavaScript page usually loads its data from a URL you can call yourself. On the sandbox's film page, DevTools > Network > Fetch/XHR shows ?ajax=true&year=2015, which returns a JSON array of 16 films. VBA has no built-in JSON parser, so this macro pulls the fields with a regular expression:

Sub ImportFilmsJson()
    Dim http As Object, re As Object, m As Object, ws As Worksheet, r As Long

    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "GET", "https://www.scrapethissite.com/pages/ajax-javascript/?ajax=true&year=2015", False
    http.setRequestHeader "User-Agent", "Mozilla/5.0"
    http.send
    If http.Status <> 200 Then
        MsgBox "HTTP " & http.Status
        Exit Sub
    End If

    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.Pattern = """title"":\s*""([^""]*)"",\s*""year"":\s*(\d+),\s*""awards"":\s*(\d+),\s*""nominations"":\s*(\d+)"

    Set ws = ThisWorkbook.Sheets(1)
    ws.Range("A1:D1").Value = Array("Title", "Year", "Awards", "Nominations")
    r = 2
    For Each m In re.Execute(http.responseText)
        ws.Cells(r, 1).Value = Trim(m.SubMatches(0))
        ws.Cells(r, 2).Value = CLng(m.SubMatches(1))
        ws.Cells(r, 3).Value = CLng(m.SubMatches(2))
        ws.Cells(r, 4).Value = CLng(m.SubMatches(3))
        r = r + 1
    Next m
End Sub

Expected result: 16 rows, from "Spotlight" (2 awards, 6 nominations) down. The pattern matched all 16 films in the endpoint's response on 2026-10-27. A regex depends on the key order, so for nested or changing JSON, import the open-source VBA-JSON module (JsonConverter.bas, GitHub) and use JsonConverter.ParseJson. Scraping hidden APIs shows how to find and replay these requests on other sites.

When there is no JSON request: Power Query and browsers

Power Query has two web functions, and they differ on JavaScript. Excel's From Web generates Web.Page, a legacy function built on Internet Explorer; Power BI Desktop generates Web.BrowserContents, which uses Microsoft Edge's WebView2 control and returns the HTML "as viewed by a web browser". Its WaitFor option waits for a CSS selector or a time before it reads the page, for example Web.BrowserContents(url, [WaitFor = [Selector = "td.film-title", Timeout = #duration(0,0,0,10)]]) (Microsoft: Web connector troubleshooting, Web.BrowserContents). Outside Power BI, drive a real browser from Python or PowerShell (Selenium, Playwright) and write the result to a file Excel opens.

Several pages in a loop

Wrap the request in a function and loop the page parameter, with a pause so you don't hammer the site:

Function GetHtml(url As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "GET", url, False
    http.setRequestHeader "User-Agent", "Mozilla/5.0"
    http.send
    If http.Status = 200 Then GetHtml = http.responseText
End Function

Sub ScrapePages()
    Dim p As Long, html As String
    For p = 1 To 24                                  ' the bound stops the loop: past page 24 the site still answers 200 with an empty table
        html = GetHtml("https://www.scrapethissite.com/pages/forms/?page_num=" & p)
        If Len(html) = 0 Then Exit For               ' an HTTP error (GetHtml returns "" unless the status is 200)
        ' parse html as in ScrapeCountries and append rows
        Application.Wait Now + TimeSerial(0, 0, 2)    ' wait 2 seconds
    Next p
End Sub

Other paging patterns are in web scraping pagination, and table parsing in other languages is in how to scrape a table from a website.

What about Internet Explorer and Selenium in VBA?

Older VBA tutorials drive Internet Explorer through InternetExplorer.Application, which ran the page's JavaScript. Microsoft ended support for the Internet Explorer 11 desktop application on certain Windows versions on June 15, 2022 (Microsoft lifecycle FAQ), so this route is a dead end for new work. SeleniumBasic, the COM wrapper that let VBA drive Chrome or Edge, has had no release since version 2.0.9.0 in March 2016 (GitHub releases), and current browsers need driver versions it was never built for.

Windows ships Windows PowerShell 5.1; PowerShell 7 runs on Windows, macOS and Linux (releases). The script below ran on PowerShell 7.6 on 2026-10-23:

$url  = "https://www.scrapethissite.com/pages/simple/"
$resp = Invoke-WebRequest -Uri $url -UserAgent "Mozilla/5.0" -TimeoutSec 30

# Each country: the name in h3.country-name, then the capital and population spans
$pattern = '(?s)<h3 class="country-name">.*?</i>\s*(?<name>[^<]+?)\s*</h3>.*?country-capital">(?<capital>[^<]*)</span>.*?country-population">(?<pop>\d+)</span>'
$rows = [regex]::Matches($resp.Content, $pattern) | ForEach-Object {
    [pscustomobject]@{
        Name       = $_.Groups['name'].Value.Trim()
        Capital    = $_.Groups['capital'].Value
        Population = [int]$_.Groups['pop'].Value
    }
}
$rows | Export-Csv -Path countries.csv -NoTypeInformation
"$($rows.Count) countries"      # 250 countries

Invoke-WebRequest returns the status code, headers, the HTML in .Content, and the page's links and images in .Links and .Images. In PowerShell 7 it does not return a parsed document: the ParsedHtml property, which used Internet Explorer's engine, exists only in Windows PowerShell 5.1 (our PowerShell 7.6 response object had no such property). Three ways to parse HTML in PowerShell 7:

• Regex, as above, for small, stable pages. Keep patterns narrow: they break when the markup changes.
• A .NET HTML parser such as HtmlAgilityPack or AngleSharp, loaded with Add-Type, for XPath or CSS selectors.
• On Windows only, the same htmlfile COM object VBA uses: $doc = New-Object -ComObject "HTMLFile".

JSON endpoints: Invoke-RestMethod

Invoke-RestMethod parses JSON into objects for you, which makes the JavaScript-page route one line:

$films = Invoke-RestMethod "https://www.scrapethissite.com/pages/ajax-javascript/?ajax=true&year=2015"
$films | Select-Object title, awards, nominations | Export-Csv films-2015.csv -NoTypeInformation
"$($films.Count) films"         # 16 films

Schedule a PowerShell scraper

Save the script as scrape.ps1 and create a daily task:

$action  = New-ScheduledTaskAction -Execute "pwsh.exe" -Argument "-NoProfile -File C:\scripts\scrape.ps1"
$trigger = New-ScheduledTaskTrigger -Daily -At 7am
Register-ScheduledTask -TaskName "DailyScrape" -Action $action -Trigger $trigger

Use powershell.exe instead of pwsh.exe for Windows PowerShell 5.1. Scheduling options on other systems are in schedule web scrapers.

When a site limits requests per IP or shows region-specific content, send the requests through a proxy. In VBA, ServerXMLHTTP takes the proxy and its credentials directly:

Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
http.setProxy 2, "gate.proxyfield.io:7000"                  ' 2 = use this proxy server
http.Open "GET", "https://api.ipify.org", False
http.setProxyCredentials "<login>-country-us-city-new_york", "<password>"
http.send
Debug.Print http.responseText                                ' the proxy's IP

In PowerShell, pass -Proxy and a credential:

$pass = ConvertTo-SecureString $env:PROXY_PASS -AsPlainText -Force
$cred = [pscredential]::new("<login>-country-us-city-new_york", $pass)
Invoke-RestMethod "https://api.ipify.org" -Proxy "http://gate.proxyfield.io:7000" -ProxyCredential $cred

Keep the password out of the workbook or script: read it from an environment variable, as above, or from the Windows Credential Manager. More options are on the PowerShell proxy setup page. Scrape public pages only and follow the site's terms; is web scraping legal? covers the rules.

Pulling web data into Excel or scripts? ProxyField residential proxies: 412M+ residential IPs in 195+ countries, HTTP, HTTPS and SOCKS5, from $1.95/GB with no subscription; start with the Easy Trial (1 GB for $2.95). Traffic never expires, and all purchases are final.