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

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

August 19th, 2013 3:29pm

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 cant 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


Free Windows Admin Tool Kit Click here and download it now
August 20th, 2013 9:08am

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)




August 20th, 2013 10:09am

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 cant 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.


Free Windows Admin Tool Kit Click here and download it now
August 20th, 2013 11:25am

So, I guess there is no fix for this issue???
August 22nd, 2013 9:47am

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..

Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2013 1:36pm

Anyone anything?

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

August 27th, 2013 10:27am

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.

Free Windows Admin Tool Kit Click here and download it now
September 25th, 2013 7:56pm

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
March 18th, 2014 7:48pm

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
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2014 11:59am

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
April 21st, 2014 7:36pm

I agree. 
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2014 6:40pm

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

October 3rd, 2014 10:57pm

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.
Free Windows Admin Tool Kit Click here and download it now
November 6th, 2014 3:09pm

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

January 8th, 2015 4:26pm

"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?

Free Windows Admin Tool Kit Click here and download it now
January 9th, 2015 11:04am

"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? 

January 9th, 2015 11:08am

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
Free Windows Admin Tool Kit Click here and download it now
January 9th, 2015 11:24am

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
January 22nd, 2015 8:11am

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!

Free Windows Admin Tool Kit Click here and download it now
February 16th, 2015 9:15pm

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.

February 20th, 2015 4:01pm

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,

Free Windows Admin Tool Kit Click here and download it now
March 6th, 2015 10:26am

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.

March 9th, 2015 9:35pm

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. 
Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2015 10:40pm

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?? 



April 11th, 2015 3:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics