Hello,
Everytime I refresh the queries, this message appears and I want it to go away.."can't open pivot table source file' I looked everywhere trying to find the file and now come to think of it, I might have deleted it. Queries work just fine and gives the data I need, but I do not want this message to pop up everytime I refresh the queries...
Any advice, please?
Thank you
Hi Luna,
Are you clicking the 'Refresh All' button or refreshing each query individually? I'm asking because that error shouldn't appear if you're just refreshing the query.
Have you got a PivotTable in the file? If so, have you edited the PivotTable source data range to correct the source? Have you checked the 'Data Connections' list on the Data tab?
I hope that gives you some ideas.
Mynda
Hi Mynda,
True, I tried by refreshing the query itself and the message did not appear at all - thank you! But I would like to use 'Refresh All' button because there are Pivot Tables in multiple tabs.
I don't recall using the file that appears in the error message and the Pivot Table source data range is correct, it is in the same workbook. I checked the 'Data Connections' list on the Data tab but the file was not there.
Thank you - L
You could try temporarily changing the PivotTable source and then change it back. The only other place might be a named range, but this wouldn't trigger that error on refresh.
I tried your suggestions but unfortunately didn't work.. the message still pops up. There seems to be a VBA code to handle it but I am now more curious to know what causes this? The file has been deleted! The error message is really annoying and misleading that there is a problem with the file. If you have any other suggestions, please share.. Thank you - L
There is a known bug with PivotTables losing the connection to the source data. It can happen if you rename or save a copy of the file containing the PivotTable. I wouldn't waste any more time on it. You might be just as well off rebuilding the file from scratch, as painful as that sounds.
Thank you, it is good to know! I really like coming here asking for advice, my membership will expire soon, it is sad that I wont be here much longer. It is such a great and fast way to learn Excel!
You're welcome, Luna. You can always renew your membership for 50% off 😉 See your 'My Profile' page for details.
Hi Luna,
See this article for a solution to fix the existing file, it might be hard to replicate your reports into a new file.
I think it's easy to fix.
