如何以编程方式将Excel数据导入Access表？ [英] How can I programmatically import Excel data into an Access table?

问题描述

我已经阅读了一些相关的主题，但仍然让我有这个问题。我想在Access数据库应用程序中编写一个函数，以编程方式导入前两行之间的Excel数据 - 这是头和单元分隔符。

我正在寻找要完成以下事情：

• 能够动态选择我要导入的Excel文件，也许使用一个对话框，也许文件浏览器窗口。


• 在导入的每一行中插入共同数据，如录音机的资产编号和录音机的指定位置。


• 在行＃3而不是行＃1开始导入，因为设备会自动将记录的标题和测量单位单位放在那里。


• 忽略所有其他列工作表 - 数据将始终存在于列A至G中，数据将始终从第3行开始。

这是如何Excel数据通常是格式化的（虚线表示数据）：

 
日期时间温度露点湿球GPP RH 
CºCºC / kg％
 ---- ---- ---- ---- ---- ---- ---- 
 ---- ---- ---- ---- ---- ----  -  -  
 

我已经尝试了内置的Access获取外部数据功能，但不会跳过第2行而Excel文件中的额外数据在尝试导入时会导致错误，从而阻止其进程。

我将首先承认我从未尝试在使用外部文件之前写入Access的导入功能，因此我有点新手。任何帮助人们都可以非常感激，我可以根据需要对尝试的代码进行更新。感谢您提前为大家提供的所有帮助。

- 已编辑01/03/2011 @ 10:41 am -

在阅读由Remou提出的Excel数据线程的ADO连接之后，以下是我认为可能的代码，但我不确定。

  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 
如果strFile =然后退出Sub 
 
带有cnn2 
 .Provider =Microsoft.Jet.OLEDB.4.0
 .ConnectionString =Data Source ='& strFile& ;& 扩展属性='Excel 8.0; HDR =是; IMEX = 1'
 .Open 
结束
 
设置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 
  

解决方案

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

或者您可以使用AD连接到Excel O连接。

可能最简单的是导入或链接，然后使用查询更新相关表格与电子表格数据和公共数据。 / p>

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.

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.

This is how the Excel data is commonly formatted (the dashes represent the data):

     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

解决方案

You can use 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.

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.

这篇关于如何以编程方式将Excel数据导入Access表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！