Pivot with source on different drive

[DKcrm]

Hey all..

I really hope someone can help me...

I'm trying to create a pivot table in a blank workbook.

The table should get it's source data from an excel-file on a different (network) drive.

I've tried something like this:

'p:\cars & bikes\[overview 2006.xls]vehicles'!$a$1:$m$2500

With 'p:\cars & bikes\' being the path to the file, [overview 2006.xls] being the file with the source data, 'vehicles' being the sheet-name with the data and a1:M2500 being the range....

Somehow this matrix doesn't work.

The pivot should be in a file with only the PT, taking data from the source upon 'update'. The PT should be on the users c-drive..

Anyone with proper knowledge?

Thanks in advance

 

[stanleydgromjr]

I have not tried this, but it should work.

In the blank workbook, click on:

Data, PivotTable and PivotChart Report..., External data source

Browse to the external data source.


Have a great day,
Stan

 

[DKcrm]

Thank you..

I tried that before, but excel prompts me for MS Query.. a feature not installed on my work station. Need IT dept. / admin to install it on the computer.. And make sure that it's installed on all the stations that needs this pivot table.

Was hoping there would be another way.

Even tried to record a macro that
-opened the source file
-copy the data from source to new wkbk sheet1
-close source file
-create pivot table on sheet2
-hides sheet1

This works the first time, but when trying to update, I get a message that all the fields already are in the table, and finally a debug-prompt ...

 

[PaddyD]

wrong way round...

open file with data & file you want the pivot in.

from the file with the data, set up pivot as desired. at the 'where do you want the pivot' stage, select existing worksheet & browse to the new file...

 

[DKcrm]

Thanks !!..

Seems like this is what I'm looking for.

The tests I tried on my own station worked like a charm. Just need to try copy the pivot-file to other stations to test.

Again, thanks