external data file locations
A quick way:
In VBA editor immediate pane, ?
Worksheets("SheetName").QueryTables(1).Connection shows the connection
string; for Access normally this will include in it the path to the Access
file. Use the Replace function to replace this with the new file path, e.g:
Worksheets("SheetName").QueryTables(1).Connection= Replace(Worksheets("SheetName").QueryTables(1).Con nection ,"C:\old path\file.mdb","C:\new path\file.mdb")
sorry about any word wrapping: the above should all be one long line
--
- K Dales
"Lou Sanderson" wrote:
Windows XP, Office 2003
I use Access to build all my queries, and use 'get external data' to get the
data and build charts in Excel. As such, if I move the location of my Access
database, Excel gives me an error saying it cant find the file.
Does anyone know how I can move the Access database and then tell excel the
new location or something like that so it all still works?
Thanks,
Lou Sanderson
|