ODBC auto-connection string with VBA issue with Power Query connection

[jeff_cp]

I've got a spreadsheet that is shared between customers/users. These customers/users all have an ODBC connection name of "APEXDATA" on their workstations. In the spreadsheet, I have the following VBA code in the "ThisWorkbook" section and a connection string of "DSN=APEXDATA" in each table so the users don't have to update the table connection's IP addresses:

Private Sub Workbook_Open()
Dim TheConnectionName As String
For Each objWBConnect In ThisWorkbook.Connections
TheConnectionName = objWBConnect.Name
ThisWorkbook.Connections.Item(TheConnectionName).ODBCConnection.Connection = "ODBC;DSN=APEXDATA"
Next objWBConnect
End Sub

The problem I've run into is I'm using Power Query to combine employee names from 3 different tables (on a worksheet called Employees) into a single query showing a list of unique employees. The connection string for the table where these unique employees are is:

Provider=Microsoft.Mashup.OleDB.1;Date Source=$Workbook$;Location=Employees;Extended Properties=""

When I open the spreadsheet, I'm getting a run-time error '1004': Application-defined or object-defined error. When I debug, it's showing the error is with this line of the VBA code:

ThisWorkbook.Connections.Item(TheConnectionName).ODBCConnection.Connection = "ODBC;DSN=APEXDATA"

Any help with this would be greatly appreciated.

 

[jeff_cp]

Providing more information hoping it will help someone to be able to help me.

I have 3 Power Query queries that are connections only. These are 3 different lists of employees. I'm using a 4th query to combine these 3 lists into a single list showing unique values only. I removed all 4 queries and the error is gone so I know it has something to do with these queries.

I think I need to modify the VBA in ThisWorkbook to account for these but I'm not sure how.

 

[jeff_cp]

I found a work around. I removed the connection information from each table using the ODBC connection and replaced it with just "DSN=APEXDATA". This allows me to exclude the VBA code.