I've assigned a macro to the ribbon to update 4 PQs. This is done via VBA because I need the refresh to be ordered; Query 4 Appends the other three into a single query that is then used as the PowerPivot connection. This is Excel 2010 so the load options are set to "connection only" as there is no direct data model option. Background refresh is disabled and the macro executes with no errors however, I can't tell that anything is actually being done. The query panel still reflects that the PQs have not been refreshed recently and the query editor indicates the same. All PQs (except the 4th) are connected to external workbooks. I would really like to make this refresh macro work so I don't have to instruct users to go into the PQ editor and refresh, load each query each time; this will inevitably create an issue. Macro aside, simply clicking refresh connection for each query name doesn't seem to do anything either. I'm starting to suspect that this might be due to the fact that none of the queries are loaded to worksheets and connection only. Any work around for this? I've searched extensively and can't find anything related to this, I thought this would be a common issue for sure.
