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.
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
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.