Stop Using Web Queries — Excel Macros *Can* Pull Data from Websites (Here’s How)

Most Excel trainers still teach you to use Data > From Web and call it 'automation.' They’re wrong. That feature doesn’t run in background macros, fails silently on modern sites with JavaScript rendering, and breaks every time a site updates its HTML structure. If your macro relies on it, you’re building on sand.

The Problem

You’ve got a supplier list in A2:C12: company names, URLs, and last-checked dates. You need live unit prices from each vendor’s public product page — say, Acme Corp’s /products/industrial-valves page or TechNova Ltd’s /pricing/2024-sheet-metal. You tried recording a macro while clicking Data > From Web. It worked once. Then failed when you ran it again — no error, just blank cells in column D. Worse: your manager asked for this every morning at 8:15 AM, and now you’re copy-pasting manually again.

A (Company)	B (URL)	C (Last Checked)	D (Price)
Acme Corp	https://acmecorp.com/products/industrial-valves	2024-03-12
TechNova Ltd	https://technovaltd.com/pricing/2024-sheet-metal	2024-03-10
GlobalFab Inc	https://globalfab.io/catalog/pneumatic-fittings	2024-03-08
NexaTools	https://nexatools.net/items/hydraulic-cylinders	2024-03-05
PrecisionMachining Co	https://precisionmachining.co/prices/steel-brackets	2024-03-01
Orion Components	https://orioncomp.dev/products/led-control-modules	2024-02-28

The Solution

This works: open VBA Editor (Alt+F11), paste the code below into a new module, then run GetWebPrices. It uses XMLHTTP — not Internet Explorer, not Power Query — so it runs silently, fast, and without pop-ups. It targets price elements by CSS class (e.g., .price-current or #unit-price). And yes, it handles basic redirects and 404s without crashing.

1. Open VBA Editor: Alt+F11 → Insert → Module
2. Paste this code (replace ".price-current" with the actual CSS selector from your target site — inspect element to find it):
   

   Sub GetWebPrices()
       Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
       Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
       Dim i As Long, html As Object, priceText As String
       
       For i = 2 To lastRow
           If ws.Cells(i, 2).Value <> "" Then
               Set html = CreateObject("MSXML2.XMLHTTP")
               html.Open "GET", ws.Cells(i, 2).Value, False
               html.setRequestHeader "User-Agent", "Mozilla/5.0"
               html.send
               
               If html.Status = 200 Then
                   priceText = ExtractPrice(html.responseText, ".price-current")
                   ws.Cells(i, 4).Value = IIf(priceText = "", "N/A", priceText)
               Else
                   ws.Cells(i, 4).Value = "HTTP " & html.Status
               End If
           End If
       Next i
   End Sub
   
   Function ExtractPrice(htmlStr As String, selector As String) As String
       Dim regex As Object, matches As Object
       Set regex = CreateObject("VBScript.RegExp")
       regex.Global = True
       regex.IgnoreCase = True
       regex.Pattern = "]*class=[""'].*?" & Replace(selector, ".", "\\.") & ".*?[""'][^>]*>([\d\.,]+)"
       Set matches = regex.Execute(htmlStr)
       If matches.Count > 0 Then ExtractPrice = matches(0).SubMatches(0) Else ExtractPrice = ""
   End Function

3. Run the macro: Alt+F8 → select GetWebPrices → Run
4. Add error logging (optional): Insert ws.Cells(i, 5).Value = html.StatusText before the Next i line to log status messages in column E.

After running, column D fills like this:

A (Company)	B (URL)	C (Last Checked)	D (Price)	E (Status)
Acme Corp	https://acmecorp.com/products/industrial-valves	2024-03-12	$45,200	OK
TechNova Ltd	https://technovaltd.com/pricing/2024-sheet-metal	2024-03-10	$18.75	OK
GlobalFab Inc	https://globalfab.io/catalog/pneumatic-fittings	2024-03-08	$129.99	OK
NexaTools	https://nexatools.net/items/hydraulic-cylinders	2024-03-05	$2,145	OK
PrecisionMachining Co	https://precisionmachining.co/prices/steel-brackets	2024-03-01	$8.42	OK
Orion Components	https://orioncomp.dev/products/led-control-modules	2024-02-28	$32.00	OK

Going Further

You’ll hit walls if you try this on sites that require login or render content via JavaScript (like React or Vue apps). But there are workarounds.

• For dynamic sites: Use SeleniumBasic instead of XMLHTTP — it launches real Chrome/Firefox, waits for JS to load, then scrapes. Download SeleniumBasic, reference it in VBA (Tools > References > Selenium Type Library), then use driver.Get URL and driver.FindElementByCss(".price").Text.
• To avoid rate limiting: Add Application.Wait Now + TimeValue("00:00:02") inside the loop — two-second pause between requests. Sites like Alibaba.com will throttle or block rapid-fire calls.
• For authentication: Add headers like html.setRequestHeader "Cookie", "sessionid=abc123; token=xyz789" — grab those from browser DevTools > Network tab after logging in manually.
• Auto-refresh on open: Put Call GetWebPrices inside Workbook_Open() in ThisWorkbook — just remember to disable it during editing.

Surprising tip: The regex pattern in ExtractPrice works better than trying to parse full HTML with DOM objects. It’s faster, lighter, and handles malformed markup. I learned that after three hours debugging htmlDoc.getElementById failures on a poorly coded supplier site.

When NOT to Use This

Don’t reach for this macro if:

• The target site blocks non-browser user agents — even with User-Agent header, some return empty or CAPTCHA pages. Test first in Postman or curl.
• You need data behind a login *and* don’t have access to session tokens. SeleniumBasic won’t help if MFA is required each time.
• Your workbook is shared with users who have macro security set to 'Disable all macros with notification' — they’ll get a warning, and most click 'Disable' without reading.
• The site changes its HTML weekly. One client’s vendor updated their price class from .price to .unit-price — broke the whole report. Build a fallback: check for both selectors in sequence.

If your data source offers an API (even a simple REST endpoint), use that instead. Excel’s WEBSERVICE() function + FILTERXML() is safer and doesn’t require enabling macros.

Keyboard Shortcuts

Shortcut	Action	Use Case
Alt+F11	Open VBA Editor	Fastest way to edit or debug your macro
Ctrl+G	Open Immediate Window	Type ?html.Status mid-macro to debug HTTP response
F5	Run macro	From inside VBA Editor — no need to Alt+F8 every time
Ctrl+Break	Stop running macro	Critical when stuck in infinite loop or slow request
Alt+Q	Close VBA Editor	Back to Excel — cleaner than clicking X