Direct path in data source to Pivot Tables

[Bartłomiej Grzebinoga]

Dear Community,

I have an issue with the data sources in my pivot tables while putting the Excel file to the version control system.

Every time I check in the file to the version control system and then open it, all links to the tables in the data sources of the pivot tables are replaced with the direct paths (which are reflecting the path from the version control system) what is making me unable to refresh the pivot table any more.

What can I do to avoid adding the direct path to the pivot table data source?

As a workaround I am using the following makro, but it does not work always:

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

Excel version: 2016 MSO (16.0.9126.2116) 32 bit

[#2]

Hi
Could you explain how you are getting data into the Pivot Tables?
Are you pulling data into the Pivot Tables directly, or pulling the data into Tables and then referencing those?

Are you using Power Query to pull in your data?

[#3]

I am pulling data from the Oracle database to the Excel Table and then I am using that table as a source for the Pivot Table.

I am not using Power Query.