try this code. I will link your Excel sheet to Access programatically.
Just replace the workbook name "Book1.xls" with the name of your actual
workbook (where ever it says Book1 - replace that with the name of your
actual workbook) and replace "Sheet1$" with the actual name of your
spreadsheet - make sure to include the $ (dollar sign is required). If
you have spaces in your sheet name, you need to eliminate the spaces -
use underscore _. Add this code to a standard module. Then press the
F5 function key to run the code. Then you can just use queries against
the linked table. Oh, and the subroutine assumes that your Access mdb
and your Excel file reside in the same directory.
-------------------------------------------------------
Sub ConnectToExcel()
Dim DB As Database, tdf As TableDef, strPath As String
Dim strTable As String, strConnect As String
Dim strSourceTable As String
Set DB = CurrentDb
strPath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
strPath = strPath & "Book1.xls"
strTable = "Book1"
strConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strPath
strSourceTable = "Sheet1$"
For Each tdf In DB.TableDefs
If tdf.Name = "Book1" Then DB.TableDefs.Delete tdf.Name
Next
Set tdf = DB.CreateTableDef(strTable)
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTable
DB.TableDefs.Append tdf
Application.RefreshDatabaseWindow
End Sub
--------------------------------------------------------
The nice thing about this routine is that if you have several worksheets
you can write a loop and loop through a list of spreadsheets (use the
Array function to create an array of spreadsheet names and loop through
the array - goto help lookup Array)
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
