Changing location of pivot table data source… (2000 SR-3)

Topic

New Reply

Hello everyone…

Does anyone know how I would change the location of a database that is the source of data for a pivot table?
If I refresh the data, it says that the data isn’t found (at the old location) which is correct… but where do I go to change it to look for the same table, same database name, but at a different path?

TIA

Reply | Quote

Replies

Right click on the pivot table
WIzard

Select the new range
etc

Steve

Reply | Quote

I tried that… It keeps giving me a Login window… prompting me for Authorization… and password… The default user is Admin… I ignored that and selected Database and the path, but it never saves it…
Any idea what’s wrong?

Reply | Quote

Try changing it in the immediate window:
Alt-F11 to get to VB, Ctrl-G to open immediate window then enter:
activesheet.pivottables(1).sourcedata = “‘Sheet 1’!A1:D100″

or if on another workbook:
activesheet.pivottables(1).sourcedata=”‘[wkb Name.xls]Sheet 1’!A1:D100”

Change Pivot index, workbook, sheetname and range as appropriate. You must have the sheet with the desired pivot table active

You can use
?activesheet.pivottables(1).sourcedata

to determine what the source data is currently.

Steve

Reply | Quote

Thanks Steve… I really appreciate the help, but all I get is “DataType Mismatch” error… even when I just put… ?activesheet.pivottables(1).sourcedata …in the Immediate window… I’ve checked out the object browser and tried to use the pivottable object, pivottables collection, pivotcache… etc… I’m not having any luck…
The only thing I can get to return anything is this statement… It actually finds that there is a pivottable…

?Worksheets(“BOC SUMM”).pivottables.count
1

Lot’s of good that does me huh?…

Reply | Quote

Does:
?Worksheets(“BOC SUMM”).pivottables(1).sourcedata

do anything?

Steve

Reply | Quote

Just gives me… Run-time error 13 – Type Mismatch…

Well I have it fixed now… Don’t ask me exactly how…

Here’s what I did… Copied the worksheet… Deleted the pivot table on the new sheet… Used the wizard to create a new pivot table with the new source… Started playing with formating and totals and things… Refreshed the data… etc…. (all on the new sheet)

But check this out…. On a whim I decided to try refresh the data on the old worksheet… and instead of the error about not finding the external database, the data updated from the location specified on the new sheet… Go figure?!?! …I’ve deleted my copied sheet and left the old one (since it’s in the right format and sort) … and it’s fine… ?!?!?!
It drives me batty when I fix things and I don’t know how I did it…

Anyway… Thanks for all of your help Steve…

Reply | Quote

Just gives me… Run-time error 13 – Type Mismatch…

Well I have it fixed now… Don’t ask me exactly how…

Here’s what I did… Copied the worksheet… Deleted the pivot table on the new sheet… Used the wizard to create a new pivot table with the new source… Started playing with formating and totals and things… Refreshed the data… etc…. (all on the new sheet)

But check this out…. On a whim I decided to try refresh the data on the old worksheet… and instead of the error about not finding the external database, the data updated from the location specified on the new sheet… Go figure?!?! …I’ve deleted my copied sheet and left the old one (since it’s in the right format and sort) … and it’s fine… ?!?!?!
It drives me batty when I fix things and I don’t know how I did it…

Anyway… Thanks for all of your help Steve…

Reply | Quote

Forget my last post… I went back into the xls file…and guess what?… I’m getting the error about the old path again… WTH is going on?????
There’s no way this should be this difficult…

Reply | Quote

I am afraid I don’t understand what is going on. I played with pivot tables linked to other files with no problem. Is there any code running that changes the pivot?

Step thru this code and stop when “End Sub” is highlighted

Sub play()
    Dim pt As PivotTable
    Set pt = Worksheets("BOC SUMM").PivotTables(1)
End Sub

Now in “locals window” (view -locals window) open up the “+” by PT object and look at properties. You should be able to see some property that lists the “source data” what does it say? The ones I create are sourcedata and they should be string, why you would get a type mismatch when you try to print this property, I don’t understand.

Steve

Reply | Quote

I am afraid I don’t understand what is going on. I played with pivot tables linked to other files with no problem. Is there any code running that changes the pivot?

Step thru this code and stop when “End Sub” is highlighted

Sub play()
    Dim pt As PivotTable
    Set pt = Worksheets("BOC SUMM").PivotTables(1)
End Sub

Now in “locals window” (view -locals window) open up the “+” by PT object and look at properties. You should be able to see some property that lists the “source data” what does it say? The ones I create are sourcedata and they should be string, why you would get a type mismatch when you try to print this property, I don’t understand.

Steve

Reply | Quote

Forget my last post… I went back into the xls file…and guess what?… I’m getting the error about the old path again… WTH is going on?????
There’s no way this should be this difficult…

