从Access（mdb）文件中指定表格导出excel，数据量过大会报错：电子数据表满 怎么解决

Archer乄

Sub ADO直接生成工作簿() 'mdb
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Fso As New FileSystemObject
    Dim oFile As File
    Dim myTable As String
    Dim Mypath As String
    Dim SQL$, n&, sh$
    myTable = InputBox("请输入导出数据表名")
    If myTable = "" Then Exit Sub
    For Each oFile In Fso.GetFolder(ThisWorkbook.Path).Files
        If oFile.Name Like "*.mdb" Then
            Mypath = Replace(oFile, "mdb", "xls")
            If Dir(Mypath) <> "" Then Kill Mypath
            Set cnn = New ADODB.Connection
            cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & oFile & ";Persist Security Info=False;Jet OLEDB:Database Password=123456"
            If cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, myTable, Empty)).EOF Then
                MsgBox myTable & "表" & "不存在，请重新输入。", vbInformation
                Exit For
            End If
            SQL = "SELECT * INTO [Excel 8.0;Database=" & Mypath & ";]." & myTable & " FROM " & myTable
            cnn.Execute SQL
        End If
    Next
    cnn.Close
    Set cnn = Nothing
    Set Fso = Nothing
    MsgBox "ok"
End Sub

zpy2

SELECT * INTO [Excel 12.0;Database=" & Mypath & ";]." & myTable & " FROM " & myTable

Archer乄

zpy2 发表于 2020-5-22 08:28
SELECT * INTO [Excel 12.0;Database=" & Mypath & ";]." & myTable & " FROM " & myTable

谢谢大佬。已解决