Refreshall crashes VBA execution

Question

Hi, I looked, but I couldn’t find anything similar to my problem. I work in a company where we have some reports, in a remote terminal, which have queries to databases, after the query always inserted a ThisWorkbook.RefreshAll to update the dynamic tables and External Data Queries that may exist. It worked very well.

Issue is that some time to here the delay to execute the code became large and even lock Excel, after an analysis we found that the command ThisWorkbook.RefreshAll is always locking the execution and after removing from all files the queries returned to normal.

I was wondering if anyone’s had similar problems before, and how did you fix them? Because now users need to update the dynamic tables manually and the biggest problem is that if I have an External Data Query backing it also does not update itself.

Answer 1

Smooth?

I don’t know how many dynamic tables your spreadsheet has, but one possibility is to name in the code the list of dynamic tables to be updated. For example:

ActiveSheet.PivotTables("Tabela dinâmica1").PivotCache.Refresh
ActiveSheet.PivotTables("Tabela dinâmica2").PivotCache.Refresh

Thus, the spreadsheet updates only the mapped tables, instead of all external data ranges and dynamic table reports of the spreadsheet, which is what the ThisWorkbook.RefreshAll does and can take long to process.

If it doesn’t help or you need more information, let me know.