Forum Discussion

messengineer's avatar
messengineer
Copper Contributor
Nov 08, 2024

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 columns, filters and sorts I need to pull the Power BI data.

I am using these ODC files across 7 different Workbooks with some overlap, so sometimes the ODC is used across 3-4 different Workbooks, but generally only once.

This all works fine. This solution HAS to be in Excel as these Excel files are used as templates for a Bulk Upload to one of our systems.

However, the Power BI dataset has a PROD and TEST version and I have been asked to allow the Excel files to be “switched between”. So effectively 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).

I can edit the ODC files in Notepad easily and search and replace the Dataset ID’s but trying to find a longer term more supportable method.
Or I can edit in Excel and then resave another ODC to get the same result.

https://learn.microsoft.com/en-us/office/vba/api/excel.oledbconnection.connection

If I attempt to change the “Connection String” in Excel via Visual Basic it fails even though this is meant to be read/write. 

I also can’t seem to create a Parameter to pass the Dataset ID in as a variable either.

Does anyone have experience in this or could make an alternative suggested method?

BI & Data Analysis
Excel
Power BI
  • messengineer's avatar
    messengineer
    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's avatar
    Lorenzo
    Silver Contributor
    Nov 08, 2024

    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

    • messengineer's avatar
      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's avatar
        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

         

Resources