Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strsql, Field, i
cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
strsql = "select 班级,姓名,政治,物理,数学,语文,英语,总分 ,dcount(1,""Sheet2$"",""班级='""& 班级 &""' and 总分> "" & 总分)+1 as 班级排名,dcount(1,""sheet2$"",""总分>"" & 总分)+1 as 年级排名 from [sheet2$]"
rs.Open (strsql), cnn, adOpenKeyset, adUseClient
For Each Field In rs.Fields
Sheets("sheet1").Cells(1, 1).Offset(0, i) = Field.Name
i = i + 1
Next
Sheets("sheet1").Cells(2, 1).CopyFromRecordset rs
rs.Close
cnn.Close
End Sub
