Forum Discussion

Copper Contributor

Nov 08, 2024

Solved

Dynamically changing ODC Connection

I have connected Excel to a Power BI dataset using ODC (Office Data Connection). I am pulling data into 19 different Excel tables, so I have 19 different ODC files all configured for the different c...

Nov 09, 2024

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

messengineer

Copper Contributor

Nov 08, 2024

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

messengineer

Copper Contributor

Nov 08, 2024

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! Whoops

Public 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 Sub

messengineer

Copper Contributor

Nov 09, 2024

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

Lorenzo
Silver Contributor
Nov 09, 2024
Hi messengineer
Glad you have a solution and thanks for posting it