Change Pivot Table Source File

    Hello all,


    I have a workbook with a number of pivot tables as well as the source data for each pivot table. Some of the source data sheets needed to be deleted so now if I try to refresh the corresponding pivot table I get "Connot open pivot table source file '[Std Mthly Rpt.xls]Report_2' ". I recreated the source file again and would like to know if I can link the pivot table to this new source file or do I need to create a new pivot table??


    Thank you all for your time.
    -DWildman

    Re: Change Pivot Table Source File


    Thanks for your thoughts so far guys.
    The problem is not with selecting a range for the pivot table.


    What I want is this:
    I create a pivot table from a worksheet and put the pivot table in a new worksheet. I then delete the source data sheet. When I try to "Refresh Data" in the pivot table, it cannot find the source data because I deleted it. Now I create a new source data sheet with the same info and same name as the original. I want to link the pivot table to the new source data.


    The pivot table is formatted precisely how I want. So if I lose the source data I dont want to have to create the pivot table all over again. Just give it new source data.


    Hope this is clear.


    Thanks
    -DWildman

    Re: Change Pivot Table Source File


    OK. After some experiments with file names and data, I can tell you what works for me. I can create a Pivot Table with data in another Excel file. I can rename the data file and try to refresh the Pivot Table and of course, Excel doesn't find the data, much as you describe. I can then rename the file back to the original file name, and refresh and Excel has no problem finding the data again.


    That tells me that if you're having problems, it must be because you don't have all the same information in the data files. Either the sheet name or the file name or the data range is different.


    Hope that helps,
    Jim

    Re: Change Pivot Table Source File


    Quote

    I create a pivot table from a worksheet and put the pivot table in a new worksheet. I then delete the source data sheet. When I try to "Refresh Data" in the pivot table, it cannot find the source data because I deleted it.

    I don't get it! Why not change the PivotTable Source THEN delete the old data?

    Re: Change Pivot Table Source File


    Thanks again for the responses.
    Jim - I will look further to ensure that my new source data is identical to the original.


    Dave - The reason I ask is this:


    I have a number of pivot tables that are used for monthly reporting. So every month I update the source data for the new month (this is done using TransferSpreadsheet macro from MS Access). Then all I have to do is "Refresh Data" for each pivot table.


    I accidentally deleted the source data sheets. When I rerun the TransferSpreadsheet macro in MS Access, it re-creates the source data sheets with the same names as the originals. However, the pivot table is still looking for the original source data when I do "Refresh Data".


    Many Thanks
    -DWildman

    Re: Change Pivot Table Source File


    If your getting everything out of Access why not create a OLAP Cube and then your data source will always be the same? once you've set up your parameters and groups all you'll have to do is refresh the data without deleting every spread sheet?


    try playing around with Cross tab queries... there're very usefull and in the long run you'd save yourself alot of time by creating one cube instead of multiple spreadsheets?


    HTH


    z

    Re: Change Pivot Table Source File


    Hi,
    This is maybe the answer. It coming lately, but this bug also appears in Excel 2010 and I experienced it.
    Note : I translate french excel terms and it may not be exactly the same as in your english excel version.
    1) Open both files in your excel (old source and the new file)
    2) Go to "Options" and then "Change data source"
    3) Minimize the window (here is the trick!!!) and choose the new data source (whitout minimizing, you can't choose the source or even you can't paste the new source)
    That's it!


    François

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login