Adding linked table with VBA

[Karl Irvin]

Is there way to use VBA to link to an Excel spreadsheet?

I tried to add a new tdf to the xls file but couldn't add the tdf without
also creating field and when I created a field, the table was a local table
and I couldn't set the connect string to make it a linked table.

[Bri]

Karl Irvin wrote:

Is there way to use VBA to link to an Excel spreadsheet?

I tried to add a new tdf to the xls file but couldn't add the tdf without
also creating field and when I created a field, the table was a local table
and I couldn't set the connect string to make it a linked table.

Take a look at the CreateTableDef Method in Help for more detail.
This function works:

Function LinkExcel() As Boolean
On Error GoTo Err_
Dim myDB As DAO.Database, tbl As TableDef
Dim stSource As String, stConnect As String

Set myDB = CurrentDb()
stSource = "Sheet1$"
stConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\myFolder\MySheet.xl s"

Set tbl = myDB.CreateTableDef("mySheet")
tbl.Connect = stConnect
tbl.SourceTableName = stSource

myDB.TableDefs.Append tbl

LinkExcel = True

Exit_:
Set tbl = Nothing
Set myDB = Nothing
Exit Function

Err_:
LinkExcel = False
MsgBox Err.Description
Resume Exit_

End Function