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:
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:
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:
Any help with this would be greatly appreciated.
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.