Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel - Query
- Thread starter psarros
- Start date
The easist solution, albeit not the necessarily the best depending on how your environment is set up, is to just to rename the old file (say xyz.xls) to xyz.old. Then rename the new file to xyz.xls and put it in the same place that the old one was. Excel will never know it is looking at a differant file as long as they are both formatted the same way.
Another way is to select Edit...Links.
From the dialog that opens up, you can change the linked file to suit your new requirements.
If you are using a Database query, you can press Alt and F11 to edit the macro in the Visual Basic Editor. Find the query and it will include the path and filename. Simply edit here and save the file. The query willl reference the new location. Assuming no changes required for formatting, things will work perfectly.
Hope this helps
Dave
"Probably the only Test Analyst on Tek-Tips"
Take a look at Forum1393 & sign up if you'd like
From the dialog that opens up, you can change the linked file to suit your new requirements.
If you are using a Database query, you can press Alt and F11 to edit the macro in the Visual Basic Editor. Find the query and it will include the path and filename. Simply edit here and save the file. The query willl reference the new location. Assuming no changes required for formatting, things will work perfectly.
Hope this helps
Dave
"Probably the only Test Analyst on Tek-Tips"
Take a look at Forum1393 & sign up if you'd like
Depends on the type of query (pivottable or not)
Generally, you need to use VBA and get the CONNECTION property which identifies the file used for the query. You can then change the connection property to point to a new file. Pivottables are a bit different as they have the pivottable and pivotCACHE elements which both need changing
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
Generally, you need to use VBA and get the CONNECTION property which identifies the file used for the query. You can then change the connection property to point to a new file. Pivottables are a bit different as they have the pivottable and pivotCACHE elements which both need changing
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
- Status