How to Create a PivotTable from an OLEDB Connection Using VBA
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.

- 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.
Ensure your macro security settings allow VBA code execution and verify that your SQL query syntax is correct before running the script.
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.
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.
Use the ActiveWorkbook.PivotCaches.Create method. Set SourceType:=xlExternal and assign your defined connection object or string to the SourceData parameter.
Call the CreatePivotTable method on your newly created PivotCache object, specifying the TableDestination (e.g., Worksheets("Sheet1").Range("A1")) and a TableName.

Create PivotCache Using a Standard Database Range for Internal Tables
If your OLEDB connection struggles to query tables within the same workbook (ThisWorkbook), bypassing OLEDB and referencing the range directly avoids internal memory and locking errors.
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. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Enable the Developer Tools: Go to the 'Developer' tab to access the VBA editor if you prefer creating PivotTables programmatically.
- 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. 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.

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.




