Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.crashesgpfs,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
external data file locations
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 |
#2
Posted to microsoft.public.excel.crashesgpfs,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
external data file locations
Edit the query - right click on the query and choose Edit=Query.
-- Regards, Tom Ogilvy "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 |
#3
Posted to microsoft.public.excel.crashesgpfs,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.crashesgpfs,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
external data file locations
You'll have to update Connection and CommandText attributes of the
querytable: '------------ Sub ModQuery() Const oldpath = "U:\Databases\mydatabase" Const newpath = "C:\mydatabase" Dim qt As QueryTable Set qt = ActiveSheet.QueryTables(1) With qt .Connection = Replace(.Connection, oldpath, newpath) .CommandText = Replace(.CommandText, oldpath, newpath) End With End Sub '----------- HTH -- AP "Lou Sanderson" a écrit dans le message de ... 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 |
#5
Posted to microsoft.public.excel.crashesgpfs,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
external data file locations
K Dales,
OK, this changed the location of the "DBQ" (whatever that is) in the connection string to the correct new location, but the "DefaultDir" remains at the old location, and my error still exists.... Do I need to change the "DefaultDir" location as well, and if so can I have help with that? "K Dales" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import external data problem | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Import External Data Source File Location Changed | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |