Web Scraping in Excel: Power Query, VBA, and API Methods

TL;DR:

• Use Power Query when a page exposes a stable HTML table or JSON endpoint; it gives Excel a refreshable, documented transformation.
• Use VBA when workbook users need a button-driven import or a controlled CSV handoff. Avoid making VBA parse changing page markup.
• For JavaScript-rendered or traffic-validated pages, acquire the content with an API first, then let Excel transform the returned data.
• The most maintainable design separates acquisition from analysis: the source service returns a stable schema, and Excel owns filtering, joins, pivots, and charts.

Web scraping in Excel can mean four very different jobs: copying a table once, refreshing a public table, calling a JSON API, or feeding a workbook from a page that requires browser rendering. Choosing the tool by page behavior is more reliable than choosing it by familiarity.

This guide covers Power Query, VBA, and an API-first path. Examples are limited to public data that the project is permitted to collect. Do not use them to access private, authenticated, personal, or restricted information without explicit authorization.

Choose the method before building the workbook

The practical rule is simple: keep Excel responsible for tabular transformation, not browser emulation. When a web page is the presentation layer rather than the data source, look for a documented endpoint or put an acquisition service in front of the workbook.

Method 1: Import a visible table with Power Query

Microsoft's supported flow is Data > From Web, followed by selecting a detected table in Navigator and loading it into the workbook. The full sequence appears in Microsoft's web import guide.

Step-by-step

1. Open a workbook and select Data.
2. Choose From Web in the Get & Transform Data group.
3. Paste the permitted public page URL.
4. Select the table in Navigator.
5. Choose Transform Data if columns need cleaning, or Load to write it directly to a sheet.
6. Rename the query and table so their purpose is obvious.
7. Select Refresh when the source needs to be fetched again.

Power Query stores the transformation steps. This matters because a reproducible query can remove columns, set types, split text, and join reference data the same way on every refresh. Microsoft also documents that a loaded web table can be updated with Query Refresh in the same Excel refresh workflow.

When From Web returns no useful table

The page may render its data with JavaScript after the initial HTML arrives. It may also expose the values through an XHR or fetch request instead of an HTML table. In those cases, the Navigator result can be empty even though the browser shows rows.

Open browser developer tools only to identify a permitted, documented data endpoint—not to defeat access controls. If a stable JSON endpoint exists, Power Query can call it directly. If traffic validation or browser rendering is required, use the API-first method later in this guide.

Method 2: Call JSON with Power Query M

Power Query's Web.Contents function performs HTTP requests and returns a binary response that can be passed to Json.Document. Microsoft documents its request options, including Headers, Content, RelativePath, Query, and ApiKeyName, in the Web.Contents reference.

Prerequisites

• Excel with Power Query
• A Scrapeless API token entered through the workbook's Web API credential dialog
• A permitted public target URL
• A known JSON response envelope

The following Power Query M block is a prerequisite-gap example because it needs the reader's token and target. It calls the Universal Scraping API, reads the returned HTML from data, and produces a one-row table that can feed a later parser or audit sheet.

let
    Endpoint = "https://api.scrapeless.com",
    TargetUrl = Excel.CurrentWorkbook(){[Name="AuthorizedTargetUrl"]}[Content]{0}[Column1],
    Payload = Json.FromValue([
        actor = "unlocker.webunlocker",
        proxy = [country = "ANY"],
        input = [
            url = TargetUrl,
            jsRender = [
                enabled = true,
                response = [type = "html", options = []]
            ]
        ]
    ]),
    Raw = Web.Contents(
        Endpoint,
        [
            RelativePath = "api/v2/unlocker/request",
            Headers = [
                #"Content-Type" = "application/json"
            ],
            Content = Payload,
            ApiKeyName = "x-api-token"
        ]
    ),
    Envelope = Json.Document(Raw),
    Checked = if Envelope[code] = 200 and Envelope[data] <> null
        then Envelope[data]
        else error "The acquisition response did not contain page data",
    Output = #table(
        {"source_url", "html"},
        {{TargetUrl, Checked}}
    )
in
    Output

After creating the query, choose Web API when Excel asks for credentials and paste the token there. ApiKeyName specifies the parameter name while keeping the secret out of the M source. The credential behavior is illustrated in Microsoft's secure API-key example.

The current API request shape and JavaScript options are also available in the Scrapeless JS Render documentation.

Turn returned HTML into stable Excel columns

Loading a complete HTML document into one cell is a diagnostic bridge, not the final dataset. Production workbooks should receive a compact schema.

For example:

There are two clean ways to reach that contract:

• Ask the acquisition layer to return structured fields.
• Parse returned HTML outside Excel, then expose JSON or CSV to Power Query.

Both reduce the workbook's dependence on page markup. A workbook that expects six named fields is easier to test than one containing dozens of selectors and text-cleaning steps.

If the page needs JavaScript rendering before those fields exist, test the Universal Scraping API with one authorized URL before redesigning the workbook.

Method 3: Use VBA for a controlled CSV handoff

VBA is useful when an analyst needs a button that imports a file into a known sheet. Keep the web acquisition outside the macro and let the macro consume a stable CSV URL. This avoids coupling a workbook to changing HTML.

