How can I programmatically import Excel data into an Access table?

Question

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

Answer 1

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.