excel - 如何以编程方式将 Excel 数据导入到 Access 表中？

Question

我已经阅读了一些相关的主题，但仍然留下了这个问题。我想在 Access 数据库应用程序中编写一个函数，以编程方式导入从前两行(标题和单位分隔符)之前开始的 Excel 数据。

我希望完成以下任务:

• 能够动态选择我要导入的 Excel 文件，可能使用对话框或文件浏览器窗口。
• 在导入时将“常用”数据插入到每行中 - 例如记录器的 Assets 编号和记录器的指定位置。
• 从第 3 行(而不是第 1 行)开始导入 - 因为设备会自动将记录的标题和测量单位信息放在那里。
• 忽略工作表中的所有其他列 - 数据将始终出现在 A 至 G 列中，并且数据始终从第 3 行开始。

这是 Excel 数据的常见格式(破折号代表数据):

     Date     Time     Temp     Dew Point     Wet Bulb     GPP     RH                       Cº       Cº            Cº           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.

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!

-- Edited 01/03/2011 @ 10:41 am --

After reading the ADO connection to Excel data thread proposed by Remou, here is some code I think might do the job, but I am not sure.

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

Answer 1

您可以使用 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"

或者你可以 connect to Excel with an ADO connection .

最简单的方法可能是导入或链接，然后使用查询来使用电子表格数据和通用数据更新相关表。