Pivot Table Issues

How to Create a PivotTable from an OLEDB Connection Using VBA

Steve KSteve K Nov 10, 2026 869 views

Question details

The user needs to create a PivotTable using VBA from an OLEDB connection but is experiencing failures when the connection queries internal tables.

How to Create a PivotTable from an OLEDB Connection Using VBA
Product
Microsoft Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Executing a VBA macro to generate a PivotCache and PivotTable using an OLEDB connection for a multi-table SQL query.
Observed behavior
The VBA code successfully creates a PivotTable for external sources but throws an error when the OLEDB connection points to tables located within ThisWorkbook.
Before you start

Ensure your macro security settings allow VBA code execution and verify that your SQL query syntax is correct before running the script.

Solution 1Recommended

Use an Existing OLEDB Connection for the PivotCache

This solution ensures the PivotCache is correctly configured to use an explicit OLEDB connection string and command text, which is required for external data queries.

When building a PivotTable from an external data source via VBA, you must explicitly pass the workbook connection to the PivotCaches.Create method. A malformed connection string or command text will cause the PivotTable generation to fail.

1
Define the Connection String

In your VBA module, declare a string variable for your OLEDB connection. Ensure it specifies the correct provider (e.g., Microsoft.ACE.OLEDB.12.0) and points to your data source.

2
Create the PivotCache

Use the ActiveWorkbook.PivotCaches.Create method. Set SourceType:=xlExternal and assign your defined connection object or string to the SourceData parameter.

3
Add the PivotTable

Call the CreatePivotTable method on your newly created PivotCache object, specifying the TableDestination (e.g., Worksheets("Sheet1").Range("A1")) and a TableName.

Use an Existing OLEDB Connection for the PivotCache
Independent Testing: Always test your OLEDB connection string and SQL query independently using a standard ADODB connection in VBA before applying it to the PivotTables.Add method.

Easily Analyze Data and Automate Tasks with WPS Spreadsheet

WPS Spreadsheet offers powerful data analysis tools, including intuitive PivotTables and robust VBA macro support, allowing you to easily summarize complex datasets without dealing with complex connection string errors.

  1. 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your dataset.
  2. 2. Enable the Developer Tools: Go to the 'Developer' tab to access the VBA editor if you prefer creating PivotTables programmatically.
  3. 3. Insert a PivotTable: Navigate to the 'Insert' tab and click 'PivotTable' to easily create a cache from your internal tables without manual OLEDB configuration.
  4. 4. Design Your Report: Drag and drop your desired fields into the Rows, Columns, and Values areas in the PivotTable Fields pane to instantly build your report.
Fully compatible with Microsoft Excel (.xlsx) formats and VBA macros.User-friendly PivotTable wizard for quick and accurate data summarization.Lightweight performance, ensuring fast processing even with large internal datasets.Free to use with a familiar interface that requires zero learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA OLEDB connection fail when pointing to ThisWorkbook?

Excel frequently encounters memory locking or driver conflicts when attempting to query its own open workbook using OLEDB. For internal tables, it is generally safer to use the xlDatabase SourceType.

Can I use multiple tables for a PivotTable via an OLEDB connection?

Yes. You can use a SQL JOIN statement within the CommandText property of your OLEDB connection to combine data from multiple tables into a single PivotCache.

What is the correct SourceType for an external OLEDB connection in VBA?

When creating a PivotCache connected to an external OLEDB or ODBC data source via VBA, the SourceType parameter must be set to xlExternal.

How do I refresh a PivotTable connected to an OLEDB source using VBA?

You can refresh the data by calling ActiveSheet.PivotTables("YourPivotTableName").PivotCache.Refresh within your VBA macro.