Problem with MS Access Data Import 65000 lines limit despite using non-Excel 2003 functions and files

Question

I am creating an MS Access Database for analysis purposes of operative data at my workplace. In order to do that, I am automatically importing data from Excel sheets using the "DoCmd.TransferSpreadsheet" function and later transforming them via SQL.

It works well except in one specific case: When the excel sheet contains more than 65k lines. I know, that this is an Excel 2003 limitation, but I cannot tell where it is coming from.

I already read through similar questions in different forums, but what helped them was already included in my case:

I am using the parameter "acSpreadsheetTypeExcell12"
The excel file I am reading from is a .xlsx
I am using the Microsoft Excel 16.0 Object Library
The MS Access File Version is: .accb (2007-2016)

The Function that I use is the following:

Sub ImportExcelData(filePath As String, fileName As String)
    ' Creating and initializing variables
    Dim file As String
    Dim tempTable As String
    Dim appEx As Excel.Application
    Dim wb As Excel.workBook
    Set appEx = CreateObject("Excel.Application")
    Set wb = appEx.Workbooks.Open(file)
    appEx.Visible = True

    file = filePath + fileName


   ' reading in Data from sheets within workbook
   For Each ws In wb.Worksheets
    If ws.Visible = True Then
        ws.Activate
        tableName = ws.Name
        With ws
            lastRow = Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
            'MsgBox lastRow
        End With
        'MsgBox lastRow
        On Error Resume Next

        DoCmd.DeleteObject acTable, tempTable

        If Err.Number = 2008 Then

            MsgBox "Die Tabelle kann nicht gelöscht und neu erstellt werden, da diese geöffnet ist."

        End If


        On Error GoTo 0

        sAdress = tableName & "!A1:Z" & lastRow
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, filePath & fileName, True, sAdress 'function that thros error


      End If
    Next



    ' close everything
    wb.Close SaveChanges:=False

    Set appEx = Nothing
    Set wb = Nothing


End Sub

As I said, I am getting a runtime error 3011 "The Microsoft Access Data Bank Module couldn't find the object Sheet_name$Range" (loosely translated from German). What I would want is that the data is being imported just as if it was less than 65k lines.

I am really out of ideas what it might be; Thank you very much for your help!

Van Ng Van Ng · Accepted Answer · 2019-04-26T12:10:55

I'd rather create a macro, that imports that file, and see what would be the result. Or even try to import file manually, there should be no limitation to import XLSX to ACCDB files. Check, if this would be OK?

Other point, that you are calling "old" library in such syntax:

Set appEx = CreateObject("Excel.Application")

Use:

Set appEx = CreateObject("Excel.Application.16")

Or change definition

Dim appEx As New Excel.Application

So the trouble is that: despite of turning on the Reference to 16th version, when you are calling CreateObject method, it provides you version of library without correspondence on References, and old library object is created

Problem with MS Access Data Import 65000 lines limit despite using non-Excel 2003 functions and files

1 Answers