Query doesn't work in Excel 2010

[MosesHigh]

This type of code works in Excel 2003 but not in Excel 2010. What do I have to add there to get it to work?
When I try to run the macro it says "Run-time error '414': Object required".


Sub test()
Array("SELECT XYZ.FIELD1 ", _
"FROM SOURCE.XYZ XYZ ", _
"WHERE (XYZ.FIELD1='ABC') ", _
"ORDER BY XYZ.FIELD1")
Worksheets("Sheet1").QueryTables("Data").Sql = code
Worksheets("Sheet1").QueryTables("Data").Refresh
End Sub

[Jan Karel Pieterse]

Sub test()
Dim sSQL As String
sSQL= "SELECT XYZ.FIELD1 FROM SOURCE.XYZ XYZ WHERE (XYZ.FIELD1='ABC') ORDER BY XYZ.FIELD1"

Worksheets("Sheet1").QueryTables("Data").CommandText = sSQL
Worksheets("Sheet1").QueryTables("Data").Refresh

End Sub

[MosesHigh]

Now it says
"Run-time error '9'
Subscript out of range"

[Jan Karel Pieterse]

Is there a querytable called "Data" on worksheet "Sheet1"?

[MosesHigh]

Yes. But still it does not work

[Jan Karel Pieterse]

Hmm. Is this a newly created querytable (in the new version of Excel)? If so, the querytable now is the child of a listobject:
Worksheets("Sheet1").ListObjects(1).Querytable.CommandText = sSQL

[MosesHigh]

Yes, this is new. So the code should be like this:


Sub test()
Dim sSQL As String
sSQL= "SELECT XYZ.FIELD1 FROM SOURCE.XYZ XYZ WHERE (XYZ.FIELD1='ABC') ORDER BY XYZ.FIELD1"
Worksheets("Sheet1").ListObjects(1).Querytable.CommandText = sSQL
Worksheets("Sheet1").QueryTables("Data").Refresh
End Sub

It still give me the same information (run time error 9)

[Jan Karel Pieterse]

I bet this time it is on the Refresh line :-)

[MosesHigh]

Yes, that line is yellow when I press Debug-button

[Jan Karel Pieterse]

So it needs a similar change as the line above it :-)

[MosesHigh]

Yes! Now it works! Tank you!

[MosesHigh]

Do you know how to split the code into several lines? If the code is wide it's not possible to keep it on the one line.

In Excel 2003 I have added characters:
", _

Like this:

Array("SELECT XYZ.FIELD1 ", _
"FROM SOURCE.XYZ XYZ ", _
"WHERE (XYZ.FIELD1='ABC') ", _
"ORDER BY XYZ.FIELD1")

[Jan Karel Pieterse]

Like so:


sSQL= "SELECT XYZ.FIELD1 FROM SOURCE.XYZ XYZ WHERE (XYZ.FIELD1='ABC') ORDER BY XYZ.FIELD1"
'The line above is equal to:
sSQL= "SELECT XYZ.FIELD1 FROM SOURCE.XYZ XYZ "
sSQL = sSQL & "WHERE (XYZ.FIELD1='ABC') "
sSQL = sSQL & "ORDER BY XYZ.FIELD1"

[MosesHigh]

So simple. Thank you again!