Hector

Thank you for your reply. It looks like what I need. Unfortunately I am
stumbling at the first step. The code...

? activesheet.pivottables(1).pivotcache.connection

when pasted into the vbe editor is giving error 1004 Application defined or
object defined error.

I have checked that the pivot table is called PivotTable1 and that the
cursor was in the table when I opened the vbe if that matters. It is on
Sheet1.

I know this is me not properly understanding the vbe but can you give me
just a little more detail on this first bit?

(Beleive it or not I have written a few procedures in the past so I am
embaressed that I cannot do this).

With many thanks, Peter

"Héctor Miguel" wrote:

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