Forum Discussion
Dynamically changing ODC Connection
This is where I ended up .... with two named cells having the two different Power BI Dataset IDs
Option Explicit Public Sub SwapProdTest() Call SwapConnections("PROD", "TEST") End Sub Public Sub SwapTestProd() Call SwapConnections("TEST", "PROD") End Sub Private Sub SwapConnections(str_From As String, str_To As String) Dim str_Old, str_New, str_Connection, str_Response As String Dim cn As WorkbookConnection Dim oledbCn As OLEDBConnection For Each cn In ActiveWorkbook.Connections Set oledbCn = cn.OLEDBConnection str_Old = oledbCn.Connection str_New = Replace(str_Old, shtSettings.Range("ptr_ID_" & str_From), shtSettings.Range("ptr_ID_" & str_To)) str_Connection = str_Connection & Chr(13) & cn.Name Debug.Print cn.Name Debug.Print str_Old Debug.Print str_New oledbCn.Connection = str_New Next cn str_Response = MsgBox("For Connections :" & str_Connection & Chr(13) & Chr(13) & "Activate 'Refresh All' when ready.", vbOKOnly, "Swapped " & str_From & " to " & str_To) End Sub
Hi messengineer
I don't have a similar infrastructure to test something so can't be a great help. Though, wanted to share a couple of things
- If I attempt to change the “Connection String” in Excel via Visual Basic it fails even though this is meant to be read/write
It fails doesn't mean anything if I may. Query doesn't refresh, error message would help to help you further
And just in case did you read the Remarks para. on OLEDBConnection.Connection property (Excel) ?
- there are two different Dataset IDs so there should be two different “Connection Strings” in the ODC file (or two different ODC files for each of the 19 versions)
Just an idea: why not creating 2 folders (PROD & TEST), each with the different version of your ODC files and via VBA switch between those folders thanks to a parameter you read somewhere (Name Manager, cell on Hidden sheet...) in the workbook
Hi.
Thanks for responding. The error message is
Run-time Error '1004'
Application-defined or object-defined error
I tried using the LocalConnection and get the same error. I am sure I am missing something obvious.
The named cell is where the Connection String is kept whilst I am experimenting.
Dim MyString As String
Dim cn As WorkbookConnection
Dim oledbCn As OLEDBConnection
MyString = Range("ptr_TEST").Value
Debug.Print MyString
Set cn = ThisWorkbook.Connections("MomentumDataModel")
Set oledbCn = cn.OLEDBConnection
oledbCn.LocalConnection = MyString
oledbCn.UseLocalConnection = True
I experimented further and did two Debug.Prints to compare my new Connection string to the old Connection String and found that it needed "OLEDB;" added to the front of the new one.
It works now! WhoopsPublic Sub SwapConnectionsPROD() Dim MyString As String Dim cn As WorkbookConnection Dim oledbCn As OLEDBConnection MyString = Range("ptr_TEST").Value Debug.Print "New " & MyString Set cn = ActiveWorkbook.Connections("MomentumDataModel") Set oledbCn = cn.OLEDBConnection Debug.Print "Old " & oledbCn.Connection oledbCn.Connection = MyString End SubThis is where I ended up .... with two named cells having the two different Power BI Dataset IDs
Option Explicit Public Sub SwapProdTest() Call SwapConnections("PROD", "TEST") End Sub Public Sub SwapTestProd() Call SwapConnections("TEST", "PROD") End Sub Private Sub SwapConnections(str_From As String, str_To As String) Dim str_Old, str_New, str_Connection, str_Response As String Dim cn As WorkbookConnection Dim oledbCn As OLEDBConnection For Each cn In ActiveWorkbook.Connections Set oledbCn = cn.OLEDBConnection str_Old = oledbCn.Connection str_New = Replace(str_Old, shtSettings.Range("ptr_ID_" & str_From), shtSettings.Range("ptr_ID_" & str_To)) str_Connection = str_Connection & Chr(13) & cn.Name Debug.Print cn.Name Debug.Print str_Old Debug.Print str_New oledbCn.Connection = str_New Next cn str_Response = MsgBox("For Connections :" & str_Connection & Chr(13) & Chr(13) & "Activate 'Refresh All' when ready.", vbOKOnly, "Swapped " & str_From & " to " & str_To) End SubHi messengineer
Glad you have a solution and thanks for posting it
