Change properties of Linked Table via VBA Code

  • Thread starter Thread starter FME
  • Start date Start date
F

FME

Hello
I would like to automize the process of extracting
information out of various spreadsheets with various
worksheets.
My idea was to set up a link with a master name and then
change the pointer (i.e. the location) to the respective
spreadsheet via the Linked Table Manager.

Question 1: Can I change the pointer to a different
worksheet in the same spreadsheet via this manager ?
Question 2 : Can I manage these two operations via VBA ?

Every help is very welcome!
 
To be honest, I don't know how to do it through the Linked Table Manager. I
always do it through VBA, as I really don't like the Linked Table Manager!

If you're using Access 2000 or 2002, you'll need to set a reference to DAO
3.6 before the code below will work.

What you need to do is delete the existing linked table:

CurrentDb().TableDefs.Delete("TableName")

Then, you create a new linked table:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.CreateTableDef("LinkedToExcel")

tdfCurr.Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=D:\Data\MSAccess\LinkedTables.xls"
tdfCurr.SourceTableName = "Sheet2$"
dbCurr.TableDefs.Append tdfCurr

Set tdfCurr = Nothing
Set dbCurr = Nothing

Try creating one manually to make sure you get the correct value for Connet
in your case. Note that the SourceTableName is the name of the spreadsheet
with a $ appended to the end.
 
Super !
It worked. Thanks a lot.
-----Original Message-----
To be honest, I don't know how to do it through the Linked Table Manager. I
always do it through VBA, as I really don't like the Linked Table Manager!

If you're using Access 2000 or 2002, you'll need to set a reference to DAO
3.6 before the code below will work.

What you need to do is delete the existing linked table:

CurrentDb().TableDefs.Delete("TableName")

Then, you create a new linked table:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.CreateTableDef("LinkedToExcel")

tdfCurr.Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=D:\Data\MSAccess\LinkedTables. xls"
tdfCurr.SourceTableName = "Sheet2$"
dbCurr.TableDefs.Append tdfCurr

Set tdfCurr = Nothing
Set dbCurr = Nothing

Try creating one manually to make sure you get the correct value for Connet
in your case. Note that the SourceTableName is the name of the spreadsheet
with a $ appended to the end.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)





.
Click to expand...
 
Back
Top