Sql in VBA 之排序

排序
数据如下表：
代码：
desc：降序、asc：升序，不写则默认升序
 1 Sub 排序()
 2     Dim cnn As Object, rst As Object
 3     Dim Mypath As String, Str_cnn As String, Sql As String
 4     Dim i As Long, start
 5     start = Timer
 6     Set cnn = CreateObject("adodb.connection")
 7     
 8     Mypath = ThisWorkbook.FullName
 9     If Application.Version < 12 Then
10         Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath
11     Else
12         Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath
13     End If
14     cnn.Open Str_cnn
15     
16     
17 '    Sql = "select 月份,生产量 from [产品表$] order by 生产量"
18 '    'Sql语句 生产量升序
19 
20 '    Sql = "select 月份,生产量,销售量 from [产品表$] order by 生产量 desc,销售量 asc"
21 '    'Sql语句 先生产量降序、后销售量升序
22     
23 '    Sql = "select 月份,生产量 from [产品表$] order by instr('五月,四月,三月,二月,一月',月份)"
24 '    'Sql语句 自定义排序
25 
26     Sql = "select top 3 月份,生产量 from [产品表$] order by 生产量 desc"
27     'Sql语句 生产量降序
28 
29     Set rst = cnn.Execute(Sql)
30 
31     [k:l].ClearContents
32     For i = 0 To rst.Fields.Count - 1
33         Cells(1, i + 11) = rst.Fields(i).Name
34     Next
35     Range("k2").CopyFromRecordset rst
36    
37     cnn.Close
38     Set cnn = Nothing
39     
40     MsgBox Format(Timer - start, "0.00s")
41 
42 End Sub
Sql in VBA 之 排序

Sql in VBA 之排序
