Code to Refresh Link for a specific table

[SAC]

I'm browing to the folder where some Excel Spreadsheets are and I'm using
the Dir function to go through the list of the files in the folder.

I have tables that match the names of the Excel Spreadsheets.

As I go through the list of excel spreadsheets I'd like to specifiy that the
corresponding table have it's link refreshed.

The code I now have loops through the tables in the db and then loops
through the files in the directory looking for a matching name and then
relinking that table.

Is there a way the once I have the name of the spreadsheet I can specify
that I want that table to be relinked?

Here's the code I have so far:

Function RelinkBPCSData()
Dim db As Database
Dim strPath As String
Dim InputDir, ReLinkFile As String, tblName As String
Dim strLeftConnect As String
Dim strFileName As String

Set db = CurrentDb

strPath = BrowseFolder("Refresh BPCS Data") 'This is from the Web Access
website

'Lists Files in folder
InputDir = strPath
ReLinkFile = Dir(InputDir)

For Each tbl In CurrentDb().TableDefs
If (tbl.Connect <> "") Then
MsgBox (tbl.Connect)

MsgBox (tbl.Name)
tbl.Connect = strPath
strLeftConnect = Left(tbl.Connect, 34)
strRightConnect = strPath
strFileName = Dir(strPath & "\*.*")
strFileName = Dir(strPath & "\" & tbl.Name & ".*")
Do Until strFileName = tbl.Name
If strFileName = tbl.Name Then tbl.RefreshLink
Loop
'strFileName > ""
tbl.Connect
'Loop
MsgBox (tbl.Connect)
MsgBox (strLeftConnect)
MsgBox (strRightConnect)

tbl.RefreshLink
End If
Next
End Function


Thanks

 

[SAC]

I got it. Thanks.

I set the table with db.tabledefs("Whateverthename")