VBA-将数据分页显示（listview）

1.首先我们来看一下分页显示的效果

2.要想作出分页的效果，必须要用listview控件实现，一般来说在窗体工具箱中并没有这个控件，所以我们要自己添加，打开窗体工具箱，在空白处右键-->附加控件-->选择microsoft listview control,version 6.0-->确定就好了，其使用方法和其他控件的使用方法是一样的，拖动到窗口就可以使用了

3.首先我们来了解一下listview 的一些方法

Private Sub CommandButton1_Click()
   '添加表头
    With ListView1
方法一:     挨个添加
     .ColumnHeaders.Add 1, "xh", "学号", .Width / 7, lvwColumnLeft
     .ColumnHeaders.Add 2, "xm", "姓名", .Width / 7, lvwColumnLeft
     .ColumnHeaders.Add 3, "xh", "班级", .Width / 7, lvwColumnLeft
     .ColumnHeaders.Add 4, "xh", "语文", .Width / 7, lvwColumnLeft
     .ColumnHeaders.Add 5, "xh", "数学", .Width / 7, lvwColumnLeft
     .ColumnHeaders.Add 6, "xh", "英语", .Width / 7, lvwColumnLeft
     .ColumnHeaders.Add 7, "xh", "总分", .Width / 7, lvwColumnLeft
方法二: 动态添加
.ColumnHeaders.Clear
 Dim itm As ListItem
      Dim j As Integer
Dim i As Integer
Dim col As Integer '用于记录列数
    col = Range("A1").End(xlToRight).Column  '从A1开始向右取得最后一列列号
    For i = 1 To col
    .ColumnHeaders.Add i, , Cells(1, i), .Width / col, lvwColumnLeft
    Next i

     格式处理
     .Gridlines = True '显示表格线
     .FullRowSelect = True '支持整行的选取
     .View = lvwReport '设置数据以报表形式显示
     .ListItems.Clear
      For i = 2 To Range("A1").End(xlDown).Row
            Set itm = .ListItems.Add()
            For j = 2 To Range("A1").End(xlToRight).Column
                itm.Text = Cells(i, 1)
                itm.SubItems(j - 1) = Cells(i, j)
             Next j
          Next i
    End With

End Sub
'添加内容
Private Sub CommandButton2_Click()
'手工添加
      Dim itm As ListItem
      Dim i As Integer
      For i = 2 To Range("A1").End(xlDown).Row
        Set itm = ListView1.ListItems.Add()
        itm.Text = Cells(i, 1)
        itm.SubItems(1) = Cells(i, 2)
        itm.SubItems(2) = Cells(i, 2)
        itm.SubItems(3) = Cells(i, 2)
        itm.SubItems(4) = Cells(i, 2)
      Next i
      循环添加
      Dim j As Integer
      With ListView1
        For i = 2 To Range("A1").End(xlDown).Row
            Set itm = .ListItems.Add() '注意第一列是listitems
            For j = 2 To Range("A1").End(xlToRight).Column
                itm.Text = Cells(i, 1)
                itm.SubItems(j - 1) = Cells(i, j)
             Next j
          Next i
        End With


End Sub

4.以上面所了解的为基础，进行更深层次的尝试，对所查询的结果进行分页显示，具体的代码及解释如下

'listview
Option Explicit
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsds As ADODB.Recordset
Dim rspage As Integer '当前处于第几页

'当复合框里面的内容更改以后，重新刷新
Private Sub ComboBox1_Change()
rspage = 1
Call addrows(rspage)
End Sub
'关闭窗体，释放空间
Private Sub CommandButton1_Click()
rs.Close
con.Close
Set rs = Nothing
Set rsds = Nothing
Set con = Nothing
End

End Sub
'切换到第一页
Private Sub dyy_Click()
 rspage = 1
 Call addrows(rspage)
End Sub
'切换到上一页
Private Sub syy_Click()
 If rspage <> 1 Then
 rspage = rspage - 1
 Call addrows(rspage)
 End If
End Sub
'切换到下一页
Private Sub xyy_Click()
 If rspage <> rs.PageCount Then
 rspage = rspage + 1
 Call addrows(rspage)
 End If
End Sub
'切换到最末页
Private Sub zmy_Click()
 rspage = rs.PageCount
 Call addrows(rspage)
End Sub

'窗体加载
Private Sub UserForm_Initialize()
'循环方式设置复选框
     Dim i As Integer
     For i = 1 To 20
     ComboBox1.AddItem i
     Next i
    ' 默认显示
     ComboBox1.Value = 5
     rspage = 1
     '连接数据库
     Set con = New ADODB.Connection
     With con
       .Provider = "microsoft.ace.oledb.12.0"
       .ConnectionString = "data source=" & ThisWorkbook.Path & "\学生管理.accdb"
       .Open
     End With
     '查询数据
     Dim sql As String
     sql = "select * from 员工 order by 编号 "
     Set rs = New ADODB.Recordset '创建记录集对象
    rs.Open sql, con, adOpenKeyset, adLockOptimistic
    '设置listview1控件的信息，添加表头
     With ListView1
        .ColumnHeaders.Clear
        .ListItems.Clear
        .View = lvwReport
        .FullRowSelect = True
        .Gridlines = True
        For i = 0 To rs.Fields.Count - 1
          .ColumnHeaders.Add i + 1, , rs.Fields(i).Name, , lvwColumnLeft
        Next i
      End With
    Call addrows(rspage) '调用子过程显示第一页记录
     
End Sub
Public Sub addrows(mypage As Integer)
     On Error Resume Next
     Dim i As Integer, j As Integer
     '创建局部recorset对象rsds,保存rs 记录集中当前页的数据
     Set rsds = New ADODB.Recordset
     For i = 0 To rs.Fields.Count - 1
     'append追加的意思，字段名称，字段类型，字段大小
        rsds.Fields.Append rs.Fields(i).Name, rs.Fields(i).Type, rs.Fields(i).DefinedSize
     Next
     '打开局部recorset 对象rsds
     rsds.Open
     rs.PageSize = Val(ComboBox1.Value) '重置rs 每页显示的记录条数(pagesize 表示记录集的每页的记录条数)
     rs.AbsolutePage = mypage '重置rs的当前记录页(跳到这页的第一条记录）
     '当rs 的当前记录页保存到rsds 之中
     For i = 1 To rs.PageSize
     rsds.AddNew '添加一条记录
     For j = 0 To rs.Fields.Count - 1
       rsds.Fields(j).Value = rs.Fields(j).Value
     Next j
     If rs.EOF Then Exit For
     rs.MoveNext
     Next i
     '在listview1控件中显示当前页的记录数据
     rsds.MoveFirst '定位到第一条记录
     '添加记录
     With ListView1

        .ListItems.Clear
        For i = 1 To rsds.RecordCount
            .ListItems.Add , , rsds.Fields(0).Value
            For j = 1 To rsds.Fields.Count - 1
                .ListItems(i).SubItems(j) = rsds.Fields(j).Value
            Next j
            rsds.MoveNext
         Next i
     End With
     '在文本框中刷新当前记录页信息
     TextBox1.Value = mypage & "/" & rs.PageCount
End Sub