How to Switch Excel ODC Connections Between Power BI Test and Production
Question details
The user needs a method to dynamically switch multiple Excel OLE DB connections between test and production Power BI datasets.

- 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.
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).
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.
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.
Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor. Go to 'Insert' > 'Module' to create a new script module.
Create a macro that loops through 'ActiveWorkbook.Connections'. Inside the loop, check if the connection is an OLE DB connection type.
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.
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.

Maintain Separate ODC File Folders
If VBA string replacement causes provider errors, maintain separate folders for production and test ODC files and toggle the file paths.
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. Download WPS Office: Visit the official WPS website and click on the 'Download' button to get the free installation package.
- 2. Install the Application: Run the downloaded installer and follow the simple on-screen instructions to set up WPS Office on your device.
- 3. Open Your Workbooks: Launch WPS Spreadsheet and open your existing macro-enabled workbooks to continue your data analysis seamlessly.

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.




