Linking Tables to Excel Spreadsheet

[SAC]

I'm using code to relink excel files to tables. It's been working fine for
months, but now that we are changing the method of making the excel files it
doesn't work.

I noticed that the old files each had a spreadsheet name of "Database Query
Results" and the new files' spreadsheet names are the name of the file.

When I change the sheet name to "Database Query Results" it relinks.

Any idea as to why this is happening and how I can change the code to
accommodate this?

Here's the code:

Dim db As Database
Dim tdf As DAO.tableDef
Dim tdfs As DAO.TableDefs
Dim strPath As String
Dim InputDir, ReLinkFile As String, tblName As String
Dim strLeftConnect As String
Dim strFileName As String
Dim intCounter As Integer
Dim strSpreadsheetName
Dim strConnection As String
Dim strDateOfUpdate As String
Dim strMonth
Dim strDay
Dim strYear
Dim strSQL As String

DoCmd.SetWarnings False

Set db = CurrentDb
Set tdfs = db.TableDefs

'Sets path to folder
strPath = BrowseFolder("Refresh BPCS Data") 'This is from the Web Access
website
strSQL = "UPDATE tblSystem SET tblSystem.PathToBPCS = '" & strPath &
"';"
'MsgBox (strSQL)
DoCmd.RunSQL strSQL

strDateOfUpdate = Right(strPath, 6)
strSpreadsheetName = Dir(strPath & "\*.*")
strFileName = Left(strSpreadsheetName, 6)

Set tdf = db.TableDefs(strFileName & "tmp") 'Add tmp onto the end of the
table name so it's not the same as the original table

'This is the connection string without the path
strLeftConnect = Left(tdf.Connect, 34) 'Path to the folder
'MsgBox (strLeftConnect)

'This is the path to the folder
strRightConnect = strPath & "\" 'Path to the folder

strRightConnect = strRightConnect & strSpreadsheetName

'MsgBox (strRightConnect)

strConnection = strLeftConnect & strRightConnect
'MsgBox (strConnection)
'MsgBox (tdf.Name)
tdf.Connect = strConnection
tdf.RefreshLink


Do While strSpreadsheetName <> ""
strSpreadsheetName = Dir
strFileName = Left(strSpreadsheetName, 6)
If strFileName = "" Then GoTo endFunction
Set tdf = db.TableDefs(strFileName & "tmp")
' MsgBox (tdf.Name)
Forms![frmBPCSConversion]![Label1].Caption = "ReLinking " &
strSpreadsheetName
Forms![frmBPCSConversion].Repaint

strLeftConnect = Left(tdf.Connect, 34) 'Path to the folder
strRightConnect = strPath & "\" 'Path to the folder
strRightConnect = strRightConnect & strSpreadsheetName
strConnection = strLeftConnect & strRightConnect
tdf.Connect = strConnection
tdf.RefreshLink
Loop