Grabbing data from excel sheet column into access table

[syvman]

Hi everyone... I am pulling my hair out trying to do this, and was
wondering if someone could give me some assistance... I've searched
for
a while, looking at different methods - but I keep coming up empty when
I try it myself.
I have an Excel spreadsheet containing several worksheets. I'd like to

be able to take all of the data from the second column (B) of each
worksheet and append that raw data to an access table. The columns in
the spreadsheet do not have headers for use as field names (it's my
assumption that my table should have a field named "F2")...
This action needs to take place on an event - I need to be able to grab

updated data out of the spreadsheet at any time (a one-time import
won't work).
I only need the data from the second column, and it's all text data.
I've looked into several different methods already, and I think the ADO

connection is the best way to go, but I'm having no luck establishing
the connection to the spreadsheet and/or extracting any data from it.
Any help (or code examples) would be greatly appreciated. Thanks!
PS - here's what I've used to establish my connection to the excel file

- I get an error: "Could not find installable ISAM"... The path to my
excel dll file is correct in the registry.
Once I get past the connection problem, I have no idea how to append
the data from the spreadsheet to the database. Anyone's help would be
GREATLY appreciated. Thanks!
-Terry


Dim xlpath As String
Dim cn As New ADODB.Connection


xlpath = "c:\test\book1.xls"


With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlpath & ";Extended
Properties=Excel 8.0;HDR=No;"
.CursorLocation = adUseClient
.Open
End With

 

[Guest]

To do what you want to do, you will need to open the spreadsheet using
Automation.

Go to the "Programming" thread of the Access newsgroups and search for
"Excel Automation". You will find several threads that will be of help.

As for how to get the data you want, if you can record a macro in Excel that
will get the data you want, you can then use protions of that macro in your
automation.