vba 自动将不同的 Excel 文件导入 MS Access 2010 表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21578518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import different excel files into MS Access 2010 tables automatically
提问by Gyuzal R
I'd like to import all Excel files (with different data and columns) from some directory into MS Access 2010 database, creating new table for each file. I've found the code to import files into one table:
我想将所有 Excel 文件（具有不同的数据和列）从某个目录导入 MS Access 2010 数据库，为每个文件创建新表。我找到了将文件导入一张表的代码：
Option Compare Database
Option Explicit
Function DoImport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents and Settings\myName\My Documents\Access Test\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop
End Function
But I need to create new table each time. Is it possible in VBA?
但我每次都需要创建新表。在VBA中可能吗？
回答by HansUp
I think all you need to do is change the destination table name (the value of strTable) each time before you do DoCmd.TransferSpreadsheet.
我认为您需要做的就是在strTable每次更改之前更改目标表名称（的值）DoCmd.TransferSpreadsheet。
In a comment you said you want the table name to be derived from the workbook file name. And, each time through your loop, another variable (strFile) contains the file name. So I think you could strip the file extension from that file name and use it as the Access table name.
在评论中，您说您希望从工作簿文件名派生表名。而且，每次循环时，另一个变量 ( strFile) 都包含文件名。所以我认为您可以从该文件名中去除文件扩展名并将其用作 Access 表名。
Here is an Immediate window example which demonstrate how that can be done ...
这是一个立即窗口示例，它演示了如何做到这一点......
strFile = "foo.xls"
strTable = Left(strFile, Len(strFile) - 4)
? strTable
foo
If that approach is suitable, revise the loop in your VBA code like this (untested) code snippet ...
如果这种方法合适，请像这样（未经测试的）代码片段一样修改 VBA 代码中的循环......
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = Left(strFile, Len(strFile) - 4)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop
回答by CRAFTY DBA
I used to be a MOS Access 2003. Now everyone is using 2010 but many things have not changed.
我以前是MOS Access 2003，现在大家都在用2010 但是很多东西没变。
When you do a manual import or export, you can save the layout as a specification.
手动导入或导出时，您可以将布局另存为规范。
This process can be automated by a macro.
这个过程可以通过宏自动化。
Check out the link below for more details and steps.
查看下面的链接以了解更多详细信息和步骤。
As for the other stuff, buttons, modules, etc, please read the on line help / documentation first.
至于其他东西，按钮，模块等，请先阅读在线帮助/文档。
We are here to help but not do the work for you.
我们在这里为您提供帮助，但不是为您完成工作。
J
J
回答by CRAFTY DBA
Okay, I do not know if it is an issue with my computer not being on the current office CU.
好的，我不知道是不是我的电脑不在当前办公室 CU 上的问题。
http://msdn.microsoft.com/en-us/library/office/ff192475(v=office.14).aspx
http://msdn.microsoft.com/en-us/library/office/ff192475(v=office.14).aspx
Here is a link to how to use the ImportExport Macro. Use to be in the macro section.
这是如何使用 ImportExport 宏的链接。用于在宏部分。
I did read that you had to trust the location. So I tried both my location c:\msdn plus the default for the wizards.
我确实读到您必须信任该位置。所以我尝试了我的位置 c:\msdn 加上向导的默认位置。
Still was not able have it the option come up.
仍然无法让选项出现。
I tried creating a specification to see if one was needed for the option to show, no dice.
我尝试创建一个规范，看看是否需要一个选项来显示，没有骰子。
However, there is a DoCmd.TransferText and DoCmd.TransferSpreadSheet.
但是，有一个 DoCmd.TransferText 和 DoCmd.TransferSpreadSheet。
Both will allow you to import.
两者都允许您导入。
Create a function. Call the function from a macro (RunCode). Another way is to create a main menu form. Have a button. On the click command, run the code.
创建一个函数。从宏 (RunCode) 调用该函数。另一种方法是创建一个主菜单表单。有一个按钮。在单击命令上，运行代码。
Please tell me if you ever get the ImportExportData Macro to show. I think it is a bug. I will need to bring down the latest Cumulative Updates and try again.
请告诉我您是否曾经显示过 ImportExportData 宏。我认为这是一个错误。我需要关闭最新的累积更新并重试。
