Exporting an Excel Spreadsheet to an Access Table

[LongFeiFengWu]

This is something new to me that I've been challenged with by my superiors. I've been able to set up an ODBC connection to an Excel spreadsheet on my server, but don't know how to call the data from the spreadsheet since there are no column headings. Do I just reference A,B,C,D like I would reference columns ID,Name,EntryDate in Access?

Any Help Will Be Appreciated.

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[Tarwn]

You shoud be able to do a SELECT * rom the worksheet and then just referencethefields by index instead of by name, something like:

Dim excRS, excConn

Set excConn = Server.CreateObject("ADODB.Connection")
excConn.Open "your connection string to excel file"

Set excRS = excConn.Execute("SELECT * FROM [Sheet1$]")

If Not excRS.EOF Then
   excRS.MoveFirst

   'sample loop and output
   Response.Write "<table>"
   Do Until excRS.EOF
      Response.Write "<tr>"

      'first field
      Response.Write "<td>" & excRS(0) & "</td>"
      'second field
      Response.Write "<td>" & excRS(1) & "</td>"
      'third field
      Response.Write "<td>" & excRS(2) & "</td>"

      Response.Write "</tr>"
      excRS.MoveNext
   Loop
   Response.Write "</table>"
End If

Set excRS = Nothing
excConn.Close
Set excConn = Nothing

So basically you can select * from the sheet, which allowsyou to ignore the column name issue in your select statement. Then you can reference the columns based on their index in the recordset rather then their names, again not needing a name.


According to MS the connection is supposed to specify a name for you if you set the HDR attribute = NO in your connection string, something like Fn, ie F1, F2, F3, etc. But these don't seem to be much more helpful then just using the numeric index above. Here is the link to the page I found that on:

http://support.microsoft.com/kb/278973/EN-US/

Even though it is a VBA/VB example the ADO object should work the same regardless of wat language your using it from.

-T