Fritz Paul
asked on
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\La stLanded.x ls\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
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\La
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
ASKER
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\Las tLanded.xl s 6
tblBlockTestDetail C:\MeatSCL_CostingData\Mea tSCL_Costi ng_be.mdb 6
tblBlockTests C:\MeatSCL_CostingData\Mea tSCL_Costi ng_be.mdb 6
tblCountry C:\MeatSCL_CostingData\Mea tSCL_Costi ng_be.mdb 6
tblGeneralInfo C:\MeatSCL_CostingData\Mea tSCL_Costi ng_be.mdb 6
tblIngredient C:\MeatSCL_CostingData\Mea tSCL_Costi ng_be.mdb 6
qryNamesOfTables.xlsx
Please.
Results:
name connect Database type
LastLanded Excel 8.0;HDR=YES;IMEX=2; C:\MeatSCL_CostingData\Las
tblBlockTestDetail C:\MeatSCL_CostingData\Mea
tblBlockTests C:\MeatSCL_CostingData\Mea
tblCountry C:\MeatSCL_CostingData\Mea
tblGeneralInfo C:\MeatSCL_CostingData\Mea
tblIngredient C:\MeatSCL_CostingData\Mea
qryNamesOfTables.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
!!!!!! You really are clever.
Now I just want to go on re-linking all the time.
Thanks a lot.
Now I just want to go on re-linking all the time.
Thanks a lot.
SELECT [name],[connect],Database,
FROM msysobjects
WHERE [type]=6