Exclude Excel file from re-linking in Access database with VBA

I have a MS Access front end which is linked to a back end database. I also link to one Excel.xls 2003 file. From time to time I want to relink to another back end Access database with similar tables, but I do not want to relink the Excel file.
My database is Access 2003, although I run it in Access 2010, all on my local C drive.
The Excel file is named LastLanded and the sheet that is linked is Named Sheet1. When I go to linked table manager, I see it is linked as "LastLanded  (C:\MeatSCL_CostingData\LastLanded.xls\Sheet1$)" Note the $ sign after the Sheet1.
I have VBA code like below. When I want to exclude an Access table from re-linking it works fine, but when I try it with the Excel file, I get a message:
"The Microsoft Access database engine could not find the object 'Sheet 1$'. Make sure the object exists and that you spell its name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."
This also happens even if I add "\Sheet1" or "\Sheet1$" behind the "LastLanded" name.
Can a clever person help me out please.


VBA Code that I use
    Dim dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set dbs = CurrentDb
    Set Tdfs = dbs.TableDefs
    '
    'Loop through the tables collection
    DoCmd.Hourglass True

    For Each Tdf In Tdfs

        If Tdf.SourceTableName <> "LastLanded" Then

            If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
                Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
                Tdf.RefreshLink 'Refresh the link
            End If

        End If
Fritz PaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
create and run this query to see the correct properties of the linked tables


SELECT [name],[connect],Database,[type]
FROM msysobjects
WHERE [type]=6
0
Fritz PaulAuthor Commented:
Thanks, I've done it, but I don't know what to do with it. Below are my results. I also attach the results in Excel format. LastLanded is the file that I want to exclude. Any further advice?
Please.
Results:
name      connect      Database      type
LastLanded      Excel 8.0;HDR=YES;IMEX=2;      C:\MeatSCL_CostingData\LastLanded.xls      6
tblBlockTestDetail            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblBlockTests            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblCountry            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblGeneralInfo            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblIngredient            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
qryNamesOfTables.xlsx
0
Rey Obrero (Capricorn1)Commented:
why are you using

 Tdf.SourceTableName ?

try replacing this part

Tdf.SourceTableName

with

Tdf.Name
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fritz PaulAuthor Commented:
!!!!!! You really are clever.
Now I just want to go on re-linking all the time.
Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.