Pivot Table 1

[nateshk]

Hi,

I have some pivot tables in my excel sheet which is based on external Excel sheets. When the external sheets are moved iam not able to refresh my pivot tables.

Is it possible to give the data source of the pivot tables at run time.

Help will be greatly appreciated.

Thanx and Regards...

Natesh

 

[xlbo]

You can access the source data of the pivot table in code using

mySource = pivottables("tablename&quot.sourcedata

or manually by right clicking on the pt, choose wizard, then "Back" - this should take you back to the source data selection part of the wizard, where you can re-specify the source data Rgds
~Geoff~

 

[nateshk]

Hi Geoff,

thanx but what i wanted was a macro to be written that will be able to change the data source at runtime, without requiring user intervention.

Hope u r able to help me out.

Thanx and regards.

Natesh

 

[xlbo]

How are you intending on doing this....????
If the path has changed, you will need to alter the path of the source data - surely that would need user input
here's 1 possibility of how to structure it

Sub ptRef()
For each ws in activeworkbook.worksheets
for each pt in ws.pivottables
curSource = pt.sourcedata
newSource = inputbox("Current source data is" & chr(10) & curSource & chr(10) & "Please enter new source data path&quot
pt.sourcedata = newSource
pt.refresh
next
next
end sub

If all the pivot tables are built on the same source, you could have the input box at the start and then loop thru changing the source for each one - either way, excel ain't gonna know what the new file location is so you're gonna have to tell it somewhere Rgds
~Geoff~

 

[nateshk]

Thanx , i will try it out and let u know

Regards

Natesh

 

[nateshk]

Geoff,

It worked.... :0)

Thanx...

There is 1 more issue which iam facing ....

1. there is a function called "Spread_PVBP" which is called everytime the shhet data is refereshed ie; when i right click on the pivot table and refresh the table.

But not able to figure out how the function is being invoked.

Hope u r able to help me out.

Thanx and Regards...

Natesh

 

[xlbo]

Without any more info, I'd guess it may be stored in the worksheet mo0dule - probably under the change event. Refreshing a pivot table changes the sheet and therefore would invoke this function.
If this is not so then please provide more info Rgds
~Geoff~

 

[nateshk]

"Spread_PVBP" is present in the standard module. But i have no clue from where it is getting called. I tried the "Call Stack Window" but of no use.

I also tried locating the change event. All the workbook sheets are empty. is there any macro which calls the function whenever the sheet is changed. In that case how is the macro called whenever the sheet is modified.

i hope i have provided enough input.

u have been helpfull.

Thanx

 

[xlbo]

Try stepping thru the pivot table refesh code (use F8 to do this). This should tell you when and from where the function is being called Rgds
~Geoff~

 

[nateshk]

To add to the probing that i undertook, i find the function "Spread_PVBP" being used in my excel sheets in cell formulas.

But how the function is called automatically ?

It also get called whenever i open the sheet

I tried stepping through referesh code. After i referesh my pivot table,this function is called.

Any clues....

Thanx...