VBA & Macro Problems

How to Switch Excel ODC Connections Between Power BI Test and Production

Emma BrownEmma Brown Nov 10, 2026 868 views

Question details

The user needs a method to dynamically switch multiple Excel OLE DB connections between test and production Power BI datasets.

How to Switch Excel ODC Connections Between Power BI Test and Production
Product
Excel
Device & OS
not provided
Scenario
Managing Power BI dataset connections in Excel and needing an efficient way to toggle between test and production environments.
Observed behavior
The user is seeking a reliable VBA script or workbook setting approach to change ODC connection strings dynamically based on specific dataset IDs.
Before you start

Ensure you have the exact dataset IDs for both your test and production Power BI environments, and verify that your Excel workbook is saved as a macro-enabled format (.xlsm).

Solution 1Recommended

Use VBA and Named Cells to Toggle Connection Strings

Store your dataset IDs in named cells and use a VBA macro to loop through and update all OLE DB connections in the workbook automatically.

This method involves updating the connection string of the OLEDBConnection object directly. By referencing cells that contain the environment IDs, you can switch environments with a single macro execution.

1
Create Named Cells

In your workbook, designate two cells for your dataset IDs. Name one cell 'ptr_ID_PROD' (for production) and the other 'ptr_ID_TEST' (for testing), and enter the corresponding Power BI dataset IDs.

2
Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor. Go to 'Insert' > 'Module' to create a new script module.

3
Write the Connection Loop

Create a macro that loops through 'ActiveWorkbook.Connections'. Inside the loop, check if the connection is an OLE DB connection type.

4
Replace the Source ID

Use the VBA 'Replace' function on the 'OLEDBConnection.Connection' property to swap the old dataset ID with the target dataset ID referenced from your named cells.

5
Refresh Connections

At the end of your script, include the command 'ActiveWorkbook.RefreshAll' to apply the updated connection strings and fetch the data from the new environment.

Use VBA and Named Cells to Toggle Connection Strings
Test Connection Behavior Carefully: Connection strings and OLE DB provider behaviors can vary between workbooks and versions. It is highly recommended to test this macro in an isolated copy of your workbook first.
Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

While advanced Power BI ODC connections are deeply integrated into the Microsoft ecosystem, WPS Office serves as an excellent, lightweight alternative for your daily spreadsheet, VBA macro, and data analysis needs without the hefty subscription fees.

  1. 1. Download WPS Office: Visit the official WPS website and click on the 'Download' button to get the free installation package.
  2. 2. Install the Application: Run the downloaded installer and follow the simple on-screen instructions to set up WPS Office on your device.
  3. 3. Open Your Workbooks: Launch WPS Spreadsheet and open your existing macro-enabled workbooks to continue your data analysis seamlessly.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .xlsm, .csv)Supports standard VBA macros and advanced spreadsheet formulasLightweight design ensuring faster load times and smoother performanceFamiliar user interface for a zero-learning-curve transition
microsoft office alternative - wps office

Frequently Asked Questions

Why does changing the OLE DB connection string via VBA fail sometimes?

An error might occur if the OLE DB provider settings differ significantly between test and production, or if the connection requires re-authentication. Reviewing the Remarks section of the OLEDBConnection.Connection documentation can help identify specific provider limitations.

Can I use ODC files instead of modifying connection strings directly?

Yes, maintaining separate production and test folders with their respective pre-configured ODC files is a highly reliable alternative. You can use a workbook setting to switch the target file path rather than parsing and replacing the entire connection string.

Where is the best place to store dataset IDs for the VBA script?

It is best practice to store them in named cells (e.g., ptr_ID_PROD and ptr_ID_TEST) on a dedicated, hidden configuration worksheet. This prevents users from accidentally editing or deleting the IDs.