none
Data Source path in Pivot Table changes to absolute on its own

    Question

  • Hello.

    I have a .XLSX file, that was created long time ago (I don't even know in which Office version, but definitely not 2013), and maybe even was a .XLS file at first.

    So it's a 4 MB file with 16 Sheets and 8 Pivot Tables.

    All of the Pivot Tables use other sheets from the same file as Data Source.

    Data Source for some of them look like this: 'Sheet3'!$A:$E

    Everything is fine when I save the file, and open it from saved file. 

    But as soon as I try to move the file elsewhere, or rename it, or email it - all Data Source paths change to something like this: '\Users\Sergii_Litnevskyi\Desktop\New folder\[FileName.xlsx]Sheet3'!$A:$E

    And it happens with all Pivot Tables. The problem is that it links to an old file path, where the file does not exist anymore. And it links to an external file, which is not what I want.

    If I Save As and select different path and filename - then it works fine. So it's a workaround for renaming and moving files, but not for sending them to other persons.

    I've read some threads, and people recommend disabling "Save external link values", but it does not help. It is already turned off in my office, but it keeps acting weird. 

    So what I need is: Save the file, close it, rename it, move it to other place, send it over email as attachment. And then I want to have the same Data Source path in my PivotTables as I had before I saved the file. How can I do it?

    My Office version: Microsoft Excel 2013 (15.0.4454.1503) MSO (15.0.4517.1005) 32-bit

    Monday, August 19, 2013 3:29 PM

All replies

  • Hi,

    According to your description, I suppose the issue may be caused by some reason.

    Do you link the outside data source?

    I think if the file moves the file elsewhere, or renames, or email, data source paths can’t be change.

    But, your data source paths add the absolute path.

    Do you link the outside data source?

    I recommend you zip the file and send it as Email attachment.

    If the issue exists, you may save as it in a new name and test it in another computer.

    Regards,

    George Zhao
    TechNet Community Support


    Tuesday, August 20, 2013 9:08 AM
    Moderator
  • Hi,

    This problem has been mentioned before, sadly without any real answer to it.  I am still confused why are there so few complains about this serious problem. Probably because files created in earlier versions of excel are not affected.

    Nevertheless,  I have found a way around it: 

    What is seems to be working is using File --> Save as.. when copying/moving/transferring/renaming files.

    When I try copying/moving/transferring/renaming the through the Windows Explorer without opening it via Right Click & Copy / Ctrl+C / Drag & Drop etc, then the links change to absolute, thus reading from initial file. 

    The same issue in mentioned in another relative thread under the title:

    "Excel 2013 - absolute and relative links problem" (Sorry, my account is unable to paste links)




    Tuesday, August 20, 2013 10:09 AM
  • Hi,

    According to your description, I suppose the issue may be caused by some reason.

    Do you link the outside data source?

    I think if the file moves the file elsewhere, or renames, or email, data source paths can’t be change.

    But, your data source paths add the absolute path.

    Do you link the outside data source?

    I recommend you zip the file and send it as Email attachment.

    If the issue exists, you may save as it in a new name and test it in another computer.

    Regards,

    George Zhao
    TechNet Community Support


    I am pretty sure that I don't have any external links in the document.

    However, even if I did - why would it change Data Source path for all of the Pivot Tables, when I did not request it?

    I tried zipping it and sending to other person over email, but he got the file with changed data source paths.

    I can even record a short video to show what happens.

    Actually, I just did it. You can see the video here: http://screencast.com/t/qMBild3ck9b

    It is rather big - 23.8 MB.

    Let me explain what I showed there:

    I opened my original file. I showed that there are Pivot Tables, whose Data Sources are in the same file, on various other sheets.

    I showed this for all of the Pivot Tables in the document.

    I saved the file using Save As in a different folder and under a different name (TEST.xlsx).

    I then opened that saved file to show you that it is fine, and the Data Source path for one of the Pivot Tables is the same as it was in original file. It is the same for all of the other Pivot Tables.

    Then I closed, and simply renamed the file to TEST123.xlsx.

    Opened it, and first thing wrong - Security warning.

    Then I got ‘Cannot open PivotTable source file ….’ messages. And, as I showed, now all Data Source paths have been changed to full paths of the file, that was created by Save As (TEST.xlsx) from original file.


    • Proposed as answer by papawlik Tuesday, September 01, 2015 5:00 PM
    • Unproposed as answer by papawlik Tuesday, September 01, 2015 5:00 PM
    Tuesday, August 20, 2013 11:25 AM
  • So, I guess there is no fix for this issue???
    Thursday, August 22, 2013 9:47 AM
  • In addition, I must add the problem of shared network files.

    For example, in an office network, if someone uses a map network drive letter, that path then becomes absolute for everyone else.

    Say I name my COMMON folder with the letter “K”.

    So the files I create will have a path:  K:\Work\Documents\File…

    That means that the file is useless for anybody with access on the same folder unless he/she uses the same letter to create a map network drive.

    If another user uses a different letter to map the same COMMON folder, say “J”.

    Then the files will have a path:  J:\Work\Documents\File…

    And the problem is there! Imagine this with multiple co-workers..

    The same thing happens in dropbox since the path usually changes from one computer to another.

    Say,

    OFFICE COMPUTER: C:\Users\officepc\Dropbox

    HOME COMPUTER:   C:\Users\homepc\Dropbox

    And the problem is there (again)!!

    -Patience Daniel-son.. the solution will come... hopefully before we are forced to go back to the previous version..

    Thursday, August 22, 2013 1:36 PM
  • Anyone anything?

    This seems to be a bug. As far as I remember this is not reproducible in pre-2013 Office versions.

    Tuesday, August 27, 2013 10:27 AM
  • Looks like a bug to me.  I can reproduce.  Any xlsx files when linked keep their links as absolute paths when copied or moved.

    The behavior does NOT happen in Office 2010, it also works in 2013 if the file is a .xls (compatibility mode).  I am at a loss for a fix.  I think the fix has to come from Microsoft.  Only work around I have found is to not use the .xlsx format when working with linked files, in 2013.

    Wednesday, September 25, 2013 7:56 PM
  • I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

    I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

    Hope these additional clues can help find a solution.


    • Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
    • Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
    • Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM
    Tuesday, March 18, 2014 7:48 PM
  • I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

    Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

    this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

    • Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM
    Wednesday, March 19, 2014 11:59 AM
  • Yes, "save as" is the only thing that works if you didn't select "add this data to the data model" for new pivots in 2013. Shouldn't it be "select this if you WANT absolute values" instead of having to opt-out? It is incomprehensible to me that a released version of excel "breaks" a file when you rename it.

    • Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
    • Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
    • Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
    • Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM
    Monday, April 21, 2014 7:36 PM
  • I agree. 

    Best Regards, David K Allen http://codecontracts.info Minneapolis, Minnesota, USA

    Wednesday, July 02, 2014 6:40 PM
  • I didn't seen any real resolution that was simple on this thread so I'm posting. This method seemed to work for me.  

    In Windows Explorer, right-click on the file and select copy.  Then Paste into the same folder.  A new file will be created as 'filename COPY'.  You can then just re-edit the filename as desired there (ie. with a new date).  You can then update whatever data tables you have and the pivot tables will be referencing those tables internal to that document and not the old file.  

    Thank goodness that worked!

    Hope it helps anyone else out there!

    Phil

    Friday, October 03, 2014 10:57 PM
  • I am having the same issue.  Microsoft still has not fixed the issue. Ever link in my large excel file with multiple pivot tables are broken after i updated and changed the file name. I cannot find a fix to solve this error.  Microsoft 2013 was installed on my computer recently.
    Thursday, November 06, 2014 3:09 PM
  • I know it's a bit old topic, but here is  solution:

    Open the thisWorkbook module in VBA, then:

    Private Sub Workbook_Open()
    Worksheets(PivotSheetName).PivotTables(PivotTableName).ChangePivotCache _    
    ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Worksheets(SourceSheet).Range(PivotSource))
    End Sub

    Whenever you open your file, this code will rewrite the pivot source to the actual.

    It works for me.

    Tamas

    Thursday, January 08, 2015 4:26 PM
  • "Save As" used to work.

    But i just "Saved As" a sheet I've been working on for a while and now Excel 2013 has gone back to saving the entire path. 

    How do we get Microsoft to fix this once and for all?

    Friday, January 09, 2015 11:04 AM
  • "Save As" used to work but now doesn't work. So I tried your Macro on a test Sheet with a Pivot Table. I get "Subscript out of range."

    Does your solution require manually entering the PivotSheetName and PivotTableName  in the Macro? Or is there some other missing piece in your code? 

    Friday, January 09, 2015 11:08 AM
  • The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

    But one thing I did verify, as you mentioned using "xls" solves the problem!

    This definitely is a bug.

    • Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
    • Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
    Friday, January 09, 2015 11:24 AM
  • Hello everyone, I think I might have found a solution to this particular issue.

    Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

    When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

    The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

    After going mad for a couple of hours, I find out some Excel options that solve this issue.

    Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

    First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

    Then I unchek "Update link to others documents" and "Save external link values".

    Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

    It seems to do the trick.

    Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

    However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

    • Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text
    Thursday, January 22, 2015 8:11 AM
  • Thank you X Romea.  I went in and changed those settings, but every time I close the workbook, it re-checks the "update links to other documents" and I have the same issue.  It changes the reference to the data tabs to add the name of the old file, as described in this thread.

    This behavior is very frustrating!  Come on Microsoft!

    Monday, February 16, 2015 9:15 PM
  • At least now I understand the root of my problem. I have a problematic spreadsheet that is faulting and closing. When Excel restarts and recovers the spreadsheet all of the links to all of the pivot tables have been changed to hard links.

    This is because Excel recovers the file with suffix like [recovered] or [version 2] and the file is in a data cache in the AppData folder. Then when I do a "save as" to save the recovered file back to the original file name and location all pivots get hard links. I have 30 some odd pivots that I have to go and update the links two or three times a day when the sheet crashes. Very frustrating.

    I cannot add to data model because I need downward compatibility.

    As of this date still no fix from Microsoft.

    Friday, February 20, 2015 4:01 PM
  • I have exactly the same problem and i have spent already 2 working days to solve this.

    the story is more/less same as above.. well described really. 

    I have been using the same (100% same) methodology to create reports. I have a standard way. never changed anything (relly anything) since June 2013. I always use .xlsb (binary) since the data is a bit large. (around 6-7 mb as .xlsb and if i use .xlsx it becomes 20mbs. )

    I used to update the contents of the workbook, maybe hundreds of time in a day, i have a pivot at a 2nd workbook, i refresh it very often. that's it. then in the evening, i save the file. next morning, without opening, i make a copy via windows explorer. then i open the new file and continue the same. Last week i ve begun having this absolute link issue. 

    so the only difference i made is, on another file, at home in the evening, i have been trying to understand "new" excel's "data model" features. i dont know what i have activated, during these tests. but it happened now and i cant roll back. 

    i have 8 data WorkBooks and 8 PivotTables connected. All have been working fine until next week for verrryyyy long time. 

    -Save As... doesnt work.

    -i tried all above solutions, including the vba code, it can be a temporary solution maybe, but still not efficient.

    Now looking forward to microsoft's solution/recommendation asap.

    thanks for all the people above, it s a great feeling to see such a contribution. 

    Regards,

    Friday, March 06, 2015 10:26 AM
  • Have you tried setting your hyperlink base address to C:?

    FILE->Info (Show all Properties)

    This should create hyperlinks with the UNC path.  Not sure how it works with linked data.

    Monday, March 09, 2015 9:35 PM
  • I had used that solution the last couple of months, but for some reason it stopped working as a solution.  the copy/paste/rename this time created the absolute links in the pivot tables. 
    Thursday, April 02, 2015 10:40 PM
  • it is not a solution. 
    the interesting thing is that, it doesnt happen everytime.

    I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

    and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

    Thus we can easily sayt that this is a BUG.

    Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 



    • Edited by HakanSD Saturday, April 11, 2015 7:30 AM
    Saturday, April 11, 2015 7:25 AM
  • this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!


    Tuesday, April 14, 2015 6:36 AM
  • this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!


    have just found this thread having discovered the same problem - agree entirely with the above!!!

    • Proposed as answer by nosp Sunday, May 31, 2015 8:08 PM
    • Unproposed as answer by nosp Sunday, May 31, 2015 8:08 PM
    Wednesday, April 22, 2015 2:19 PM
  • Workaround: Save as .xls

    • Proposed as answer by nosp Sunday, May 31, 2015 8:29 PM
    • Edited by nosp Sunday, May 31, 2015 8:34 PM
    Sunday, May 31, 2015 8:29 PM
  • I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

    here is what I found and it works.

    Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

     Select the corresponding "Table" or "Range" for your data source.

    Continue with ALL your Charts, before do anything else.

    After you finished by doing this will all your charts,

    Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

    Unselect "Save source data with File"

    Select "Refresh data when opening the file"

    Then will appear the following 2 alert message

    1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

    2. "Data will not be saved with other PivoTable report using the same source data as this report"

    Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

    Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

    It works for me!

    Good Luck


    • Edited by antonio_ags Tuesday, June 09, 2015 2:49 AM
    • Proposed as answer by Sorin_P Monday, December 14, 2015 3:06 PM
    Tuesday, June 09, 2015 2:49 AM
  • I also have the same problem:  Saving or Resaving files with Pivot Table connections within the same workbook convert to absolute and break the independent spreadsheet usability and portability and backups.

    I tried disabling just save data with pivot table, and that didnt work. 

    The second option you note to refresh every time it is opened, may not be viable for me as my sheet executes a rather large query...

    Microsoft should allow pivot tables to use relative references as the permanent state of the the pivot table source reference for pivot tables. 

    Friday, June 12, 2015 8:48 PM
  • Formatting the data source (of the pivot) as a table seems to resolve this
    • Proposed as answer by ggoodfellow Monday, July 06, 2015 11:16 PM
    Friday, June 19, 2015 2:46 PM
  • Hi All,

    I appreciate the many posts and advice on this.  I too have been having this issue when I updated to 2013.  I tried all the remedies except VBA, and they did not work for me.

    I had posted on another forum:

    "Excel 2013 keeps updating the pivot table data ranges to a prior named file.

    I built out a spreadsheet with multiple tabs of pivot tables all referencing the master data tab in the same spreadsheet.

    When I "save as" the document as a new name, the pivot tables refer back to the old document.  Not sure why this is happening and it is very frustrating and time consuming to update the data ranges each time I save the document with an update. I am just changing the file name with V2, V3, V4 etc or changing the file name to have a different date extension (20150522 to 20150529)"

    https://social.technet.microsoft.com/Forums/en-US/0cedfc28-aeba-469c-9a91-0389fc3c6937/excel-2013-not-updating-pivot-table-data-ranges-correctly-please-advise-how-to-fix?forum=excel

    "Excel 2013 not updating Pivot Table data ranges correctly.  Please advise how to fix?

    I was directed to this Forum.  The one thing I did find for NEW Excel 2013 files is the following:

    However mine is was originally built in 2010, so it seems the only solution to completely rebuild the file in 2013.

    It is surprising that this bug in logic has not been addressed yet and fixed.

    Tuesday, June 23, 2015 5:18 AM
  • Thank you so much for proposing this option. I have also been frustrated by this problem and have run down the solutions posted by everyone on this list. Unfortunately, none of them really worked reliable. However, your solution did seem to do the trick for me.  I highlighted the data in my worksheet that is the source for my pivot table, and I chose Insert > Table to turn my source data into a data table.  Then I saved my document with a new name.

    Now each time I open the document, the error is gone, and the "old" filename is no longer associated with the data source of my pivot tables.

    Thanks so much!

     
    Monday, July 06, 2015 11:20 PM
  • Hello, it's been almost 2 years and I don't see any answer... I have the same problem, since I change to Excel 2013, in other versions had no problem.

    Does some body knows if SP1 fixes the problem?

    Best regards,

    Eugenio

    Tuesday, July 07, 2015 4:53 PM
  • I just try converting the data source into a table, as you indicate, however, Excel keeps adding the filename; I did not have to rename the file y just closed and opened again and the data source changed to what I said.

    Thanks anyway!!

    :)

    Tuesday, July 07, 2015 5:52 PM
  • I was having this issue too.

    From what I've found, the best way is to create a new Excel file and copy the data in from the old file. You then create new PivotTables which will be linked to the file they're in when you create new versions, rather than the file they were originally linked to.

    Take care though - when I create a new Excel file through 'right click, new, Microsoft Excel Worksheet' this will NOT work - you need to create the new file by opening Excel!

    Best Regards

    Robert

    Tuesday, August 04, 2015 11:11 AM
  • I have the same issue and I guess the reason caused this issue is very similar to yours. 

    I have two Excel files with complicated models inside. One night I opened one of them at home using Excel 2016 for Mac, but not the other file. 

    Then when I got back to work in office, I found the issue with the one that I opened at home while the other file without any issue. 

    I guess opening the same file and sync through Onedrive may cause this issue for some reasons. 

    Currently my workaround is to move (not copy) all the tabs from the original file to a new file. This seems solve the issue. But since my file includes some slicers, I need to disconnect them, update the data source, and then reconnect them. 


    • Edited by LittleSun Monday, August 10, 2015 7:46 PM
    Monday, August 10, 2015 7:45 PM
  • Dear all,

    I figured out in Excel 2013 the settings in the section Info might solve the issue


    papawlik

    • Proposed as answer by papawlik Wednesday, September 02, 2015 8:59 AM
    Wednesday, September 02, 2015 8:59 AM
  • If you are having this problem and use Named Ranges, here is a potential workaround: Scope the names to the sheet instead of the workbook

    I.e., Formulas / Define Name / Scope = [Sheet Name].

    The root problem is, Excel is inserting the file name in the pivot table data source, inappropriately.  The data source may appear to be clean when initially working with the pivot table, but when you close the workbook and reopen it, the file name will always have been reinserted.  For example, "MyRange" (named range), after reopening will be "'New Microsoft Excel Worksheet.xlsx'!MyRange".

    Excel 13 seems to want to do this when you set the pivot table data source to a range that does not require a bang (!).

    So workbook-scoped names (which I have used with pivot tables for years) will trigger the bad Excel behavior, because "!" is not needed to reference the named range.  Unnamed ranges (e.g., "A1:C5") within the same worksheet may work similarly, but I haven't tested those.

    Sheet-scoped names defining a range on a different sheet will require the sheet name before the name (e.g., Sheet1!Range1).  When that sheet reference (!) is there in the pivot table data source, Excel seems satisfied and doesn't mess with it anymore.

    This is clearly a bug and should be fixed by Microsoft.

    p.s. Tried all of the solutions posted in this thread to date (except VB), none worked for me.  The problem doesn't manifest in all my Excel files.  The files it's happening in were all created in Excel 2013.  Others were possibly created in Excel 2010.



    Wednesday, September 02, 2015 4:31 PM
  • The workaround worked for a few file copies Excel got creative and found a way to work the file name in anyway.  It is now inserting the file name in this format:

    '[New Microsoft Excel Worksheet.xlsx]Sheet1'!MyRange

    I give up.

    Wednesday, September 02, 2015 5:43 PM
  • I know it's a bit old topic, but here is  solution:

    Open the thisWorkbook module in VBA, then:

    Private Sub Workbook_Open()
    Worksheets(PivotSheetName).PivotTables(PivotTableName).ChangePivotCache _    
    ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Worksheets(SourceSheet).Range(PivotSource))
    End Sub

    Whenever you open your file, this code will rewrite the pivot source to the actual.

    It works for me.

    Tamas

    thanks. I used this. Only I tweaked it a little. I created a bigger, generic macro. we have a lot of spreadsheets, some with many pivot tables.

    I changed the UpdateRemoteReferences to False.
    I changed the ActiveWorkbook.SaveLinkValues to False .

    Those are both suggestions other users have had, but through navigating via File>Options>Advanced....

    I unlisted my data source (convert to range). Then I turned it back into a Table, but (no longer trusting MS to name things) I renamed it.

    I looped through all the sheets using their index number,

    and for each (<-that's another loop) sheet not equaling source sheet name,

    I reset the source for the pivot table to the new data source Table.

    And just because I no longer trust MS to name things properly without totally fouling up our giant sheets of data...I looped through all the sheets again and renamed the the Pivot Tables (ie PivotTable1 now equals sheetName_Month).

    Test:

    * change source data, save, close, and reopen workbook and refresh. <-works

    * same as above, but save the file with a different name. <-works

    * use the second file, and repeat same procedure (making a third file). <- works.

    Macro was only run once. Macro can transport to any workbook. Honestly, I'm not entirely sure why the problem ever popped up. I bet it is like someone mentioned, prefixing the workbook name to further identify data- but not allowing or updating workbook name changes...And I'm not entirely sure why all this renaming works, but it does. At least for me.

    Wednesday, September 16, 2015 11:46 PM
  • It is clearly a bug, since the following set of 4-steps does not work by itself:

    1. Select Pivot Table
    2. Analyze->Options-Data
    3. Unselect "Save source data with File"
    4. Select "Refresh data when opening the file"

    NOTE: I tried other suggestions of working around of this post: nothing worked for me.

    THIS SOLVED: The following VBA macro may help someone else to make relative Pivot Tables' source data:

    (I made it as simple as possible, just cutting the filename of the source and refreshing)

    Public Sub Update_PivotTables_Source()
    Dim currWS As Worksheet
    Dim currPT As PivotTable
    
    Dim strName As String
    Dim strMsg As String
    Dim Res
    
     On Error Resume Next
     
     For Each currWS In Application.Worksheets
        For Each currPT In currWS.PivotTables
            strName = currWS.Name & "!" & currPT.Name
            strMsg = "Source of pivot table " & strName & Chr(13) & _
                     "Changig from " & currPT.SourceData & Chr(13) & _
                     "To " & CutFilename(currPT.SourceData)
            Res = MsgBox(strMsg, vbYesNoCancel, "Source PT")
            
            Select Case Res
                Case vbYes
                    currPT.SourceData = CutFilename(currPT.SourceData)
                    currPT.RefreshTable
                Case vbCancel
                    Exit Sub
            End Select
        Next currPT
     Next currWS
    End Sub
    
    Private Function CutFilename(strSource As String) As String
    Dim intPosition As Integer
    Dim intStrLen As Integer
    Dim blnFound As Boolean
    
    Dim intFileStart As Integer
    Dim intFileEnd As Integer
    
    Dim chrCurr As String
    
     strSource = Trim(strSource)
     CutFilename = strSource
     
     intPosition = 0: intStrLen = Len(strSource)
     intFileStart = 0: intFileEnd = 0
     
     blnFound = False
     Do While (Not (blnFound) And (intPosition < intStrLen))
        intPosition = intPosition + 1
        chrCurr = Mid(strSource, intPosition, 1)
        Select Case chrCurr
            Case "["
                intFileStart = intPosition
            Case "]"
                intFileEnd = intPosition
                blnFound = True
        End Select
     Loop
     
     If blnFound Then CutFilename = Mid(strSource, 1, intFileStart - 1) & Mid(strSource, intFileEnd + 1, intStrLen)
    End Function

    FINAL NOTE: I have also found that the problem disappeared after executing this macro (moving or renaming new files did not changed the relative path of the Pivot Tables). After some checks I have found that the problem can be solved by simple quotation of worksheet names:

    1. Changing the options setting by the 4-steps shown at the beginning of this answer.
    2. Changing the PivotTables' Source Data by putting the worksheet name between simple quotations (i.e. rewrite Sheet1!A1:D10 into 'Sheet1'!A1:D10)

    Please, confirm this solved the problem...

     




        
    • Edited by rellampec Tuesday, September 22, 2015 8:29 AM
    Monday, September 21, 2015 11:54 PM
  • Thanks for your workings but unfortunately when I run your macro, Excel crash and stop working.

    In my workbook, data source refer to a name range e.g. Work Sheet Name!DataRange, therefore I had to slightly tweak your code to get it working. To make things worst, I have lot of slices on the workbook as well and not sure it has an impact. I tried to move back to Excel 2010 but it does not allow to change the data source without deselecting all the slicers from current data source.

    Tuesday, September 22, 2015 11:07 AM
  •  Hi Lalith

     Thank you for your feedback. The function leaves all the same when there is no [Filename] format. I do not know why could it crash. Can you show the complete Data Source of any pivot table after moving/renaming the file? (I mean the format... in mine Excel uses [] as file name delimiters).

     Did you tried my last comments? In your example: 'Work Sheet Name'!DataRange? (worksheed between simple quotation ' ' ). Before you must change options by following these 4-steps:

    1. Select Pivot Table
    2. Analyze->Options-Data
    3. Unselect "Save source data with File"
    4. Select "Refresh data when opening the file"

     And after that, erase the filename and  put the simple quotation around the worksheet name. Please, try it for one of your Pivot Tables and check if it works by copying to a new file.

     Thank you again!

    Tuesday, September 22, 2015 10:19 PM
  • I have the same problem. File created in Excel 2013. Can someone confirm  whether this issue is persisting in Excel 2016?
    Wednesday, October 14, 2015 9:21 PM
  • Sorry - but this did not work for me.
    Tuesday, November 24, 2015 5:01 AM
  • I tried this Macro but get "Subscript out of range."
    Thursday, December 10, 2015 1:26 AM
  • I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

    here is what I found and it works.

    Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

     Select the corresponding "Table" or "Range" for your data source.

    Continue with ALL your Charts, before do anything else.

    After you finished by doing this will all your charts,

    Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

    Unselect "Save source data with File"

    Select "Refresh data when opening the file"

    Then will appear the following 2 alert message

    1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

    2. "Data will not be saved with other PivoTable report using the same source data as this report"

    Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

    Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

    It works for me!

    Good Luck


    guys. i did exactly what was described above ( i only had pivots, not charts) for each of my 150 pivots and in my PC it worked.

    than i sent the file as email attachedment to my self and the connections broke again.

    i than used papawlik reply and went to inspect workbook option and clicked on allow info to be stored in your file. 

    sent the excel to me and to a friend and finally the issue was solved.

    hope it helps everyone. 

    Sorin 

    Monday, December 14, 2015 3:19 PM
  • I know it's a bit old topic, but here is  solution:

    Open the thisWorkbook module in VBA, then:

    Private Sub Workbook_Open()
    Worksheets(PivotSheetName).PivotTables(PivotTableName).ChangePivotCache _    
    ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Worksheets(SourceSheet).Range(PivotSource))
    End Sub

    Whenever you open your file, this code will rewrite the pivot source to the actual.

    It works for me.

    Tamas

    Hi Tamas,

    I tried your code in Excel 2016 and get a

    Run-time error '9':

    Subscript out of range.

    When I debug the following statement is highlighted in yellow

    Worksheets(PivotSheetName).PivotTables(PivotTableName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Worksheets(SourceSheet).Range(PivotSource))

    I placed the code in 'ThisWorkbook' as suggested. Any ideas why it throws this error? I'm a total novice as far as this is concerned so not able to recognise the issue

    I have come to the conclusion that the only way to fix this issue is to use some VBA code. My client needs two separate file names (file 1 and file 2 which is a copy of file 1 with a different file name) and both copy & rename in Windows Explorer as well as File | Save As doesn't work as Excel remembers the absolute path and the only way to fix file 2 is to change the data source for every pivot (12 of them) in the file. not workable


    • Proposed as answer by Rafał.Ś Monday, December 28, 2015 10:12 PM
    • Unproposed as answer by Rafał.Ś Monday, December 28, 2015 10:12 PM
    Tuesday, December 15, 2015 12:43 PM
  • Hi,

    I developed a solution, which can fix this annoying problem, but it involves editing the excel archive. More details on this article I wrote for all users with this problem: http://www.excel-first.com/cannot-open-pivot-table-source-file/

    Important: try this solution on a copy of your file, if you make a mistake, your workbook may become irreversibly corrupted.

    Step 1:

    Open the file in excel, uncheck the the option from Excel Options-Trust Center-Privacy Options: “Remove personal information from file properties on save”

    Step 2:

    The key is in the excel archive: if you right click the excel file, and open it with an archiver, this is our guilty folder:

    xl\pivotCache\_rels , the problem is related to pivot cache relationships…

    Inside this folder, there should be at least 1 file, named: pivotCacheDefinition1.xml.rels

    If there are multiple caches, the rest of the files will be: pivotCacheDefinition2.xml.rels, pivotCacheDefinition3.xml.rels and so on.

    From the pivotCacheDefinition1.xml.rels file, simply delete the following part, but ONLY that, otherwise excel will not be able to open it, it will become corrupted:

    Type=”http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing” Target=”New%20Microsoft%20Excel%20Worksheet.xlsx” TargetMode=”External”/><Relationship Id="rId1"

    Instead of Target=”New%20Microsoft%20Excel%20Worksheet.xlsx” you will see your file name.

    Step 3:
    open the file with excel, SAVE the file, and CLOSE it.
    The problem is gone, you will be able to save the file with another name, the reference will be relative always.

    Regards,

    Catalin Bombea


    • Edited by Catalin Bombea Saturday, December 19, 2015 9:18 PM added link
    • Proposed as answer by Rob Kueffner Wednesday, December 07, 2016 10:23 PM
    Saturday, December 19, 2015 9:17 PM
  • Here is simple macro that solves the problem.  I used code that rellampec wrote and  made it shorter and working. 

    Sub Update_PivotTables_Source()
    Dim currWS As Worksheet
    Dim currPT As PivotTable
     On Error Resume Next
     For Each currWS In Application.Worksheets
        For Each currPT In currWS.PivotTables
            currPT.SourceData = "'" & Mid(currPT.SourceData, InStr(1, currPT.SourceData, "]") + 1)
        Next currPT
     Next currWS
    End Sub
    General rule is the same. For some reason rellampec's macro just didn't work for me. You can use it many ways. I recommend to keep it in Personal Macro Workbook. You can use it in many files then and don't need to keep them as xlsm format. The only problem I experienced using it, was that Excel freezes and stops working some times. Generally it happens when you run this code in large files (tens of MB). The solution that I found in that situation, is to save the file just after running the code. You close all Excel files then and open it again. Some times you have to reopen the file twice but it helps. I hope it will be helpfull for all of you. Good luck. 


    Monday, December 28, 2015 10:29 PM
  • That solved the problem. Thank you! After a year with an outdated filename, I can finally rename it.
    Tuesday, January 12, 2016 8:55 PM
  • Thanks a lot, that worked for me as well! I run it once, after adapting it slightly to my own source filename pattern:

    Sub Update_PivotTables_Source()
    Dim currWS As Worksheet
    Dim currPT As PivotTable
     On Error Resume Next
     For Each currWS In Application.Worksheets
        For Each currPT In currWS.PivotTables
            currPT.SourceData = Mid(currPT.SourceData, InStr(1, currPT.SourceData, "!") + 1)
        Next currPT
     Next currWS
    End Sub

    It seems that you can save the file as xlsx, without the macro that needs to run only once.
    So far, so good, thanks again.

    Thursday, January 21, 2016 1:52 PM
  • Thank you Rafał.Ś and  Klint68 !!!

    I have a spreadsheet with around 80 pivot tables and no matter what solutions I tried, the pivot table source data would always revert back to a link to the old original file name.  Wasted two days of work trying to figure this out and fix it.

    I ran Klint68 version of Rafał.Ś macro and it fixed all the pivot tables data source.

    There is still something somewhere that references the old original spreadsheet name because if I go into a pivot table data source (which does not have the original file name) and click okay, I do get an error message saying it could not update the data with the original file name, but continues to update with the correct data in my spreadsheet.

    So if I dig, I can tell there is still traces of this bug.... but in actual daily use it is fixed and now works no matter if I copy it, save as, upload / download to SharePoint, etc....

    THANK YOU!!!!!
    Friday, January 29, 2016 10:39 PM
  • hi,

    I was so happy to discover this thread - I have the same frustating issue - however, the macro does not seem to work for me - I am not sure if I am not able to make macros or if the macro really does not work - could somebody give some advice

    Big thanks in advance

    ps - I have created the macro as follows: I made a simple macro via the recording button and then via edit, I changed the code to:

    Sub Update_PivotTables_Source()

    Dim currWS As Worksheet
    Dim currPT As PivotTable
     On Error Resume Next
     For Each currWS In Application.Worksheets
        For Each currPT In currWS.PivotTables
            currPT.SourceData = Mid(currPT.SourceData, InStr(1, currPT.SourceData, "!") + 1)
        Next currPT
     Next currWS
    End Sub

    Tuesday, April 26, 2016 2:09 PM
  • Try to do following steps: Save your file as Excel Macro-Enabled Workbook. Check your macro security settings in Excel. Use this code:

    Sub Update_PivotTables_Source()
    Dim currWS As Worksheet
    Dim currPT As PivotTable
     On Error Resume Next
     For Each currWS In Application.Worksheets
        For Each currPT In currWS.PivotTables
            currPT.SourceData = "'" & Mid(currPT.SourceData, InStr(1, currPT.SourceData, "]") + 1)
        Next currPT
     Next currWS
    End Sub
    Maybe version of this code by Klint68 doesn't  match your source filename pattern or generates some error.
    Sunday, May 01, 2016 10:47 AM
  • Something similar is happening with Excel 2016 when I rename a file or perform a Save As with a different name.  Specifically the message "  Can't open PivotTable source file 'file.xlsx'  "

    The situation can occur when "Remove personal information from file properties on save" checkbox is enabled [found under File > Options > Trust Center > Trust Center Settings > Privacy Options] . The checkbox becomes enabled after you use the "File > Info > Check for Issues > Inspect Document" feature.   You may have to use that option two or more times for the checkbox to become enabled.

    Disabling the  "Remove personal information from file properties on save" option prevents the "corruption" from occurring.

    Repairing the "corruption" can be done via one of the macros in this thread, [care must be taken not to create too many pivot caches], or by using the "Change Data Source" toolbar option that becomes available after you select a pivot table.

    • Proposed as answer by Esko Morko Friday, September 16, 2016 7:45 AM
    • Unproposed as answer by Esko Morko Friday, September 16, 2016 7:45 AM
    Thursday, August 25, 2016 1:20 PM
  • Hi

    I described a brutal but really working solution in other thread on September 15th 2016:

    https://social.technet.microsoft.com/Forums/en-US/49b676f2-2a66-49de-825b-97baabf9afa9/excel-2013-absolute-and-relative-links-problem?forum=officeitpro

    Friday, September 16, 2016 7:55 AM
  • @papawlik, thanks!

    This seems to have fixed the issue with the pivot table data source references within my workbook. Now I can copy the file via file explorer and it keeps the references within the current workbook.

    • Proposed as answer by StephenBelkin Monday, January 09, 2017 10:04 PM
    • Unproposed as answer by StephenBelkin Monday, January 09, 2017 10:04 PM
    Friday, November 11, 2016 8:06 PM
  • This has been driving me mad too.

    I tried the solution of creating the macro to re-set all the source data properties and this worked fine. So I saved the template as a macro enabled workbook. All good.

    I created a copy of the template workbook and renamed it then opened it. The macro failed with a subscript error - because it couldn't find the "!" in the current source data property. Why couldn't it find it? Because Excel was NOT changing the source data properties to absolute paths anymore!

    What the...?!

    So is the solution to just save the template as a macro enabled spreadsheet - even though it doesn;t actually need any macros?

    Monday, January 09, 2017 10:09 PM
  • Thank you X Romea.  I went in and changed those settings, but every time I close the workbook, it re-checks the "update links to other documents" and I have the same issue.  It changes the reference to the data tabs to add the name of the old file, as described in this thread.

    This behavior is very frustrating!  Come on Microsoft!

    I faced the same problem. It was finally fixed when I added the following code into ThisWorkbook:

    Sub Workbook_Open()
        ThisWorkbook.UpdateRemoteReferences = False
    End Sub
    
    
    Cheers.

    Thursday, February 23, 2017 10:03 PM
  • What works for me: I replace the file name with empty quotes in the pivotCacheDefinition relationship file.

    1. Rename the ExcelFile.xlsx file into ExcelFile.xslx.zip
    2. Open the file as archive
    3. Go to \xl\pivotCache\_rels\pivotCacheDefinition1.xml.rels 
    4. Extract and edit the file in a text editor
    4a. Find the line:
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId2" Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing" Target="ExcelFile.xslx" TargetMode="External"/>

    Replace it with line: (remove the stored file name)
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId2" Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing" Target="" TargetMode="External"/>

    5. Save the edited file back into the archive
    6. Rename the file back into ExcelFile.xlsx

    It works only for the first time. When I save my Excel file from Excel, the file name gets back in again :(
    • Edited by Alex Kosau Tuesday, August 15, 2017 5:01 PM
    Tuesday, August 15, 2017 4:59 PM
  • Hi,

    I'm convinced this is due to corruption of one of the pivot tables in the file.  I have multiple workbooks each with multiple pivots that I rename each month yet only 1 workbook has this problem.  I've tried every suggestion posted here and on other boards but nothing worked.  Each time I rename the file it converts the data source for all pivot tables in that workbook to an absolute reference.

    It is not in my Excel settings because it is specific to this workbook.  I went back one of the workbooks I saved last year and tried to rename it and all of the pivot tables correctly updated to the current data source so it is definitely a bug in one of the pivot tables in a current workbook that stays with the file each time I rename or copy it. 

    By copying the data from a current workbook into my old/renamed file and updating the pivots I now have a template to use that correctly updates the data source for all pivot tables in the workbook. 

    Not a great solution but it works and you don't have to re-create all your pivot tables.

    Friday, August 18, 2017 2:14 PM
  • hi,

    I was so happy to discover this thread - I have the same frustating issue - however, the macro does not seem to work for me - I am not sure if I am not able to make macros or if the macro really does not work - could somebody give some advice

    Big thanks in advance

    ps - I have created the macro as follows: I made a simple macro via the recording button and then via edit, I changed the code to:

    Sub Update_PivotTables_Source()

    Dim currWS As Worksheet
    Dim currPT As PivotTable
     On Error Resume Next
     For Each currWS In Application.Worksheets
        For Each currPT In currWS.PivotTables
            currPT.SourceData = Mid(currPT.SourceData, InStr(1, currPT.SourceData, "!") + 1)
        Next currPT
     Next currWS
    End Sub

                

    From all the posts in here, this is the only one that worked for me.
    I built upon the code making it more robust:

    Remark: This fix only works for datasources of pivot tables that have been defined as a NAMED RANGE.
    'Change the absolute path of the data source back to a relative one (that resides within the workbook).
    'This fix only works for datasources of pivot tables that have been defined as a NAMED RANGE (which you should do always anyway).
    Sub UpdatePivotTablesSourceDataRange()

        Dim ws As Worksheet
        Dim pt As PivotTable

        On Error GoTo ErrHandler

        Application.EnableEvents = False
        '[If applicable unprotect all the protected sheets that contain a pivot table]

        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                'Set the data source to the part after the exclamation mark ('!')
                pt.SourceData = Mid(pt.SourceData, InStr(1, pt.SourceData, "!") + 1)
            Next
        Next

    CleanUp:
        '[If applicable protect all the sheets that were unprotected here]
        Application.EnableEvents = True
        Exit Sub
    ErrHandler:
        MsgBox Err.Description & vbCr & "Err.nr: " & Err.Number, vbExclamation, "Fix it"
        Resume CleanUp
    End Sub

    • Proposed as answer by Mike063 Thursday, August 24, 2017 11:46 AM
    • Unproposed as answer by Mike063 Thursday, August 24, 2017 11:46 AM
    Wednesday, August 23, 2017 9:21 AM