VBA：在工作表的工作簿中导入特定变量命名的工作表以访问表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17838873/
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
VBA: Import Specific Variable Named Sheet in Workbook of Sheets to Access Table?
提问by user2296381
I'm looking for a way to import a specific tab into my Access table. Normally I do a TransferSpreadsheet type of code, so this is rather new to me.
我正在寻找一种将特定选项卡导入我的 Access 表的方法。通常我会做 TransferSpreadsheet 类型的代码，所以这对我来说是新的。
I need the user to be able to open the file from any directory - followed by have the program import a specific spreadsheet that can have a semi-variable name.
我需要用户能够从任何目录打开文件 - 然后让程序导入一个可以具有半变量名称的特定电子表格。
The spreadsheet that needs to be imported is always " Details".So obviously, right now the file would contain a tab called July Details. So my logic with this bit of code was to find the spreadsheet with LIKE (or *) Detail. I'm not sure if this works however, and I'm not sure how best to transfer this to my Access Table since I'm so used to TransferSpreadsheet. Any help is appreciated!
需要导入的电子表格始终是“详细信息”。很明显，现在该文件将包含一个名为“七月详细信息”的选项卡。所以我用这段代码的逻辑是找到带有 LIKE（或 *）Detail 的电子表格。但是，我不确定这是否有效，并且我不确定如何最好地将其传输到我的访问表，因为我已经习惯了 TransferSpreadsheet。任何帮助表示赞赏！
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = ActiveWorkbook
ImportFileName = cmdFileDialog("Select File to Import: ")
Set wb2 = Workbooks.Open(FileName:=ImportFileName)
For Each Sheet In wb2.Sheets
If Sheet.Name Like Detail Then
Sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
End If
Next Sheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, DataTable, ImportFileName, True, DataRange
EDIT: Let me clarify: Once I find the sheet with Detailsin it, I am not sure how to import it into an Access table like I need. That is my last problem with this.
编辑：让我澄清一下：一旦我找到包含详细信息的工作表，我不确定如何将它导入到我需要的 Access 表中。这是我最后的问题。
回答by Derek Cheng
This is a response for the last part of your question! To upload a table in Excel to an Access Table you will need the following:
这是对您问题最后一部分的回答！要将 Excel 中的表上传到 Access 表，您需要以下内容：
- ADODB library (Microsoft ActiveX Data Object 2.x)
- array operations
- ADODB 库（Microsoft ActiveX 数据对象 2.x）
- 数组操作
You will be using the following objects and methods in ADODB:
您将在 ADODB 中使用以下对象和方法：
- Connection
- Recordset
- AddNew
- 联系
- 记录集
- 添新
Here is a generic example of using ADODB to append new records to a table in Access:
以下是使用 ADODB 将新记录追加到 Access 中的表的一般示例：
(If you have more than one record that you need to add, you will need to write a loop to create different array containing the values and call the AddNew function repeatedly. As far as I know ADODB does not support batch upload of records*correct me if i'm wrong here)
（如果您需要添加多个记录，则需要编写一个循环来创建包含这些值的不同数组并重复调用 AddNew 函数。据我所知 ADODB 不支持批量上传记录*正确我，如果我在这里错了）
Dim objcon As ADODB.Connection
Dim objrec As ADODB.Recordset
Dim strSQL As String
Dim strCon As String
'important strings you'll need
strSQL = "SELECT * FROM " & TABLE_NAME & ";"
strCon = "DSN=MS Access Database;DBQ=" & FULLPATH_OF_DATABASE & ";"
'open your connection to the database
Set objcon = New ADODB.Connection
objcon.Open strCon
'open your recordset from database
Set objrec = New ADODB.Recordset
objrec.Open strSQL, objcon, adOpenKeyset, adLockOptimistic
'adding a new record
objrec.AddNew Array("FIELD_1", "FIELD_2", "FIELD_3"), Array("Value1", "Value2", "Value3")
Set objrec = Nothing
Set objcon = Nothing
Let me know if that helps!
如果有帮助，请告诉我！
回答by user2296381
Looks like I found a way with the DoCmd.TransferSpreadsheet in combination. Thank you everyone. If this is a little ugly feel free to correct or give me a more proper way.
看起来我找到了一种结合 DoCmd.TransferSpreadsheet 的方法。谢谢大家。如果这有点难看，请随时纠正或给我一个更合适的方法。
ImportFileName = cmdFileDialog("Select File to import : ")
DoCmd.SetWarnings False
Dim xlApp As Object
Dim xlWrk As Object
Dim xlSheet As Object
Dim db As Database
Dim SheetName As String
Set xlApp = CreateObject("Excel.Application")
Set xlWrk = xlApp.Workbooks.Open(ImportFileName)
Set db = CurrentDb
For Each Sheet In xlWrk.Sheets
If Sheet.Name Like "*Detail*" Then
Set xlSheet = xlWrk.Sheets(Sheet.Name)
SheetName = Sheet.Name
End If
Next Sheet
DoCmd.TransferSpreadsheet _
transfertype:=acImport, _
TableName:="tblImport", _
FileName:=ImportFileName, _
HasFieldNames:=True, _
Range:=SheetName & "!A1:BM" & ls_last_row
