Refreshing Table Links to Excel Programmatically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MacNut2004 - 06 Mar 2007 00:11 GMT

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

Reply to this Message

Tim Ferguson - 07 Mar 2007 16:35 GMT

> 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

Reply to this Message

MacNut2004 - 07 Mar 2007 20:54 GMT

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

> > I have quite a few excel workbooks that I am linking to in Access.
> > Every month, the location of these workbooks (linking to individual
[quoted text clipped - 20 lines]
>
> Tim F

Reply to this Message