Re-linking an Excel File as a Table

[Keith Wilby]

Is it possible to adapt this function to re-link to an Excel spreadsheet?
I'm sure it must be but I'm not sure of the syntax.

Many thanks.

Keith.

Function faq_ConnectLink(strTable As String, strSourceDB As String)
' This function can be run by any user who has OpenRun permission
' on the source database. It works equally well to link tables
' from scratch or to relink previously attached tables. In-line
' error handling is used to ignore any errors
' Parameters:
' strTable
' Name of the table to be linked
' strSourceDB
' Fully-qualified path and filename of the source db
'
On Error Resume Next

Dim db As Database
Dim tdf As TableDef


Set db = CurrentDb()

' Delete the link if it already exists
db.TableDefs.Delete strTable

' Create new link
Set tdf = db.CreateTableDef(strTable)

' Set the properties of the new link
' and append to the tabledefs collection
tdf.SourceTableName = strTable
tdf.Connect = ";DATABASE=" & strSourceDB
db.TableDefs.Append tdf

End Function

 

[Keith Wilby]

Is it possible to adapt this function to re-link to an Excel spreadsheet?
I'm sure it must be but I'm not sure of the syntax.

Figured it out if anyone's interested.

' Set the properties of the new link
' and append to the tabledefs collection

tdf.SourceTableName = strTable & "$"
tdf.Connect = "Excel 8.0;DATABASE=" & strSourceDB