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

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
67
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:

VBA Code:
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:

Code:
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:

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

Any help with this would be greatly appreciated.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
67
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.
 
Last edited:

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
67
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,279
Messages
5,600,696
Members
414,400
Latest member
Damocles2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top