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