Here is a VBA routine that I wrote which you could try from a Standard
Access Code module (as opposed to a Form code module). This procedure
programmatically connects an Excel sheet to Access. Then it pulls data
from the connection table into a permanent table in Access.
Sub GetDataFromExcel()
Dim dbsTemp As Database
Dim tdfLinked As TableDef
Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("tblSheet1")
tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"
tdfLinked.SourceTableName = "Sheet1$"
dbsTemp.TableDefs.Append tdfLinked
DoCmd.RunSql "Select * Into tbl1 From tblSheet1"
End Sub
Here is an explanation of what is going on. First, you create a
tableDef object to receive the Excel table you are going to connect to.
In this example I call the object tdfLinked. Note the connection
string. The only part of the connection string that you can modify is
the location of the Excel workbook you are going to connect. Here I
connect to DATABASE=C:\Dir1\Book1.xls. But you can write whatever path
you need. Leave the rest of the connection string as is. Next, in my
example, I am connecting to Sheet1. Use whatever the sheet name is.
Note also that you have to follow the sheet name with a $ sign or it
won't work. Then, of course, you append the tableDef object to the
database.TableDefs collection. Now you run a sql query to import the
data into a new table. Assuming tbl1 does not already exit you run this
query from the VBA routine
DoCmd.RunSql "Select * Into tbl1 From tblSheet1"
And you have now imported your Excelsheet to Access.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***