Pivot Table Data Source - External to Internal

[mamafich]

Hi everyone,

I've been searching to find a way of changing my pivot table data source from Access file to a local excel sheet (of same file). Is that anyhow possible?

When I select "Change Data Source" From PivotTable Tools / Options, it only allows me to select the second option, "Use an external data source".

I've tried to do it on VBA as well, but I guess I have some points missing therefore I got error. I've used below code and it gives the error - "Run-time error 5 - invalid procedure call or argument"

    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
         PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R89C64:R1265C120", Version:=xlPivotTableVersion14)

Could you please help me if it's possible? I've set all charts and calculations based on that pivot table, and I really don't want to start from the beginning.

Thanks a lot!
Cheers,

Selim

 

[mamafich]

any suggestions?

 

[mendosi]

any suggestions?

It's been a while since you asked, but I had this same problem and just discovered a solution.

1. Create a new pivot table based on the new data source (it can just be empty).
2. Click in the new pivot.
3. In the immediate window type "? selection.pivottable.cacheindex" and press enter.
4. Note the index number of the pivot cache.
5. In Excel click in the old pivot which you want to change.
6. In the immediate window type "selection.pivottable.cacheindex = x" where x is the index number you noted in step 4.
7. Repeat step 6 for every pivot table which you want to use the new source.
8. Remove the table you created in step 1.

There may to change all tables from one cache to another in a single step, but it's probably not something you need to do often.

 

[nflami]

It's been a while since you asked, but I had this same problem and just discovered a solution.

1. Create a new pivot table based on the new data source (it can just be empty).
2. Click in the new pivot.
3. In the immediate window type "? selection.pivottable.cacheindex" and press enter.
4. Note the index number of the pivot cache.
5. In Excel click in the old pivot which you want to change.
6. In the immediate window type "selection.pivottable.cacheindex = x" where x is the index number you noted in step 4.
7. Repeat step 6 for every pivot table which you want to use the new source.
8. Remove the table you created in step 1.

There may to change all tables from one cache to another in a single step, but it's probably not something you need to do often.

I know this thread's old but it was extremely helpful and I didn't know what immediate window meant, so I thought I'd elaborate as I was caught up on step 3 for a few minutes.

The immediate window is located within the Visual Basic Editor window, you can open it with Alt+F11
Once the Visual Basic window is open Ctrl+G will launch the Immediate window, now you can type steps 3 and 6 in here.

 

[jxinyi09]

Great thanks for the notes! It is indeed extremely helpful and save me a lot of time!

I know this thread's old but it was extremely helpful and I didn't know what immediate window meant, so I thought I'd elaborate as I was caught up on step 3 for a few minutes.

The immediate window is located within the Visual Basic Editor window, you can open it with Alt+F11
Once the Visual Basic window is open Ctrl+G will launch the Immediate window, now you can type steps 3 and 6 in here.

 

[istiasztalos]

Hi,

It worked perfectly to change the source, but it also deleted all the calculated items I had configured.... just fyi in case others find this thread later

 

[mikeopolo]

Excellent notes, Saved my rep as a programmer!

 

[tomfahlberg]

Thanks for this. Saved me SOOOO much work re-creating pivot tables!!