Update Link to Excel Worksheet

[Bryan in Bakersfield]

I am trying to update a link to an Excel worksheet. We receive an Excel file
that's based on the date name. I have been able to change the link to any
Excel file, but when I try to change the sheet name, I receive the error -
Run Time error 3268 Cannot set this property once the object is part of a
collection.
on the line that updates the sheetname - .SourceTableName = SheetName + "$"
In the debugger, sheetname does return the correct name of the sheet. If I
rem this line, the link (.Connect) is updated but with the original sheet
name.

Here's my code to check out

Private Sub ChangeFile_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)

Me.ChangeFile.SpecialEffect = 0
Me.ChangeFile.BackStyle = 0

'On Error GoTo FunctionError

Dim PathCurrent As String
Dim PathNew As String
Dim FilterItem As String
Dim TableConnection As String
Dim TableName As String
Dim dbs As Database
Dim tblPended As TableDef
strSQL = ""

TableName = "Pended"
Set dbs = CurrentDb()
'On Error GoTo TableError
Set tblPended = dbs.TableDefs(TableName)
'On Error GoTo FunctionError

PathCurrent = tblPended.Connect
TableConnection = Left(PathCurrent, (InStr(1, PathCurrent, "DATABASE="))
+ 8)
PathCurrent = Right(PathCurrent, Len(PathCurrent) - (InStr(1,
PathCurrent, "DATABASE=") + 8))

PathNew = PathCurrent
SheetName = tblPended.SourceTableName 'Keep old names unless changed
FilterItem = "Microsoft Excel Worksheets (*.xls)" & vbNullChar & "*.xls"
& vbNullChar

PathNew = ahtCommonFileOpenSave(4, PathCurrent, FilterItem, , , , "Open
Status File", , True)
If PathNew = "" Then GoTo ExitFunction
TableConnection = TableConnection + PathNew

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = GetObject(PathNew)

For Each oSheet In oBook.Worksheets
strSQL = strSQL + oSheet.Name + ";"
Next

Set oSheet = Nothing
oBook.Close False
Set oBook = Nothing

DoCmd.OpenForm "ChangeFile", acNormal, , , , acDialog ' select
Sheetname form
With tblPended
.SourceTableName = SheetName + "$"
.Connect = TableConnection
End With

tblPended.RefreshLink
Me.CurrentFile.Requery

ExitFunction:
dbs.Close
Set dbs = Nothing
DoCmd.SetWarnings True
Exit Sub

Sheetname and strSQL are global.
The ChangeFile form simply displays the worksheets in a list box for the
user to choose one. If I remove it, the result is the same.

Any help in changing the sheet name would be appreciated, even if it's in
the "You can't do that" vein.

 

[Douglas J. Steele]

Simply delete the existing TableDef object and create a new one.

 

[Bryan in Bakersfield]

Thanks Douglas, that was what I was going to do next.

I had just thought it should work and was trying to figure out why it
wouldn't.