Prerequisites

• Desktop Excel with macros enabled under the organization's policy
• A permitted HTTPS endpoint that returns UTF-8 CSV
• A worksheet named ImportedData
• A named range CsvEndpoint containing the endpoint URL

The block is prerequisite-gap because the endpoint and workbook policy are specific to the reader's environment.

Option Explicit

Public Sub ImportCsv()
    Dim endpoint As String
    Dim target As Worksheet
    Dim query As QueryTable

    endpoint = ThisWorkbook.Names("CsvEndpoint").RefersToRange.Value
    Set target = ThisWorkbook.Worksheets("ImportedData")

    target.Cells.ClearContents

    Set query = target.QueryTables.Add( _
        Connection:="TEXT;" & endpoint, _
        Destination:=target.Range("A1"))

    With query
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFilePlatform = 65001
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
    End With
End Sub

This macro is deliberately narrow: it imports a CSV contract and leaves acquisition, rendering, and access logic to the service that owns them. If the endpoint needs custom authentication, Power Query's credential store is often a better fit than embedding a token in VBA.

Microsoft documents that Workbook.RefreshAll refreshes external data ranges and PivotTable reports in the Excel VBA RefreshAll reference. That can support a workbook-level refresh button after each query has been configured and tested.

API-first web scraping in Excel

An API-first design has two stages:

1. Acquire and validate the page: render JavaScript where necessary, handle traffic validation, and confirm that the expected content was returned.
2. Transform and analyze in Excel: shape the response into columns, join lookup tables, build pivots, and publish charts.

The Universal Scraping API is built for the first stage. Power Query remains the workbook-facing connector. This boundary lets teams change the acquisition method without rebuilding every formula and chart.

Use the Scrapeless pricing page to compare the service cost with the engineering time needed to operate browser infrastructure. For a broader view of response formats that fit Excel pipelines, see the Universal Scraping API response-format update.

Common problems and fixes

Navigator shows no tables

The page is likely not exposing a stable HTML table in the initial response. Look for a permitted JSON or CSV source. If values appear only after JavaScript, use an acquisition layer that returns the completed content.

Refresh returns a privacy-level error

Power Query isolates data sources according to privacy settings. Review the workbook's source configuration and avoid combining private organizational data with a public source unless policy allows it.

A query works on one computer but not another

Check Excel edition, authentication settings, named ranges, macro policy, and gateway requirements. Credentials are environment-specific and should not travel inside the workbook file.

The workbook loads a challenge page

Add a content-level validation before transformation. Require the expected JSON field, CSV header, or page marker. Reject any response that does not satisfy that contract.

Column types change after refresh

Apply explicit data types in Power Query after the source step. Keep currency symbols, locale-specific separators, and missing values out of numeric columns.

Refresh takes too long

Reduce the data before it reaches Excel. Filter by date or identifier at the source, request only needed fields, and load intermediate queries as connection-only when worksheets do not need them.

From a workbook prototype to a production feed

A useful progression is:

1. Prove one URL and one row manually.
2. Create a Power Query with explicit column names and types.
3. Add content validation and an error sheet.
4. Move rendering and HTML parsing into the acquisition service.
5. Publish JSON or CSV with a versioned schema.
6. Schedule collection outside Excel and keep workbook refresh focused on analysis.

This keeps the spreadsheet useful without turning it into an unattended web browser. The workbook becomes a consumer of clean data, which is a role Excel handles well.

Conclusion: let Excel analyze, not emulate a browser

Power Query is the default choice for refreshable HTML tables and JSON APIs. VBA is valuable for controlled, user-triggered imports. When a permitted source depends on JavaScript or traffic validation, use an acquisition API and give Excel a stable data contract.

To build that boundary, create a Scrapeless account, test the Universal Scraping API with one public URL, define the fields the workbook needs, and make those fields the refresh contract.

Frequently Asked Questions

Can Excel scrape a website without code?

Yes. Power Query's From Web connector can import many visible HTML tables through a graphical workflow. It is best suited to stable public pages that expose the table in their initial HTML.

Why does Power Query show an empty page?

The values may be rendered by JavaScript after the initial response, loaded from a separate endpoint, or replaced by a traffic-validation page. Inspect the source behavior and choose the acquisition method accordingly.

Is Power Query better than VBA for web scraping?

Power Query is usually better for refreshable data transformation and credential management. VBA is useful for workbook buttons and controlled file imports, but it is a fragile place to maintain HTML parsing.

Can Power Query call a JSON API?

Yes. Web.Contents can fetch a response, and Json.Document can parse it into records, lists, and tables. Use the credential dialog instead of storing API secrets in M code.

How can a workbook handle JavaScript-rendered pages?

Use a rendering-capable acquisition service to return HTML, JSON, or CSV, then connect Power Query to that output. This keeps browser behavior outside the spreadsheet.

How often should Excel refresh scraped data?

Match refresh frequency to business need, source permission, and service capacity. For scheduled or high-volume collection, run acquisition outside Excel and let the workbook refresh from a prepared dataset.