Relative filepaths?

[Peter]

I am creating a pivot table in workbook1 from data held in workbook2. This
works just fine. Both workbooks are in the same directory.
If I now move that directory either on the same computer or, as I need to be
able to do, on 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

 

[Héctor Miguel]

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 __

 

[Peter]

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]

hi, Peter !

... 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.

make active the sheet where your pivot table is...
once in vba-editor, use the short-cut {ctrl}+G to show the immediate window code pane
once in the immediate window, copy/paste (or type) and press enter to execute:

? activesheet.pivottables(1).pivotcache.connection

you will be able to modify/adapt/... the DefaultDir= string part and re-enter above line (without "?")

if any doubts (or further information)... would you please comment ?
hth,
hector.

__ previous posts __

 

[Peter]

Hector,

I must be being very silly but that is exacly what I did and I have tried it
again but still with the same error message. I am definitely in the Immediate
window

? 4+5

returns 9 and I was on the sheet with the pivot table when entered vbe.
This is so frustrating as what you suggest seems to be the way to go.

Any other thoughts?

Thanks again, Peter

 

[Héctor Miguel]

hi, Peter !

I must be being very silly but that is exacly what I did and I have tried it again but still with the same error message.
I am definitely in the Immediate window
? 4+5
returns 9 and I was on the sheet with the pivot table when entered vbe.
This is so frustrating as what you suggest seems to be the way to go.
Any other thoughts? ...

revise in vba-editor (menu) tools / references...

(perhaps) missed or crossed references to object libraries in the vba project ?

hth,
hector.

 

[Peter]

Hector

I have found that if I place the files on the second machine in a folder of
*exactly* the same name as they were created in then the links between the
files are maintained. This is enough for my purposes.

For interest I have just spent some 90 mins trying to understand why I
cannot execute your code in the immediate window but with no luck.

? Activesheet.Name

works fine although Intellisense does not kick in whereas it does for

? ActiveWorkbook.Name

I also found that

? activesheet.pivottables(1)

works ok

So, I am confused and frustrated but, as I said at the top, I do have a way
forward. I think I'll leave it at that and not take up any more of your time.

Many thanks for you help. Peter