PDA

View Full Version : Solved: QueryTable wont work in Excel 2007



bradh_nz
04-08-2010, 05:42 PM
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


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

Jan Karel Pieterse
04-09-2010, 07:27 AM
If there is only one QT on that sheet:

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

bradh_nz
04-11-2010, 01:45 PM
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
04-11-2010, 11:19 PM
No, you're just changing the existing one.