5

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
Comrad_Durandal
  • 651
  • 2
  • 10
  • 23

1 Answers1

6

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.

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • The problem the above solution has is that I'd need to know ahead of time the range of the data, and it really depends on how much this device pulls in during it's data logging duties. I'll take a look at this when I get back into the office tomorrow - and see if I could provide more information. – Comrad_Durandal Jan 03 '11 at 04:38
  • It is always possible to connect through automation and get the range, though it will slow things up a little. – Fionnuala Jan 03 '11 at 11:48
  • I have posted a test statement above, amended to my previous post. Was this what you had in mind? – Comrad_Durandal Jan 03 '11 at 23:00
  • Is G1 a header and is you sheet called Data? You have HDR=Yes, so the first row of the selected range will be used as column headers. – Fionnuala Jan 03 '11 at 23:19
  • 1
    I have been checking out your example, and I find that "SELECT * FROM [Sheet1$A3:G65536]" will only return records for used range, though I did not test very carefully. Note that there are a few ways you can update a table from Excel to Access without stepping through rows, one is by using IN. Post back if you need examples. – Fionnuala Jan 03 '11 at 23:35
  • I am confused by what you mean 'IN'. Are you referring to a SQL instruction, or is there a function called IN in VBA? I can't claim to be an expert, so hence my confusion. The example you gave - 'A3:G65535' - sounds like a working option, but what if I go beyond 65535 rows? Would adding something like 'WHERE G IS NOT NULL' help speed things up with the query? – Comrad_Durandal Jan 04 '11 at 16:41
  • Where G is not null is fine, as long as you do _not_ mean the column called G, because that reference will not exist in ADO, the columns will be either named after the first row if `HDR=Yes`, or from `F1` to `Fn`, according to the columns selected if `HDR=No` (numbering starts from first column selected, not necessarily from A). If you are selecting the whole spreadsheet, even using , say, `F7 Is Null` you will not be able to exclude the first three rows, whereas using `A3:Gmaxrows` will allow you to do that, I used 65535 to signify the maximum number of rows in excel. – Fionnuala Jan 04 '11 at 17:09
  • IN is an SQL keyword that is difficult to google for, as you can imagine :) – Fionnuala Jan 04 '11 at 17:11
  • Row 65535 would be appropriate for Excel 2003 and before, but for 2007 and 2010, you could have more rows, as they bumped the limit (you'd have to look it up). – David-W-Fenton Jan 05 '11 at 03:22
  • IN in SQL actually has two different meanings, one for subqueries and one for the FROM clause (where you specify a connect string for the location of the tables you're querying). – David-W-Fenton Jan 05 '11 at 03:23