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

Anonymous

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

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

45 answers

Anonymous

2014-05-30T17:55:18+00:00

True! we are not asking for a customization but we are looking for a workaround of what looks like a serious issue for Excel practitioners.

Once an excel file is created with the absolute path for PivotTableDataSource, it looks like it is damned forever, no matter what you change in advanced options. It is still not clear to me what triggers the absolute path, it looks like it happens only in excel files where I’ve lots of pivot.

However, if you go in Excel Options/Advanced Options/Save external link values and uncheck it, then you should be able to modify all PivotTableDataSource from absolute to relative. I’ve written some code to do this and apparently the excel files are ok after that, but I’ve not tested it extensively.
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments
Anonymous

2016-07-12T20:39:27+00:00

There is a fix at the link. For each file you have to:

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

Next thing is to fix all data sources in a file once. Use whatever way you like.

That remark was posted on this forum as well. I wonder now why this was a problem for so long.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2015-09-15T20:44:07+00:00

This is not support! You link to the forum doesn't even search for the subject of this forum thread. Completely annoying!
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2013-09-04T14:32:55+00:00

Hi,

Even though you have mentioned that "All of the Pivot Tables use other sheets from the same file as Data Source", it looks like some Pivot Tables are coming from an external MS Excel file. That's the only way (I think), you can have the entire path display.

Microsoft, will there be any valuable answer???
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2013-08-20T11:24:00+00:00

I am pretty sure that I don't have any Pivot Tables with external Data Source.

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 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.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments