[已解决]SQL排序

张雄友 · 发表于 2014-5-17 22:35

本帖最后由张雄友于 2014-5-18 03:23 编辑

如何按拼音首字母降序排序？？见效果。

Sub SQL()
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName
Select Case Application.Version * 1
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.Open strConn
strSQL = "SELECT * FROM [学生$] ORDER BY 姓名 " '如何按拼音首字母降序排序？？见效果。
Set Rst = Conn.Execute(strSQL)
With Sheets("提取")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit
.Cells.EntireColumn.AutoFit
End With
Rst.Close
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub

复制代码

最佳答案

hwc2ycy

2014-5-17 22:54

Sub SQL()
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName
Select Case Application.Version * 1
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.CursorLocation = 3
Conn.Open strConn
strSQL = "SELECT * FROM [学生$]" '如何按拼音首字母降序排序？？见效果。
Set Rst = Conn.Execute(strSQL)
Rst.Sort = "姓名 desc"
With Sheets("提取")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit
.Cells.EntireColumn.AutoFit
End With
Rst.Close
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub

复制代码

张雄友 · 发表于 2014-5-17 22:36

order by 姓名 desc 也不行。

hwc2ycy · 发表于 2014-5-17 22:54

Sub SQL()
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName
Select Case Application.Version * 1
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.CursorLocation = 3
Conn.Open strConn
strSQL = "SELECT * FROM [学生$]" '如何按拼音首字母降序排序？？见效果。
Set Rst = Conn.Execute(strSQL)
Rst.Sort = "姓名 desc"
With Sheets("提取")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit
.Cells.EntireColumn.AutoFit
End With
Rst.Close
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub

复制代码

张雄友 · 发表于 2014-5-18 03:22

hwc2ycy 发表于 2014-5-17 22:54

14句代码有何用？？

hwc2ycy · 发表于 2014-5-18 06:56

张雄友发表于 2014-5-18 03:22
14句代码有何用？？

要使用recordset的sort属性，必须设置CursorLocation属性的值为adUseClient（对应的值为3）

张雄友 · 发表于 2014-5-18 07:22

hwc2ycy 发表于 2014-5-18 06:56
要使用recordset的sort属性，必须设置CursorLocation属性的值为adUseClient（对应的值为3）

姓名不是在第二列吗？怎么是3？难道3代表什么？

hwc2ycy · 发表于 2014-5-18 21:32

张雄友发表于 2014-5-18 07:22
姓名不是在第二列吗？怎么是3？难道3代表什么？

3列游标类型，不是列号。

		自动登录	找回密码
密码			注册