如何将超过65536行从Excel转移到Access（两个版本2010） [英] How to transfer more than 65536 rows from excel to Access (Both Version 2010)

问题描述

我有以下代码将一些行从Excel传输到Access数据库，然后从Access导出到.txt文件。问题是，当我将其导出到Access，它只导出65536行。有办法解决吗？

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

函数Exportar调用2个新函数Importar然后Exportar这里是他们：

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

没有人有线索？ =

推荐答案

您是否要导入.xls文件，即使它是使用Excel 2010创建的？在所有 .xls文件中，行数为65536，您可以尝试使用.xlsx文件来查看其是否只导入65536行。

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.

这篇关于如何将超过65536行从Excel转移到Access（两个版本2010）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！