Files Moved Need to relink everything

[ghcrazy8]

IT has moved our files to a new server. We currently have ALOT of excel tables and/or pivot tables linked to Access Database tables/queries. Is there a quick and easy way to change these links from J:\\..... to \\gad.intra.net\.......


Appreciate any advice.

 

[kjv1611]

I would think you'd have to write a VBA script to handle that. If so, you can look at the QueryTables and ListObjects objects in Excel, and that will get you what you need.

If you want to go down that path, here's a good explanation of how to use ListObjects in Excel 2003, 2007, and 2010, at least - and I'm sure you can use the same in other versions -well, I specify those, b/c the author notes the differences from 2003 to 2007 where Table objects really became useful, at least in my opinion:
... well, having trouble finding the link at the moment. If I find later, will post back - I have it stored in my favorites/bookmarks somewhere but not remembering enough right now.

Here's another reference worth noting - I know I've looked here at least once in the past:

http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

And here's another one that looks like it details some of the steps, at least, that the other site which I can't find at the moment shares:

http://excelandaccess.wordpress.com/2013/01/07/working-with-tables-in-vba/

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57