VBA + ADODB Queries Against Power BI Lose Rows Without Telling You

A recurring pattern on the Fabric Community forums: a developer writes VBA that connects to a Power BI workspace via the MSOLAP provider, runs a DAX query with SUMMARIZECOLUMNS and TREATAS filters, and gets back fewer rows than the same query produces in DAX Studio. The connection string looks correct — Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/[workspace];Initial Catalog=[dataset] — and the query executes without error. The ADODB recordset populates. CopyFromRecordset writes data to the worksheet. Everything appears to work.

Except the row count is wrong. In one documented case, 11,000 rows in DAX Studio became roughly 6,000 in Excel. No ADODB error. No truncation warning. The VBA subroutine completes normally, and the developer only catches the discrepancy by comparing totals after the fact.

This is not a DAX problem. The query itself is sound — wrapping results in TOPN(500000,...) with date parameters constructed from Excel cell values using FORMAT and DATE functions. The issue lives in the layers between the XMLA endpoint and the Excel worksheet: the MSOLAP provider's default behavior, the ADODB cursor configuration, and how CopyFromRecordset handles large recordsets. Each layer can independently clip your result set, and none of them will tell you about it.

When VBA opens an ADODB connection with the MSOLAP provider against the Power BI XMLA endpoint, several defaults work against large result sets.

First, the cursor type matters. Most VBA examples use adOpenForwardOnly (value 0) with adLockReadOnly. This is correct for performance, but forward-only cursors rely on the provider's internal fetch buffer. The MSOLAP provider does not expose a straightforward MaxRows property the way a SQL Server OLEDB provider does, but it does have internal buffering behavior that can interact poorly with how CopyFromRecordset reads data.

Second, CopyFromRecordset itself has a silent row limit. The method accepts an optional MaxRows parameter — ws.Range("A2").CopyFromRecordset rs, MaxRows — but when omitted, it writes rows until the recordset appears exhausted. The problem is that CopyFromRecordset may stop writing if it encounters a buffer boundary or if the recordset's internal cursor loses position. This does not raise an error; it simply returns the number of rows actually written, which most VBA scripts ignore.

The fix is explicit. After CopyFromRecordset completes, check the return value:

``vba Dim rowsCopied As Long rowsCopied = ws.Range("A2").CopyFromRecordset(rs) Debug.Print "Rows written: " & rowsCopied ``

If rowsCopied is less than expected, iterate the remainder manually:

``vba Do While Not rs.EOF ' Write remaining rows field by field rs.MoveNext Loop ``

Alternatively, replace CopyFromRecordset entirely with a row-by-row loop when you suspect truncation. It is slower — noticeably so above 50,000 rows — but it eliminates the buffer ambiguity. For datasets in the tens of thousands, the difference is seconds, not minutes.

Before any data flows, your VBA connection must pass through three distinct permission gates. Miss any one, and the behavior ranges from a clear authentication error to a connection that succeeds but returns partial or empty results.

Layer 1: Capacity and XMLA endpoint setting. The workspace must be on Premium, Premium Per User (PPU), or Fabric capacity. The XMLA Endpoint tenant setting must be at least Read. Navigate to Admin Portal → Capacity Settings → Power BI Premium → select your capacity → Workloads → XMLA Endpoint. If this is set to Off, ADODB connections will fail with a connection refused error. If your tenant admin has disabled the "Allow XMLA endpoints and Analyze in Excel with on-premises semantic models" integration setting, all XMLA connections are blocked regardless of capacity configuration.

Layer 2: Workspace role and Build permission. The account authenticating through MSOLAP needs Build permission on the target semantic model. Contributor, Member, or Admin workspace roles grant this implicitly. A Viewer role does not. Without Build permission, the XMLA endpoint rejects query operations — but the error message may surface as a generic MSOLAP connection failure rather than a clear permissions denial.

Layer 3: Authentication protocol. The MSOLAP provider against Power BI requires Azure AD (Microsoft Entra ID) authentication. VBA with ADODB does not natively support the interactive MFA flow that tools like SSMS handle through "Active Directory - Universal with MFA." This forces one of two approaches: use a service principal with a client secret (appending User ID=app:@;Password= to the connection string), or acquire an OAuth2 bearer token externally and pass it via the connection string's Password field with User ID left empty. Neither approach is straightforward in VBA without additional COM references or external helper libraries.

Service principals add a fourth constraint: the tenant admin must enable the "Allow service principals to use Power BI APIs" setting, and the service principal must be added to a security group that has access to the workspace.

The MSOLAP client library version installed on the machine running the VBA code is a silent variable that shapes behavior. Power BI's XMLA endpoint requires MSOLAP 17.x or later. Older versions may connect but return incomplete metadata or truncated result sets without error.

