Getting data from excel spreadsheet 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 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

[Mike]

Setup a linked table to the spreadsheet. You need to link to the
spreadsheet three times, one for each sheet. Then you can make a union
query that pulls the second column from each sheet. then update based
on that union query.

Mike

[Syvman]

Thanks for the suggestion, Mike... I was sort of hoping to avoid using
the linked table for one reason - other worksheets could be added to
this spreadsheet file at any given notice, and I'd like the ability to
be able to detect this and pull the data from those sheets, as well.
With a link, I'd be required to go in and manually establish another
linked table and add that link to my query. This is the route I
originally was going to go, and if I can't figure out why my ADO
connection isn't working, I'll resort to it. Thanks for your reply!

[fredg]

On 9 Dec 2005 08:28:12 -0800, sy****@yahoo.com wrote:

Hi everyone... I am pulling my hair out trying to do this, and was
wondering if someone could give me some assistance...
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

Can't you ise the TransferSpreadsheet method?

In the Click event of a command button:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyImportTable1","c:\YourPath\SpreadsheetName1.xls ", False,
"WorksheetNameA!B:B"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyImportTable2", "c:\YourPath\SpreadsheetName2.xls", False,
"WorksheetNameB!B:B"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyImportTable3", _
"c:\YourPath\SpreadsheetName3.xls", False, "WorksheetNameC!B:B"

Dim strSQL As String
strSQL = "INSERT INTO MyImportTable1 ( F1 )SELECT MyImportTable2.F1
FROM MyImportTable2;"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO MyImportTable1 ( F1 )SELECT MyImportTable3.F1
FROM MyImportTable3;"
CurrentDb.Execute strSQL, dbFailOnError

The above will import the data from the 3 named worksheets column B.

Then the 3 tables are combined into the first table.

You can then delete the second and third tables.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Syvman]

This would work great (I just tried it and it will work fine) if there
were a way I could determine how many worksheets (and their names) are
in the spreadsheet file automatically. I was also kind of hoping I
could deal with each cell individually so I could test for certain
conditions before placing the data into the table (check for empty/null
cells, etc.). But I could always do that after the data is placed in a
temporary table - I just need a way to see what worksheets the
spreadsheet has and their respective names.

Thanks for your reply, fredg!

[Syvman]

Actually, the more I play with it, the more I realize that this method
would work GREAT if I could just determine the number of (and names of)
the individual worksheets beforehand. Then I could dynamically loop
through all of them... Is there a way to just pull the number of
worksheets and their names out of an excel file? thanks!

[Terry Kreft]

Something like

' *********** Code Start ***********
Function ListWorkSheets(XLFileName As String) As Variant
Dim loCon As ADODB.Connection
Dim loCat As ADOX.Catalog
Dim loTab As ADOX.Table
Dim varRet As Variant
Dim intX As Integer

Set loCon = New ADODB.Connection
With loCon
.ConnectionString = GetXLConnect(XLFileName)
.Open
End With

Set loCat = New ADOX.Catalog
varRet = Array(0)

With loCat
Set .ActiveConnection = loCon
ReDim varRet(0 To .Tables.Count - 1)
For intX = 0 To .Tables.Count - 1
varRet(intX) = .Tables(intX).Name
Next
End With

Set loCat = Nothing
loCon.Close
Set loCon = Nothing

ListWorkSheets = varRet
End Function
Private Function GetXLConnect(XLFileName As String) As String
Const BASE_CONNECT = "Driver={Microsoft Excel Driver (*.xls)};" _
& "DriverId=790;" _
& "Dbq=<<TAG_SPREADSHEET>>;" _
& "DefaultDir=<<TAG_DIRECTORY>>"
Const TAG_SPREADSHEET = "<<TAG_SPREADSHEET>>"
Const TAG_DIRECTORY = "<<TAG_DIRECTORY>>"

GetXLConnect = BASE_CONNECT
GetXLConnect = Replace(GetXLConnect, TAG_SPREADSHEET, XLFileName,
Compare:=vbTextCompare)
GetXLConnect = Replace(GetXLConnect, TAG_DIRECTORY, Left(XLFileName,
Len(XLFileName) - Len(Dir(XLFileName)) - 1), Compare:=vbTextCompare)
End Function
' *********** Code End ***********

You need a reference to ADOX for this to work.

A test call could be

' *********** Code Start ***********
Sub TestListWorkSheets()
Dim varRet As Variant
Dim intX As Integer

varRet = ListWorkSheets("C:\Book1.xls")

If IsArray(varRet) Then
For intX = LBound(varRet) To UBound(varRet)
Debug.Print varRet(intX)
Next
End If

End Sub
' *********** Code End ***********

--
Terry Kreft

"Syvman" <sy****@yahoo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Actually, the more I play with it, the more I realize that this method
would work GREAT if I could just determine the number of (and names of)
the individual worksheets beforehand. Then I could dynamically loop
through all of them... Is there a way to just pull the number of
worksheets and their names out of an excel file? thanks!