Welcome to my first 2020 #TipTuesday post. Today's topic is Excel and how a background refresh setting can impact refreshing pivot tables.
The background to this post is related to a report I was creating for a client to validate timesheets before approval. The report has a couple of queries in it (from SQL) and a pivot table to summarize the data. To make the refreshing easier, I created a tiny bit of VBA and a button for the users to click when they wanted to refresh the data, to pull in the latest timesheets. When testing it, it was doing everything except updating the pivot table. Here's how I resolved that!
Query Settings
Initially, my query settings (properties) looked like this. Both queries have "Enable background refresh" enabled. At the time of creating the queries, I didn't adjust these default settings.
My simple VBA looked like this. The first 2 lines are the 2 queries I have, which are pulling in some timesheet info and pulling in a filtered list of employees by manager. Those statements refresh the queries. The next two lines are to make the sheet with the pivot table active and then refresh the table on the page. Overall this is pretty basic code, nothing super fancy was needed here.
The Issue
The challenge was that the pivot table wasn't refreshing - or more accurately described - it didn't *seem* to be refreshing. It was doing what the command indicates; however, the commands are so short that it was refreshing the pivot while the background refresh of the underlying query was still working to update the queries.
In simplistic terms, "background refresh" means refresh in the background and not to wait for it to finish before continuing with the next command. In my case, I didn't want "background refresh", I want the query to complete updating before the next command runs. Background refresh may be appropriate if the query were to take time to execute, in which case I would look for other solutions to this problem. In my case, the query is small but long enough that it is still running by the time the pivot table refresh command was started.
The Fix
There are multiple ways to handle this but the choice in this case was to revisit the properties of the 2 queries, shown in the screenshot above, and then uncheck the "Enable background refresh" option. Once I did that, everything refreshed as I expected: queries first, then pivot table data.
I could have also handled it by disabling background refresh in the VBA but in this situation, managing it via the query was easy enough to do.
That's it for this tip… enjoy!
