I have a problem with a SQL Statement in VBA Excel. I can't use ORDER BY together with GROUP BY and I don't understand why. If I delete ORDER BY I don't get errors. Can anyone help me figure out where am I wrong?
Thanks a lot to everyone!!
Here is my code:
Dim RS As New ADODB.Recordset
Dim sConn As String
Dim stmSQL As String
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & "/OrigineDati.xlsx" & ";Extended Properties='Excel 12.0 Xml;HDR=YES'"
stmSQL = "SELECT COL5 FROM [DATABASE$] WHERE COL9 = 'PITTSBURGH' AND LEFT(COL5,2) = 'BA'" _
& "GROUP BY COL5" _
& "ORDER BY COL5 ASC"
Foglio1.Range("A2").CurrentRegion.ClearContents
RS.Open stmSQL, sConn, adOpenStatic, adLockReadOnly
Foglio1.Range("A2").CopyFromRecordset RS
RS.Close
Set RS = Nothing