Reply | Quote

Does:
?Worksheets(“BOC SUMM”).pivottables(1).sourcedata

do anything?

Steve

Reply | Quote

Thanks Steve… I really appreciate the help, but all I get is “DataType Mismatch” error… even when I just put… ?activesheet.pivottables(1).sourcedata …in the Immediate window… I’ve checked out the object browser and tried to use the pivottable object, pivottables collection, pivotcache… etc… I’m not having any luck…
The only thing I can get to return anything is this statement… It actually finds that there is a pivottable…

?Worksheets(“BOC SUMM”).pivottables.count
1

Lot’s of good that does me huh?…

Reply | Quote

Try changing it in the immediate window:
Alt-F11 to get to VB, Ctrl-G to open immediate window then enter:
activesheet.pivottables(1).sourcedata = “‘Sheet 1’!A1:D100″

or if on another workbook:
activesheet.pivottables(1).sourcedata=”‘[wkb Name.xls]Sheet 1’!A1:D100”

Change Pivot index, workbook, sheetname and range as appropriate. You must have the sheet with the desired pivot table active

You can use
?activesheet.pivottables(1).sourcedata

to determine what the source data is currently.

Steve

Reply | Quote

I tried that… It keeps giving me a Login window… prompting me for Authorization… and password… The default user is Admin… I ignored that and selected Database and the path, but it never saves it…
Any idea what’s wrong?

Reply | Quote

I’ve even gone into MS Query and edited the SQL to say the proper path… but it doesn’t change on the workbook itself… I saved the query but that doesn’t help…

Reply | Quote

One thing I noticed while playing (that might be related) is that creating reference to a different workbook, does not create a link. My experiments seem to indicate that it might only update when the other workbook is open. If you don’t open the other workbook, it will not update.

So you can’t really point it to a different file and expect different results: open the source file, then refresh.

If you want it to be “live”, then LINK in your pivot workbook to the workbook source data (this will update) and then have the pivot point to the data in the workbook that has the pivot. The data will then be “live” and refreshable without having to open the other workbook.

Steve

Reply | Quote

The datasource is a table in an Access 97 database… The only thing that has changed is the path…
I can change the path in the SQL statement in MS Query… but it doesn’t save it… I’ll try with the database open and see what happens…
I’ve started trying to recreate the table from scratch… but what a pain this is…

Reply | Quote

The datasource is a table in an Access 97 database… The only thing that has changed is the path…
I can change the path in the SQL statement in MS Query… but it doesn’t save it… I’ll try with the database open and see what happens…
I’ve started trying to recreate the table from scratch… but what a pain this is…

Reply | Quote

One thing I noticed while playing (that might be related) is that creating reference to a different workbook, does not create a link. My experiments seem to indicate that it might only update when the other workbook is open. If you don’t open the other workbook, it will not update.

So you can’t really point it to a different file and expect different results: open the source file, then refresh.

If you want it to be “live”, then LINK in your pivot workbook to the workbook source data (this will update) and then have the pivot point to the data in the workbook that has the pivot. The data will then be “live” and refreshable without having to open the other workbook.

Steve

Reply | Quote

I’ve even gone into MS Query and edited the SQL to say the proper path… but it doesn’t change on the workbook itself… I saved the query but that doesn’t help…

Reply | Quote

Right click on the pivot table
WIzard

Select the new range
etc

Steve

Reply | Quote

Try if you can do it with my flexfind utility: First time just have it search for a back slash (check the objects checkbox).
Next time search for the full pat and replace with the new path.

Download the utility at my website below.

Reply | Quote

Thanks Jan… I already have your FlexFind utility… (Thank you very much BTW! )
But it’s not finding anything when I search for a back slash… … even with the objects checkbox checked)

Reply | Quote

Could you send me a strippend down version of your file (maybe by attaching it here)?

Reply | Quote

Could you send me a strippend down version of your file (maybe by attaching it here)?

Reply | Quote

Thanks Jan… I already have your FlexFind utility… (Thank you very much BTW! )
But it’s not finding anything when I search for a back slash… … even with the objects checkbox checked)

Reply | Quote

As I kind of implied in my other post, I don’t think the path is even stored with the pivot only the workbook name.

Steve

Reply | Quote

Sorry Steve… Didn’t get that far… I was playing with code… lol

Reply | Quote

Sorry Steve… Didn’t get that far… I was playing with code… lol

Reply | Quote

As I kind of implied in my other post, I don’t think the path is even stored with the pivot only the workbook name.

Steve

Reply | Quote

Try if you can do it with my flexfind utility: First time just have it search for a back slash (check the objects checkbox).
Next time search for the full pat and replace with the new path.

Download the utility at my website below.

Reply | Quote