Check your installed version by looking at the registered OLEDB providers in the registry at HKEY_CLASSES_ROOT\CLSID or by running a connection test that queries $SYSTEM.DISCOVER_XML_METADATA. In practice, the fastest check is to open a command prompt and run:

`` reg query "HKLM\SOFTWARE\Classes\MSOLAP" /s | findstr Version ``

If you are on MSOLAP 16.x or earlier, update to the latest client libraries from Microsoft's Analysis Services client libraries page. The monthly update cadence means even a version from three months ago may lack fixes for specific XMLA endpoint behaviors.

A related issue: machines with both 32-bit and 64-bit Office installations, or with multiple versions of the MSOLAP provider registered, can exhibit provider confusion. VBA in 32-bit Excel will load the 32-bit MSOLAP provider. If only the 64-bit provider is current, your VBA code runs against an outdated provider without warning. Confirm the bitness match by checking which msolap*.dll your Excel process actually loads — Process Monitor from Sysinternals will show you the exact DLL path during connection.

The ADOMD.NET library (version 19.x+) is an alternative for .NET-based automation, but it is not directly callable from VBA without a COM wrapper. For teams that need reliable programmatic access to Power BI datasets from Excel, the more maintainable path is often Power Query with parameterized M queries, or the Power BI REST API's ExecuteQueries endpoint — both of which sidestep the MSOLAP provider's quirks entirely.

When VBA + ADODB + MSOLAP becomes a debugging treadmill, the Power BI REST API's ExecuteQueries endpoint offers a cleaner path. Available since 2021, this endpoint accepts DAX queries over HTTPS and returns JSON — no XMLA, no OLEDB provider, no cursor management.

The endpoint URL follows this pattern:

`` POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries ``

The request body contains your DAX query:

``json { "queries": [{"query": "EVALUATE SUMMARIZECOLUMNS(...)"}], "serializerSettings": {"includeNulls": true} } ``

From VBA, you call this using MSXML2.XMLHTTP or WinHttp.WinHttpRequest. Authentication requires a bearer token obtained from Microsoft Entra ID — the same OAuth2 flow, but now you are making a standard HTTPS call instead of relying on the MSOLAP provider to handle auth internally. The token acquisition can use MSAL (via a small .NET helper or PowerShell script that writes the token to a file VBA reads) or a service principal client credentials flow.

The critical advantage: ExecuteQueries returns a complete JSON result with an explicit row count. There is no cursor, no buffer, no silent truncation. If the query times out or exceeds the 100,000-row response limit, you get an explicit error — not a partial result that looks complete.

The 100,000-row limit per query is the main constraint. For larger extracts, paginate with TOPN and OFFSET or split the query by date range. This is more work than a single ADODB recordset, but the failure mode is explicit rather than silent — and that difference matters when your finance team is reconciling numbers at month-end.

MetricSign monitors Power BI dataset refresh and query health, and can detect when scheduled refreshes fail or are delayed — but it cannot catch when a VBA script silently drops 5,000 rows. That detection has to happen in your code, either by validating row counts against expected totals or by switching to an API that fails loudly.

If you must use VBA with ADODB against the XMLA endpoint — and sometimes organizational constraints make it the only option — apply these controls to prevent silent data loss.

Confirm the MSOLAP provider version is 17.x or later, matching your Excel bitness. Run the registry check described above before writing any VBA. Update the client library if it is more than two months old.

Set the ADODB connection and recordset properties explicitly. Do not rely on defaults:

``vba conn.ConnectionTimeout = 120 rs.CursorLocation = adUseClient ' Forces client-side cursor rs.CursorType = adOpenStatic ' Allows full traversal rs.LockType = adLockReadOnly ``

Using adUseClient with adOpenStatic forces the entire result set to be materialized client-side before CopyFromRecordset runs. This eliminates the forward-only buffer boundary problem at the cost of higher memory usage — acceptable for datasets under 100,000 rows.

Always capture and validate the CopyFromRecordset return value. Compare it against rs.RecordCount (which is available with static cursors, unlike forward-only cursors where RecordCount returns -1). If they differ, log the discrepancy and fall back to row-by-row iteration.

For authentication, prefer service principal credentials over interactive user tokens in automated scenarios. Store the client secret in Windows Credential Manager and retrieve it via VBA's CredRead API call rather than hardcoding it. Rotate secrets on a 90-day cycle.

Finally, wrap your DAX query in an explicit TOPN with a limit higher than your expected result set, and include a COUNT measure in a separate query to validate completeness. If the TOPN limit matches the actual row count, you know the result was capped — an imperfect safeguard, but better than silent truncation with no signal at all.