vba 将多个 Excel 文件中的一张工作表导入到多访问表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19773778/
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 One Worksheet from Multiple Excel Files into Multiple Access tables
提问by logicForPresident
I have about 200 Excel files that I would like to import into a single Access database and have a table for each file. Each Excel file has multiple worksheets, but the one that I would like to import is consistently named.
我有大约 200 个 Excel 文件，我想将它们导入到一个 Access 数据库中，并且每个文件都有一个表格。每个 Excel 文件都有多个工作表，但我要导入的工作表是一致命名的。
I have found some code for this, see: http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrsFldFiles, http://social.msdn.microsoft.com/Forums/en-US/dfea25ab-cd49-495c-8096-e3a7a1484f65/importing-multiple-excel-files-with-different-file-name-into-access-using-vba
我找到了一些代码，请参阅：http: //www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrsFldFiles，http: //social.msdn.microsoft.com/Forums/en-US/dfea25ab-cd49- 495c-8096-e3a7a1484f65/importing-multiple-excel-files-with- different-file-name-into-access-using-vba
Here is one of the pieces of code which I tried:
这是我尝试过的一段代码：
Option Compare Database
Sub ImportFromExcel()
End Sub
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
strBrowseMsg = "C:\Users\fratep\Desktop\Long-term EWM Study Data Files\"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
' 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
Sub ImportMultiExcels()
End Sub
from the 1st link above, but I can't seem to get them to do what I am looking for. Can anyone help me?
从上面的第一个链接，但我似乎无法让他们做我正在寻找的事情。谁能帮我？
I am new to VBA, so am a little uncertain about editing the code.
我是 VBA 新手，所以对编辑代码有点不确定。
回答by HansUp
It seems you're able to use the Import Wizard to successfully import a worksheet into Access. In that case, you should be able to use the DoCmd.TransferSpreadsheet Methodto do the same thing from VBA code in your Access database.
您似乎可以使用导入向导将工作表成功导入 Access。在这种情况下，您应该能够使用DoCmd.TransferSpreadsheet 方法从 Access 数据库中的 VBA 代码执行相同的操作。
The procedure below imports a single sheet named XYZ Priorityas an Access table named Import1.
以下过程将名为XYZ Priority的单个工作表导入为名为Import1的 Access 表。
I used a constant for the sheet name because you said the target sheet has the same name in all the source workbook files.
我对工作表名称使用了常量，因为您说目标工作表在所有源工作簿文件中具有相同的名称。
I constructed the table name as "Import"plus i. When you extend this to multiple workbooks, you can increment iafter each import. Or perhaps you have a different strategy for the table names; you didn't say.
我将表名构造为"Import"加上i。当您将此扩展到多个工作簿时，您可以在每次导入后增加i。或者您可能对表名有不同的策略；你没说
I split the TransferSpreadsheetstatement across several lines, and included the option names to (hopefully) make it easier to understand.
我将TransferSpreadsheet语句分成多行，并包含选项名称（希望）使其更容易理解。
My worksheet includes column names, so I have HasFieldNames:=True
我的工作表包括列名，所以我有 HasFieldNames:=True
And my workbook was created with an older version of Excel. SpreadsheetType:=acSpreadsheetTypeExcel9works with this; you may need a different value for SpreadsheetType
我的工作簿是使用旧版本的 Excel 创建的。SpreadsheetType:=acSpreadsheetTypeExcel9与此合作；您可能需要不同的值SpreadsheetType
Public Sub Demo_TransferSpreadsheet()
Const cstrSheetName As String = "XYZ Priority"
Dim i As Long
Dim strFileName As String
Dim strTableName As String
' my workbook is located in the same folder as the Access db file
strFileName = CurrentProject.Path & Chr(92) & "temp.xls"
i = 1
strTableName = "Import" & CStr(i)
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
Tablename:=strTableName, _
FileName:=strFileName, _
HasFieldNames:=True, _
range:=cstrSheetName & "$"
End Sub
