Access 97: Help relinking Excel Tables

Question

I use Access to link -MANY- Excel spreadsheets. I've used the Add-In "Linked Table Manager" to change the directory that the sheets are stored in -but- it doesn't seem to let you change the name of the "sheet" it's linked to.. :-/ I would like to edit these using VBA(since they're are so many), if it's possible.

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Microsoft Access Database Discussion List <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Access 97: Help editing Linked Tables
Date: 22 July 1997 3:46

Chris,

I think you are right - you cannot "reattach" Excel sheets within Workbook - you can change the name of the "sheet" by only dropping old link and recreating it with new sheet name - SourceTableName is read-only
property of TableDef object stored in Tabledefs collection :( Don't forget to append '$' sign to the end of sheet's name when you link it through VBA.

Here is an example:

Dim dbs As Database
Dim tdef As TableDef
Dim strLink As String
    
Set dbs = CurrentDb()
    
' Delete old attachment
On Error Resume Next
dbs.TableDefs.Delete "Book1"
    
'Create new attachment   
Set tdef = dbs.CreateTableDef("Book1")
strLink = "Excel 5.0;DATABASE=C:\Book1.xls;"
tdef.Connect = strLink
tdef.SourceTableName = "Sheet2$"
dbs.TableDefs.Append tdef