vba拆分accesss数据

pfhbj

vba拆分accesss数据（主键1-44），并将每20条记录复制到一个新的工作表中。这样，前20条记录将被复制到Sheet1，接下来的20条记录将被复制到Sheet2，以此类推。

limonet

关键字：until rst.eof

limonet

Sub limonet()
    Dim Cn As Object, StrSQL$, Rst As Object, i%
    Set Cn = CreateObject("Adodb.Connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\拆分.accdb"
    StrSQL = "Select * From Table1"
    Set Rst = Cn.Execute(StrSQL)
    Do Until Rst.EOF
        i = i + 1
        Sheets(i).Range("A2").Resize(20, 2) = Application.Transpose(Rst.GetRows(20))
    Loop
End Sub

pfhbj

limonet 发表于 2024-1-30 09:36
关键字：until rst.eof

为啥改成50万条就不行了？

Sub limonet()
    Dim Cn As Object, StrSQL$, Rst As Object, i%
    Set Cn = CreateObject("Adodb.Connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\拆分0130.accdb"
    StrSQL = "Select * From Table1"
    Set Rst = Cn.Execute(StrSQL)
    Do Until Rst.EOF
        i = i + 1
        Sheets(i).Range("A2").Resize(500000, 2) = Application.Transpose(Rst.GetRows(500000))
    Loop
End Sub

pfhbj

我把20改成500000就报错，啥情况？

limonet

Transpose，转置有限制，最大65536