How to transfer more than 65536 rows from excel to

问题:

This question already has an answer here:

• Importing More Than 65.535 rows to MS Access from Excel 2 answers

I have the following code to transfer some rows from Excel to an Access Data Base, then it export from Access to a .txt file. The problem is that when I export it to Access, it only exports 65536 rows. Is there a way to fix it?

 Sub Mailing_Recebido()

    Dim i As Long
    Dim Caminho As String
    Dim A As Object

    Range("i27").Value = "Inicio da Exportação..."

    Range("BJ18").Select
    ActiveCell.FormulaR1C1 = "=CELL(""nome.arquivo"")"
    Range("BJ18").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("BJ18"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="[", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("BK18").Select
    Selection.ClearContents
    Caminho = Range("bj18").Value

    Sheets("Mailing_Recebido").Select

    Range("a5").Select
    i = Cells(Rows.Count, 1).End(xlUp).Row

    Sheets("Plan1").Select
    Range("BO5").Value = i

    Range("BO3").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(Mailing_Recebido!R[2]C[-66]:R[1048573]C[-66])"

    ActiveSheet.Calculate


    'Range("BN3").Select
    Range("BM26").Select
    Range("BM26").Value = Range("BO8").Value


    Set A = CreateObject("Access.Application")
    A.Visible = False
    A.OpenCurrentDatabase (Caminho + "\Cria_Mailing.mdb")
    A.DoCmd.RunMacro "Executar"

    'Range("bk22").Value = FileLen(Caminho + "\" + Range("c32").Value)
    Calculate

    'Call XTo_txt
    Range("i27").Value = "Exportação Completada..."



   End Sub

The Function "Exportar" calls 2 new functions "Importar" then "Exportar" here are them:

Option Compare Database


Function exporta()


    Dim rs As DAO.Recordset
    Dim caminho As String
    Dim NomeArq As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("NOMEBASE")


    caminho = rs.Fields(0).Value + "\" + rs.Fields(1).Value



    DoCmd.TransferText acExportFixed, "Mailing_Envio", "BASE", caminho



End Function


Function importa()

    Dim rs As DAO.Recordset
    Dim inicio As String
    Dim fim As String



'DoCmd.TransferSpreadsheet acImport, , _
    '"NOMEBASE", Application.CurrentProject.Path() + "\Abre_Envio_Novo_Layout.xlsm", True, "Plan1!BJ25:BM26"

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "NOMEBASE", Application.CurrentProject.Path() + "\Abre_Envio_Novo_Layout.xlsm", True, "Plan1!BJ25:BM26"


   ' TypeExcel12



    Set db = CurrentDb()
    Set rs = db.OpenRecordset("NOMEBASE")

    inicio = rs.Fields(2).Value
    fim = rs.Fields(3).Value


'DoCmd.TransferSpreadsheet acImport, , _
    '"BASE", Application.CurrentProject.Path() + "\Abre_Envio_Novo_Layout.xlsm", True, "Mailing_Recebido!A:AX"
    ' + inicio + ":" + fim


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "BASE", Application.CurrentProject.Path() + "\Abre_Envio_Novo_Layout.xlsm", True, "Mailing_Recebido!A:AX"
    ' + inicio + ":" + fim


    rs.Close

End Function

No one have a clue? =(

回答1:

Are you trying to import an .xls file even if it was created using Excel 2010? Because the limit on max number of rows is 65536 in all .xls files. You could try a .xlsx file to see if it imports only 65536 rows.