vba 在 Access 中导入 Excel 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11866255/
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 Excel data in Access
提问by Estarius
I have a table in my Access application which needs to be filled with data in a bunch of Excel files. I tried this code:
我的 Access 应用程序中有一个表，需要在一堆 Excel 文件中填充数据。我试过这个代码：
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strTable, strExcelpath, True
But it overwrite the data in my access table each time instead of appending it and it gives absolutely no control over what is being sent.
但是它每次都会覆盖我访问表中的数据而不是附加它，并且它绝对无法控制正在发送的内容。
I need to find a way to simply append the data from an Excel file to my Access table, both files have the same structure so I would like to know if there is a way to import it line by line without specifying the columns. However, for personal knowledge and fear of user uses, I would also like to know how to import it considering the lines and columns.
我需要找到一种方法来简单地将 Excel 文件中的数据附加到我的 Access 表中，这两个文件具有相同的结构，所以我想知道是否有一种方法可以在不指定列的情况下逐行导入它。但是，出于个人知识和对用户使用的恐惧，我也想知道如何考虑行和列来导入它。
Thanks !
谢谢 ！
EDIT: Code with the select on the Excel file:
编辑：在 Excel 文件上选择代码：
Dim cn As ADODB.Connection
Dim strQuery As String
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Application.CurrentProject.Path & "\Excel\test.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
strQuery = "INSERT INTO tblClients " & _
"SELECT * FROM [Excel 8.0;HDR=YES;DATABASE =" & Application.CurrentProject.Path & "\Excel\test.xls].[tblImport$]"
DoCmd.RunSQL strQuery
回答by Fionnuala
You can also refer to an Excel sheet or range in a query:
您还可以在查询中引用 Excel 工作表或范围：
INSERT INTO Table1 ( ADate )
SELECT SomeDate FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$a1:a4]
Or
或者
INSERT INTO Table1
SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$]
In a procedure:
在一个程序中：
Sub RunThisQuery()
strQuery = "INSERT INTO tblClients " & _
"SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
& Application.CurrentProject.Path & "\Excel\test.xls].[tblImport$]"
CurrentDB.Execute strQuery, dbFailOnError
End Sub
Note that you had a space in your code after DATABASE. It must read DATABASE=, no space.
请注意，您的代码中在DATABASE. 它必须阅读DATABASE=，没有空格。
