Using a workbook connection in VBA

[Murphy1]

Hi , I'm would like to use an existing workbook connection (based on an ODC file) and then change the command text from a SQL statement constructed in the spreadsheet. Getting the SQL is no problem but how do I call an existing workbook connection and then execute this with the new SQL statement.

I have created a connection via the UI and called it MyConnection.

This is what I have been testing but it fails.

Public Function GetDataSet()

'' Test config
Dim ConnName As String
Dim WorksheetName As String
Dim StartCellValue As String
Dim SQLString As String
Dim TableName As String


StartCellValue = "$A$1"
WorksheetName = "Sheet1"
SQLString = "SELECT MyField From MyTable"
TableName = "Test1"
ConnName = "MyConnection"

'Set the active worksheet
Worksheets(WorksheetName).Activate

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=ActiveWorkbook.Connections(ConnName), Destination:=Range(StartCellValue)).TableObject
.ListObject.DisplayName = TableName
.Refresh
End With

End Function

 

[Murphy1]

The question is really about the correct method to use the connection , change the commandtext and then execute the code. Thanks