Refreshing Table Links to Excel Programmatically

[Guest]

Hello,

I have quite a few excel workbooks that I am linking to in Access. Every
month, the location of these workbooks (linking to individual worksheets as
well) is going to change. How can I programmatically refresh these links so
I don't have to go in there one by one to refresh the location?

Thank you!
MN

 

[Tim Ferguson]

I have quite a few excel workbooks that I am linking to in Access.
Every month, the location of these workbooks (linking to individual
worksheets as well) is going to change. How can I programmatically
refresh these links so I don't have to go in there one by one to
refresh the location?

' create a handle to the linked table
Set db = CurrentDB()
Set tdf = db.Tabledefs("MyLinkedTable")

' make a new connection string using the correct path etc.
strConnect = _
"Excel 5.0;HDR=YES;IMEX=2;" & _
"DATABASE=M:\ThisWeeksData\GMDS_0to2.xls"

' put the string into the Connect property
tdf.Connect = strConnect

' tell Access to refresh the link and retreive the new data
tdf.RefreshLink




Hope that helps


Tim F

 

[Guest]

You're a lifesaver -- thanks so much for your help!!!