Excel Native Query Unlink/Refresh problem (BUG MAYBEÉ)

[taleb]

Hi,
I have a problem with the NATIVE Excel Query as follows:

I have a master file that contains 5 queries loading data from 5 tables on 5 different files on a SharePoint site. I also have a MASTER QUERY that combines these 5 queries into one Master Query. All work fine when it is all connected. However, I need to freeze the data each month and generate various reports accordingly. My technique is to disconnect the 5 queries from their source 5 files and then refresh the Master and get the frozen data as of that moment.

Here is the problem, EVEN after I disconnect the 5 queries, when I refresh ONLY the MASTER QUERY, Excel will still go and try to refresh those 5 queries from the files. If I try to disconnect my SharePoint site (or relocate the files to stop the refresh) Excel will error out and abort the whole refresh. The error that comes is that these queries cannot load data from the source. Please note that at this point I do not need any external data connection. I only need the connection from the Master Query to the 5 queries. I did try the File Inspector to remove xml data but it also disconnects the Master Query.

I do this process monthly and I need find a way to avoid this bug. Converting the queries into tables and rebuilding the MASTER QUERY by combining the 5 tables is not practical and will almost beat the purpose of using the queries in the first place.

Your feedback will be greatly appreciated.

 

[mole999]

have you tried saving the book as xlsx ?

 

[taleb]

Why would that make a difference? Beside, i have macros that I need to run on the file so this wouldn't work.