hi, Peter !

if you ask through vba-editor / immediate window pane what/where your connection is directed to (i.e.)
copy/paste (or type) the following and press enter to execute:

? activesheet.pivottables(1).pivotcache.connection

you will be able to modify the DefaultDir= string to any path (like: thisworkbook.path)
and once modified, execute the above instruction without the question mark

or... you could do the above by code (i.e.) in your workbook_open event

or... make some sort of tricky procedure (i.e. by ms-query) using parameters for the query and linking cells as parameters

hth,
hector.

__ OP __
... creating a pivot table in workbook1 from data held in workbook2
... works just fine. Both workbooks are in the same directory.
If I now move that directory either on the same computer or
... to a second computer the source data cannot be found when I refresh the pivot table.
Obviously the path has changed though I do always keep the 2 files together in the one directory.
I think my problem is that the data source is defined via an absolute file path
but what I need is to be able to do is direct the pivot table to the appropriate file that will always be in the *same* directory.
Can anyone help me ensure that the two workbooks stay in touch with each other no matter where I move them
so long as they both stay in the same directory?
As always any advice will be greatly appreciated.
I am using Excel 2003 on Vista
Thank you, Peter