vba 如何以编程方式将 Excel 数据导入 Access 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4572064/
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
How can I programmatically import Excel data into an Access table?
提问by Comrad_Durandal
I've read through a bit of the related threads, but still left me with this question. I want to write a function in an Access database application to programmatically import Excel data starting before the first two rows—which are the header and the unit delimiters.
我已经阅读了一些相关的主题，但仍然给我留下了这个问题。我想在 Access 数据库应用程序中编写一个函数，以便在前两行（即标题和单位分隔符）之前以编程方式导入 Excel 数据。
I am looking to accomplish the following things:
我希望完成以下事情：
- Being able to dynamically select the Excel file I am looking to import, perhaps using a dialog box and perhaps a file browser window.
- Insert 'common' data into each row as it's imported - like the asset number of the recorder and the recorder's designated location.
- Start the import at row #3, instead of row #1 - as the device automatically puts the header and unit of measurement information for the record up there.
- Ignore all other columns in the worksheet - the data will ALWAYS be present in columns A through G, and data will ALWAYS begin on row #3.
- 能够动态选择我要导入的 Excel 文件，也许使用对话框，也许使用文件浏览器窗口。
- 在导入时将“通用”数据插入每一行 - 例如记录器的资产编号和记录器的指定位置。
- 从第 3 行而不是第 1 行开始导入 - 因为设备会自动将记录的标题和测量单位信息放在那里。
- 忽略工作表中的所有其他列 - 数据将始终出现在 A 到 G 列中，并且数据将始终从第 3 行开始。
This is how the Excel data is commonly formatted (the dashes represent the data):
这是 Excel 数据的常用格式（破折号代表数据）：
Date Time Temp Dew Point Wet Bulb GPP RH
Co Co Co g/Kg %
---- ---- ---- ---- ---- ---- ----
---- ---- ---- ---- ---- ---- ----
I've tried the built-in Access 'Get External Data' function, but it won't skip beyond row #2 and the extra data in the Excel file throws an error when trying to import, stopping the process in its tracks.
我已经尝试了内置的 Access 'Get External Data' 功能，但它不会跳过第 2 行，并且 Excel 文件中的额外数据在尝试导入时会引发错误，从而停止该过程。
I'll be the first to admit that I have never tried to write a import function for Access before using external files, hence I am a bit of a newbie. Any help people can show me will always be greatly appreciated, and I can update this with attempted code as necessary. Thank you in advance for all of your help, everyone!
我将是第一个承认在使用外部文件之前我从未尝试为 Access 编写导入函数的人，因此我有点新手。人们可以向我展示的任何帮助将永远不胜感激，并且我可以根据需要使用尝试的代码更新它。在此先感谢大家的帮助，大家！
-- Edited 01/03/2011 @ 10:41 am --
-- 2011 年 1 月 3 日上午 10:41 编辑 --
After reading the ADO connection to Excel data thread proposed by Remou, here is some code I think mightdo the job, but I am not sure.
在阅读了 Remou 提出的 ADO 与 Excel 数据线程的连接后，这里有一些我认为可能会完成这项工作的代码，但我不确定。
Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim intField As Integer
Dim strFile As String
strFile = fncOpenFile
If strFile = "" Then Exit Sub
With cnn2
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source='" & strFile & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
.Open
End With
Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [Data$] WHERE G1 IS NOT NULL"
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenDynamic
rs2.LockType = adLockOptimistic
rs2.Open cmd2
回答by Fionnuala
You can use TransferSpreadsheet : http://msdn.microsoft.com/en-us/library/aa220766(v=office.11).aspx
您可以使用 TransferSpreadsheet ：http: //msdn.microsoft.com/en-us/library/aa220766(v=office.11).aspx
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"Employees","C:\Data\Test.xls", True, "A3:G12"
Or you can connect to Excel with an ADO connection.
或者您可以使用 ADO 连接连接到 Excel。
It may be simplest to import or link and then use a query to update the relevant table with the spreadsheet data and the common data.
导入或链接然后使用查询来更新具有电子表格数据和公共数据的相关表可能是最简单的。
