Excel Pivot table error is: Cannot open Pivot Table source file C:\Users\UserName\AppData\Roaming\Microsoft\Excel\DatabaseName (version1).TableName
I’ve seen other questions and answers with the same topic, but I think this is different.
I believe I know why the error is occurring: Excel closed unexpectantly and did autosave with (version1) attached to the original file name and saved it in the C:\User etc. above , which is the default recovery location. I opened the recovered file in Excel, saved it as version1 on the server where the original file was located, deleted the original file, and renamed the version1 to the original name.
When I go to PivotTable Tools? Options? Change Data Source, it shows only the Table and Range, which are correct, but it does not show the file name or path.
The version1 and the renamed file both had the same structure, so the same source table was in both, by they were different files.
How do I change the source file from what it is looking for to my renamed file?
PS- The (version1) file that it says it is looking for is not in the autosave location, i.e. it is not at the path where it says it is looking in.
Thank you for any help
Ken