Linked tables to Excel - VBA updating

  • Thread starter Thread starter Chris L
  • Start date Start date
C

Chris L

Every month I need to copy the entire folder that
an Access database and various Excel spreadsheets are
stored in to make a new month's work.

I have about 15 tables in the database, each linked to one
of three different Excel spreadsheets.

What code can I run to automatically update these
links on opening the database to the existing excel file
but in the new location.

ie - I want to automate the linked table manager wizard so
I don't have to manually update the locations.
 
Hi Chris,

Try the following piece of code:

Sub Update_Links()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb()

oldpath = DFirst("[FolderName]","tblFolderName")
newpath = CurrentProject.Path

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 10) = "Excel 5.0;" Then
lnk = Replace(lnk, oldpath, newpath)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next

Set tbl = Nothing
Set db = Nothing

strSQL = "SELECT [currentproject].[path] AS FolderName INTO tblFolderName"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub


Note: You will need to add a proper DAO reference to your project, if not
already there. "Proper" is DAO 3.51 for A97, DAO 3.6 for A2K or later.
Also: the trick is that the db "remembers" the last folder it resided (and
thus the pathpart of the links) by storing them in a single field
(FolderName), single record table called tblFoldername. In order for the
code not to fail when you fist run it, you need to create that table
manually and type in the current path, or manually run the last four lines
of code (starting strSQL = ...) before you run it all.

HTH,
Nikos
 
Back
Top