将整个Excel文件文件夹导入Access 2013 [英] Import entire folder of Excel files into Access 2013

问题描述

我很难将多个Excel文件导入到特定的Access 2013表中.

I am having a super hard time trying to import multiple Excel files into a specific Access 2013 table.

我尝试使用从另一个SO问题中找到的代码:

I tried using the code I found from another SO question:

Do While Len(strFile) > 0
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strTable, strPathFile, blnHasFieldNames
Loop

但是这为每个文件创建了新表，我需要将所有101个文件都放在一个表中.我尝试测试的代码在DoCmd.TransferSpreadsheet上失败了，我似乎根本无法使该部分正常工作.

but this makes new tables for each file, and I need all 101 files to be in a single table. The code I try to test it fails on DoCmd.TransferSpreadsheet, and I can't seem to get that part to work at all.

推荐答案

我很幸运使用带有以下参数的命令:

I have had luck using the command with these parameters:

DoCmd.TransferSpreadsheet 0, 10, tableName, wbPath, True, "MySheet!A1:C100"

该行是从工作中的应用程序复制粘贴的，该行中的应用程序将Excel文件中的数据导入到现有的Access表中，而不会覆盖或更改表中的现有数据.

The line is copy-pasted from a working application, where data from an Excel-file is imported to an existing Access table without overwriting or changing the existing data in the table.

参数列表的说明:

• 0:应与acImport枚举相同
• 10:与acSpreadsheetTypeExcel12Xml枚举相同(我认为我使用整数10是因为有些兼容性，其中枚举具有不同的值或在某些版本的Access中未定义)
• tableName:是Access数据库中现有表的名称
• wbPath:是以xlsx格式保存的工作簿的绝对文件路径
• 是:表示工作表具有标题
• "MySheet！A1:C100"指示工作表名称和应导入的单元格范围.

我不记得Excel工作表中的列标题和/或列顺序是否必须与Access表的列匹配.但是，它们应该匹配确实很有意义.你检查了吗?

I cannot remember if the column headers and/or the order of the columns in the Excel sheet have to match the Access table columns. But it would certainly make sense that they should match. Have you checked that?

我记得，如果Excel工作表中单元格中的某些数据类型与Access表中相应列的数据类型不匹配/不匹配，Access将创建一些临时的报告表"，这表明存在错误在导入过程中发生了.也许这就是您正在经历的事情?

I recall that if some of the datatypes in the cells in the Excel-sheet did not match/fit the datatype of the corresponding column in the Access table, Access would create some temporary "report tables", which indicated that an error had happened during the import. Maybe that is what you're experiencing?

这是文件类型枚举(第二个函数参数)及其对应文件类型的列表.您使用的是正确的枚举值吗?我看到您提供了acSpreadsheetTypeExcel9的值，是否要从Excel 2000文件导入?

Here is the list of file type enums (second function argument) and their corresponding file types. Are you using the correct enum value? I see you provide a value of acSpreadsheetTypeExcel9, are you importing from Excel 2000 files?

Excel 3                           acSpreadsheetTypeExcel3
Excel 95                          acSpreadsheetTypeExcel7
Excel 97                          acSpreadsheetTypeExcel8
Excel 2000                        acSpreadsheetTypeExcel9 (default)
Excel 2002                        acSpreadsheetTypeExcel10
Excel 2003                        acSpreadsheetTypeExcel11
Excel 2007 Binary Format (.xslb)  acSpreadsheetTypeExcel12
Excel 2007 (xlsx)                 acSpreadsheetTypeExcel12Xml

更新:

Access将使用您从中运行宏的当前数据库中的表(假设您从Access中运行该宏).如果要导入到另一个数据库，可以执行以下操作:

Access will use the table in the current database, that you run the macro from (assuming you run it from within Access). If you want to import to a another database, you can do this:

Dim accessApplication As Variant
Set accessApplication = CreateObject("Access.Application")

accessApplication.OpenCurrentDatabase databasePath, False

accessApplication.DoCmd.TransferSpreadsheet 0, 10, tableName, wbPath, True, "MySheet!A1:C100"

其中，databasePath是现有数据库的绝对文件路径.最后一个参数指定是否要以独占模式打开数据库.默认值为False，它指定应以共享模式打开数据库.

Where databasePath is the absolute file path to an existing database. The last parameter specifices whether you want to open the database in exclusive mode. The default value is False, which specifies that the database should be opened in shared mode.

上面的代码将确保将数据导入到特定Access数据库文件中的指定表中.

The above code will make sure that the data is imported into the specified table in a specific Access database file.

如果所有要导入的数据都存储在一个Excel文件中，我建议您将导入宏放在Excel文件中，而不要放在多个Access文件中.这样可以更轻松地维护宏.切记要在VBA编辑器的工具"->引用"中添加对Microsoft Access对象库的引用.如果您同时拥有多个Excel文件和多个Access文件，则可以考虑拥有一些主Excel工作簿，该工作簿仅保存该宏，然后打开其他Excel文件和Access数据库.

If all the data you wish to import is stored in one Excel-file, I would recommend that you put the import macro in the Excel-file instead of having it in multiple Access files. That makes it easier to maintain the macro. Remember to add a reference to the Microsoft Access Object Library in Tools -> References in the VBA editor. If you both have multiple Excel-files and multiple Access files, you could consider having some master Excel-workbook,which just holds the macro and then opens the other Excel-files and Access databases.

这篇关于将整个Excel文件文件夹导入Access 2013的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！