Refreshall crashes VBA execution

Asked

Viewed 31 times

0

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.

excel vba

1 answer

0

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.

  • So I even thought about doing this but the amount of dynamic tables and queries are large and in several files, would have to do in all.

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.