Pivot Table Data Source external excel file

[IH]

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH

 

[Ron Coderre]

Try something like this:

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
To select your data...click the [browse] button
....select your data file...and enter either the range or range name
containing the data

Click the [Layout] button

Create the pivot table structure
Click [OK]
Select where you want the Pivot Table

Now that you have created the Pivot Table....Excel has also embedded a
hidden copy of the source data in the workbook, so users can still play with
PT fields, etc.

Now...this is a bit sneaky...but...
rename the source data file
or
move it to another folder.

If the users try to refresh the pivot table
they get this error:
"Cannot ope pivot table source file (yourfilename.xls)"

When YOU want to update the rename the source or move it back where it
belongs and the refresh will work.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Debra Dalgleish]

Q2: You can use programming to lock some of the pivot table features,
as described here:

http://www.contextures.com/xlPivot12.html

 

[IH]

Ron,

I'll try that again as I tried something similar. There is one issue. The
datasource isn't under my control ie I don't have write access to that excel
file. Let me see if I can just create a PT table from an external excel file
first.

Thanks for your help. I'll let you know soon.

Try something like this:

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
To select your data...click the [browse] button
...select your data file...and enter either the range or range name
containing the data

Click the [Layout] button

Create the pivot table structure
Click [OK]
Select where you want the Pivot Table

Now that you have created the Pivot Table....Excel has also embedded a
hidden copy of the source data in the workbook, so users can still play with
PT fields, etc.

Now...this is a bit sneaky...but...
rename the source data file
or
move it to another folder.

If the users try to refresh the pivot table
they get this error:
"Cannot ope pivot table source file (yourfilename.xls)"

When YOU want to update the rename the source or move it back where it
belongs and the refresh will work.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH

 

[Ron Coderre]

This might work....

Build the Pivot Table on your PC using data sourced to a mapped drive on
your computer, then copy the file to the network.

On YOUR PC the pivot table source file might be
Y:\\DeptFolder\Analysis\datafile.xls
But....unless another user has their Y-drive mapped to that exact path,
their PC won't be able to find the source file when they try to refresh the
Pivot Table. Instead, the "Cannot open pivot table .... " message will
display .

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Ron,

I'll try that again as I tried something similar. There is one issue. The
datasource isn't under my control ie I don't have write access to that excel
file. Let me see if I can just create a PT table from an external excel file
first.

Thanks for your help. I'll let you know soon.

Try something like this:

From the Excel main menu:
<Data><Pivot Table>
Use: Excel
To select your data...click the [browse] button
...select your data file...and enter either the range or range name
containing the data

Click the [Layout] button

Create the pivot table structure
Click [OK]
Select where you want the Pivot Table

Now that you have created the Pivot Table....Excel has also embedded a
hidden copy of the source data in the workbook, so users can still play with
PT fields, etc.

Now...this is a bit sneaky...but...
rename the source data file
or
move it to another folder.

If the users try to refresh the pivot table
they get this error:
"Cannot ope pivot table source file (yourfilename.xls)"

When YOU want to update the rename the source or move it back where it
belongs and the refresh will work.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Q1: How do I use data from one excel file to populate the pivot table in
another file. I don't want users to be able to see the original file, i.e. I
want all pivot tables in an accessible excel file but the data in a different
excel file.

Q2: Is there a way to lock only certain pivot fields while allowing others
to be changed by my users?

Thank you,
IH