Solved: QueryTable wont work in Excel 2007

[bradh_nz]

I have a standard vba query which updates a tables background sql. I could apply to query tables in excel 2003. However when I create a table in excel 2007 the following line of code does not work

Set qry = Worksheets("HVDC").QueryTable("HVDC_Data")

I have read that it may be due to query tables being redefined as list object but cannot get he code to run.

Any help appreciated

[vba]
Sub HVDC_Actuals()

Dim Trade_P_CNN As String
Dim sqlcode As String
Dim qry As QueryTable

Dim Sdate As Date
Dim EDate As Date

Set qry = Worksheets("HVDC").QueryTable("HVDC_Data")

Sdate = Range("Startdate").Value
EDate = Range("Enddate").Value

Application.StatusBar = "Retrieving HVDC Data"

Trade_P_CNN = "ODBC;DSN=CDR_P;UID=trader;PWD=excel;DBQ=CDR_P;" ' set up connection

query_string = query_string + " sql code here"

qry.Sql = query_string ' set list with sql query
qry.Refresh 'refresh list
Application.StatusBar = False

End Sub

[/vba]

[Jan Karel Pieterse]

If there is only one QT on that sheet:

Set qry = Worksheets("HVDC").ListObjects(1).QueryTable

[bradh_nz]

Thanks, brilliant, taking some time to work through 2007 changes
One extra thing, everytime I now run this it creates a new workbook connection rather than using the origional one I set up?

[Jan Karel Pieterse]

No, you're just changing the existing one.