大数据翻页_Excel：分页显示大数据，原来方法这么多

详细分析一下，代码

Sub SQL方法2()

Dim cn As Object, rs As Object

Dim sql1 As String, sql2$

Dim n As Long

Dim i As Long

Dim k As Long

Set cn = CreateObject("Adodb.Connection")

With cn

.Provider = "Microsoft.Ace.Oledb.12.0;Extended Properties=Excel 12.0"

.Open ThisWorkbook.FullName

End With

'设置参数

With Sheet1.ScrollBar1

.Min = 1

.Max = Sheet1.Range("K2").Value

End With

n = Sheet1.Range("M2").Value  '每页条数

k = Sheet1.Range("I2").Value  '页码

If k > 1 Then  '页码大于1页的时候

'这个是算已经翻页的编号都有那些

sql1 = "select top " & n * (k - 1) & " 编号 from [数据$]"

'这个是总表和已经翻页编号比较，合成一个新表，这个表四个字段

sql2 = "select a.编号,a.学校,a.学员,a.学费,b.编号 as tempcolum from [数据$] a left join (" _

& sql1 & ") b on a.编号 = b.编号"

'通过判断第四个字段是空，来达到找到去掉已经翻页数据的目的

'        sql2 = "select c.编号,c.学校,c.学员,c.学费 from (" & sql2 & ") c where c.tempcolum is null"

'取上面新数据前N条数据

'        sql2 = "select top " & n & " 编号,学校,学员,学费 from (" & sql2 & ")"

Else          '页码=1页的时候执行

'取每页条数的数据，就是1-N条数据

sql2 = "select top " & n * k & " 编号,学校,学员,学费 from [数据$]"

End If

'拿RS数据

Set rs = cn.Execute(sql2)

'关闭屏幕刷新

Application.ScreenUpdating = False

'清除之前结果

Range("a1:e65536").ClearContents

'得到表头

For i = 0 To rs.Fields.Count - 1

Cells(2, i + 2).Value = rs.Fields(i).Name

Next i

'把结果复制出来到单元格里

Range("B3").CopyFromRecordset rs

cn.Close: Set cn = Nothing

Application.ScreenUpdating = True

End Sub