Here is some code you could try (copy and paste directly into a Standard
code modue)that links your Excel sheet to Access and then reads the data
in the sheet with a recordset object variable. I think I got most of
this code from the Access97 Help Files on tabledefs connect property
(except for the $ which you have to append to the sheet name- which here
is "sheet1$" - they forgot to mention about the $). Note: if the
ConnectOutput... line errors out, just get rid of the underscores and
put everything on one line - I broke it up with the underscores so it
would fit on the page here.
Sub TestConnect()
Dim DB As Database
Set DB = CurrentDb
ConnectOutput DB, "ExcelTbl", _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\dir\Test1.xls", _
"Sheet1$" '--if this line errors out - get rid of "_"
End Sub 'and put it all on one line
Sub ConnectOutput(dbsTemp As Database, strTable As String, strConnect As
String, strSourceTable As String)
Dim tdfLinked As TableDef
Dim rstLinked As Recordset
Dim intTemp As Integer
' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef(strTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked
Application.RefreshDatabaseWindow
Set rstLinked = dbsTemp.OpenRecordset(strTable)
' Display the first three records of the linked table.
intTemp = 1
With rstLinked
Do While Not .EOF And intTemp <= 3
Debug.Print , .Fields(0), .Fields(1)
intTemp = intTemp + 1
.MoveNext
Loop
.Close
End With
End Sub
Rich
*** Sent via Devdex
http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
