Relative Path in Connection String

[Breunus]

I am using Excel 2007 to import data from another Excel workbook using
Microsoft Query 2000. The solution works well with a fixed path, but I would
like the connection string to use a relative path (for example the current
directory) so that the files can be copied to a different location and still
function.

I have seen a number of web questions about this topic, but most involve a
programming solution. Is this possible just using Excel? Eg. to specify an
ODBC connection string that would solve the problem.

 

[Jim Thomlinson]

Not possible without resorting to code...

 

[Lori]

Relative paths can be used in connection strings (as well as formulas and
dialog boxes) but you need to be careful as they are relative to the active
folder¹. So if you were to include a filename without a path it would need to
be in the active folder.

To make sure the current workbook folder is activate you can try choosing
Save As then Cancel, then run the query.

¹ Excel makes a distinction between active folder and workbook folder, which
can be seen from their VBA equivalents: curdir and activeworkbook.path. The
active folder is the one that you see when you click File Open, it can be
changed simply by selecting another folder and cancelling.