问题描述
我正在创建一个MS Access数据库，用于分析工作场所中的操作数据.为此，我将使用"DoCmd.TransferSpreadsheet"功能从Excel工作表中自动导入数据，然后再通过SQL对其进行转换.
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.
它在一种特殊情况下效果很好:excel工作表包含超过65k行时.我知道，这是Excel 2003的局限性，但我无法确定它的来源.
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:
- 我正在使用参数"acSpreadsheetTypeExcell12"
- 我正在读取的excel文件是.xlsx
- 我正在使用Microsoft Excel 16.0对象库
- MS Access文件版本为:.accb(2007-2016)
我使用的功能如下:
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
正如我所说，我遇到运行时错误3011"Microsoft Access数据库模块找不到对象Sheet_name $ Range"(从德语轻松翻译).我想要的是，就像导入少于65k行一样，正在导入数据.
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!
推荐答案
我宁愿创建一个宏，将其导入该文件，然后查看结果.甚至尝试手动导入文件，将XLSX导入ACCDB文件也没有限制.检查是否可以?
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")
使用:
Set appEx = CreateObject("Excel.Application.16")
或更改定义
Dim appEx As New Excel.Application
问题在于:尽管打开了参考第16版，但是在调用CreateObject方法时，它提供的库版本与参考上没有对应关系，并且创建了旧的库对象
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
这篇关于尽管使用了非Excel 2003功能和文件，MS Access Data Import 65000行限制仍然存在问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！