Yes—you can scrape a small amount of publicly accessible HTML into desktop Excel with VBA. The dependable pattern is to request one page, verify the response, parse only the elements you need, and write the results to an explicit worksheet range. This guide shows that workflow, explains when Excel’s built-in Web connector is a better choice, and covers failures caused by changed markup, blocked requests, missing fields and Excel-version differences.
Use this method only where the site’s published terms and access rules permit your intended collection. A page being visible in a browser does not, by itself, establish permission for automated requests.
Before you write code
Use desktop Excel, not Excel for the web
VBA authoring and execution require desktop Excel. Microsoft states: “Although you can’t create, run, or edit VBA (Visual Basic for Applications) macros in Excel for the web, you can open and edit a workbook that contains macros.” You can store a macro-enabled workbook online and edit its cells in the browser, but run the scraper on a supported desktop installation with macros allowed by your organisation and file security settings.
Decide whether Power Query already solves the job
Excel’s Web connector uses Power Query to import website data, detect tables and refresh a connection. Try it first when you need a routine import that fits the connector’s detected shape. VBA is more appropriate when the workbook must perform custom actions around the import—for example, writing to a particular report area, combining the result with other macro steps, or applying workbook-specific rules.
Recommended Free Tools
#1 Best Overall
| Question | Web connector (Power Query) | VBA scraper |
|---|---|---|
| Can it shape the returned data? | Use Power Query transformations and detected tables. | Write your own extraction and worksheet logic. |
| Can it refresh? | Microsoft documents refresh for Web connector connections, subject to the page and Excel environment. | Run the macro when you choose, or call it from a larger workbook workflow. |
| How much custom workbook integration? | Limited to the query/refresh design. | High: VBA can validate, format, route and combine output. |
| What happens when markup changes? | Query steps may need adjustment. | Your selectors and parsing code may need adjustment. |
Plan a small, permitted extraction
- Name the page. Start with one stable URL that you can inspect manually.
- List the fields. Decide the exact output columns, such as title, price and publication date.
- Inspect the returned HTML. Browser-rendered text may be produced by JavaScript and may not appear in the initial response that VBA receives.
- Check access conditions. Read the site’s terms, robots guidance and any authentication or rate limits that apply to your use.
- Set a low request volume. A small test makes it easier to diagnose a missing field without placing unnecessary load on the site.
How the VBA scraper is organised
Keep the macro in five separate stages: request, response validation, HTML parsing, worksheet output and error reporting. Separating them means a timeout is distinguishable from a selector that no longer matches.
- Request: open the URL and send an HTTP GET.
- Validate: check the HTTP status and confirm that expected content exists.
- Parse: locate elements by tags, IDs or classes that you have verified in the page source.
- Output: write headers and values to known cells.
- Report: show a useful message for failed requests and absent fields rather than silently writing misleading blanks.
The example below uses late binding for the Microsoft XML HTTP object and the HTML document object, so you do not have to set a compile-time reference. Component availability, timeout behaviour, HTML parsing and 32/64-bit compatibility can differ between Office installations; validate the code in your target environment and consult the current Microsoft VBA and object-model references.
Step-by-step: fetch and parse one page
1. Create the workbook
- Open the page in a desktop version of Excel.
- Press Alt+F11 to open the Visual Basic Editor.
- Choose Insert > Module.
- Paste the macro below.
- Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
2. Add the macro
Option Explicit
Public Sub ScrapeOnePage()
Const PAGE_URL As String = "https://example.com/"
Dim http As Object
Dim doc As Object
Dim ws As Worksheet
Dim titleText As String
Dim firstParagraph As String
On Error GoTo RequestOrParseError
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set http = CreateObject("MSXML2.XMLHTTP.6.0")
http.Open "GET", PAGE_URL, False
http.setRequestHeader "User-Agent", "Excel VBA scraper for permitted use"
http.send
If http.Status <> 200 Then
MsgBox "The page returned HTTP status " & http.Status & ". No cells were changed.", vbExclamation
Exit Sub
End If
If Len(http.responseText) = 0 Then
MsgBox "The response was empty. Check the URL, access rules and whether the page requires JavaScript.", vbExclamation
Exit Sub
End If
Set doc = CreateObject("HTMLFile")
doc.Open
doc.Write http.responseText
doc.Close
titleText = FirstElementText(doc, "h1")
firstParagraph = FirstElementText(doc, "p")
ws.Range("A1:B1").Value = Array("Field", "Value")
ws.Range("A2:B2").Value = Array("URL", PAGE_URL)
ws.Range("A3:B3").Value = Array("H1", ValueOrMissing(titleText))
ws.Range("A4:B4").Value = Array("First paragraph", ValueOrMissing(firstParagraph))
If titleText = "" Or firstParagraph = "" Then
MsgBox "The request succeeded, but one or more expected elements were missing. Inspect the returned HTML and update the parser.", vbInformation
Else
MsgBox "Page fetched and values written to " & ws.Name & ".", vbInformation
End If
Exit Sub
RequestOrParseError:
MsgBox "Scraper error " & Err.Number & ": " & Err.Description, vbCritical
End Sub
Private Function FirstElementText(ByVal doc As Object, ByVal tagName As String) As String
Dim nodes As Object
Set nodes = doc.getElementsByTagName(tagName)
If nodes.Length > 0 Then
FirstElementText = Trim$(nodes.Item(0).innerText)
Else
FirstElementText = ""
End If
End Function
Private Function ValueOrMissing(ByVal valueText As String) As String
If Len(Trim$(valueText)) = 0 Then
ValueOrMissing = "[missing]"
Else
ValueOrMissing = valueText
End If
End FunctionReplace the example URL only with a page you are permitted to access. The sample deliberately extracts the first <h1> and first <p>; those are demonstration fields, not a promise that every site uses them.
3. Run and inspect the result
- Return to Excel and confirm that a sheet named Sheet1 exists, or change the worksheet name in the code.
- Press Alt+F8, select ScrapeOnePage and choose Run.
- Check cells A1:B4. Compare the values with the page you inspected manually.
- Test a page where an expected element is absent. The macro should show
[missing]and a warning instead of pretending the value was found.
Extracting several records
Once the one-page test works, select a collection that has a repeated, stable element such as product cards or article rows. Use the document’s tag collection, loop through its items, and write one record per worksheet row. Before doing that, identify the containing element and the child fields in the actual returned HTML. A CSS class copied from a visual browser inspector may not be present in the response if JavaScript builds the page after load.
Rank #2
For maintainability, put each field in a small function, return an explicit missing value, and keep the worksheet-writing code separate from parsing. Add a run timestamp and source URL to your output so a later reader can identify which request produced each row.
Validate, refresh and maintain the scraper
Compare a few values manually
After every parser change, compare several output cells with the source page. Test ordinary values, missing values and characters such as accented letters. Do not treat a successful HTTP status as proof that the desired data was returned: a consent page, bot challenge or error document can also return HTML.
Watch for structural changes
Selectors based on presentation classes are fragile. Prefer a stable ID or semantic element when the site provides one, and keep selectors in clearly named constants. When the site redesigns its markup, update the parser and rerun your manual checks.
Keep failures visible
Record the URL, status code and a short error message. Stop before clearing existing data when the request fails. If you intentionally retry, use a deliberate retry policy rather than a tight loop.
Troubleshooting common failures
“User-defined type not defined” or a compile error
The code may contain an early-bound type or a missing reference. The sample uses late-bound objects, but your Office build may still lack a required component. Check references in Tools > References, or adapt the HTTP and parser objects supported by your managed Windows environment.
HTTP status is not 200
Inspect the status and response body before parsing. A redirect, authentication requirement, rate limit, forbidden response or server error may be involved. Confirm the URL and the site’s access conditions; do not bypass an access control that you are not authorised to bypass.
The request succeeds but values are blank
The selector may be wrong, the element may be absent, or JavaScript may populate it after the initial HTML response. Save or inspect http.responseText, compare it with the page source, and choose a source that actually contains the data you need.
Characters are garbled
The response encoding may not match the parser’s assumption. Check the page’s declared charset and the HTTP response, then use an encoding-aware method supported by your Office environment. Verify accented and non-Latin text before processing many rows.
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #4
The macro hangs
A synchronous request waits for the server. A slow page, network interruption or unreachable host can make Excel appear frozen. Test connectivity, use a client and timeout approach supported by your environment, and avoid running large batches from the user interface without progress reporting.
Excel for the web does nothing
This is expected: Excel for the web can open and edit a workbook containing macros, but it cannot create, run or edit VBA macros. Open the file in desktop Excel to execute it.
When to choose VBA, Power Query or another workflow
- Choose Power Query Web when table detection and refresh produce the shape you need with little custom workbook logic.
- Choose VBA when extraction must trigger workbook actions, write to fixed report ranges or follow rules that are easier to express procedurally.
- Reconsider both when the target requires a browser-rendered session, interactive login or a site’s dedicated API. Your workbook may need a permitted upstream export instead.
Microsoft’s VBA reference covers programming tasks, samples and the Excel object model; that reference page reports a last-updated date of July 11, 2022, so verify details against the Office version you operate today.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Or skip the browser setup
If your goal is a clean image or PDF of a page rather than rows in worksheet cells, ScreenshotNeo provides a one-request website screenshot API and MCP server. It accepts cookie and consent banners like a visitor, then removes more than 60 known consent platforms, newsletter popups and chat widgets before capture; each cleanup step can be turned off. Bot checks, CAPTCHAs, blank pages, timeouts, failed loads and cache hits are not billed, and the response identifies the page verdict and billing result in headers.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Use the API documentation at https://screenshotneo.com/docs/ for the complete option list. A cURL request is:
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpThe equivalent Python request is:
import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)And Node.js:
const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);ScreenshotNeo also supports full-page and element captures, device presets and custom viewports, retina scale, PDF settings, custom CSS and JavaScript, click-before-capture, waits, request blocking, headers, cookies, user agents, timezone and geolocation, transparent backgrounds, resizing, selectable cache TTLs, signed links, asynchronous jobs, bulk capture for up to 100 URLs per call, usage reporting and an OpenAPI specification. An MCP server exposes take_screenshot, get_page_info and capture_pdf to Claude, Cursor and other MCP clients.
| Plan | Allowance | Price |
|---|---|---|
| Free | 1,000 shots/month | $0, no card |
| Starter | 3,000 shots | $5 |
| Growth | 15,000 shots | $15 |
| Pro | 60,000 shots | $39 |
| Scale | 250,000 shots | $99 |
| Business | 1,000,000 shots | $249 |
Yearly billing gives two months free, and every feature is included on every plan. Create a free ScreenshotNeo account to get 1,000 screenshots a month with no card; paid plans start at $5 for 3,000.
Further reading
A preview of Microsoft Excel 2019 VBA and Macros includes website-scraping and web-query material, which may be useful as optional background reading. It is not required for this tutorial, and the preview does not establish a current edition, price or retail listing.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Frequently Asked Questions
Can I run this VBA scraper in Excel for the web?
No. Excel for the web can open and edit a workbook that contains macros, but macro creation, editing and execution require desktop Excel.
Why does a page look complete in Chrome but return no data to VBA?
The browser may execute JavaScript, pass a challenge or accept a consent dialog before displaying the content. Inspect the initial response and use a permitted source that contains the fields you need.
Should I scrape an entire site with this macro?
No. Start with a small, permitted extraction, respect the site’s access conditions and add deliberate pacing, validation and failure handling before expanding the job.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
