I have a workbook with with pivot tables using Data connections to raw data files some .xlsx and some .xlsb files.
If that raw data file is open by someone else when I refresh the data it opens the raw data file in excel and displays it all on screen.
I dont know why it does this but its frustrating, and i also don't want the people using the pivot tables to be able to access the raw data file or make any changes to it.
Is there a small bit of code that i can add in to my workbook that stops it opening of the external data connection file?
Or what would be even better, is it possible to password the data file but still keep the connection to it and allow it to be refreshed by the user even if they don't have the password to the raw data file?
This guy on another forum posted the same scenario but never got an answer to it: https://answers.microsoft.com/en-us/msoffice/forum/all/refresh-data-connection-on-a-protected/8360f9bb-5c99-4a33-82c5-07ec0890d0c4
There is also a few similar scenarios on this forum that never got an answer to resolve either so im hoping someone can assist !
Hope that all makes sense. Thanks in advance.