Background Web Query Refresh Bug

Category: excel for developers

Question

Kaveh7 on Sat, 18 Nov 2017 04:54:06


hello,

I have a webquery which pulls currency data into a hidden sheet, this data is then referenced in a cell in the 'main' sheet which is visible.

In the main sheet I have a button which runs the macro, which only does this:

Sheets("Rates").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

Problem is when I run the macro it for some reason activates the hidden sheet, which makes the button disappear and think its on the hidden sheet even though its displaying the 'main' sheet. Crazy thing is even if after the refresh I include 'Sheets(1).Activate' to change the focus back to the main sheet, it doesnt do anything! But when I click on the tab for the main sheet, it corrects and the button re-appears etc.

I have found this is only happening on my version of excel 2016: MSO (16.0.4266.1001) 32-bit.

This does not occur on excel 2013 or the click to run versions of excel 2016, build 1701.

What to do? Incredibly frustrating as I use this spreadsheet heavily everyday...

Replies

Chenchen Li on Mon, 20 Nov 2017 06:15:06


Hello,

The code works fine for me in Excel 2016 MSO 16.0.4549.1000 32bit. 

As you are using old version, please install Office updates and test again.

Regards,

Celeste

Kaveh7 on Mon, 20 Nov 2017 07:25:49


Hello,

How do I install updates for this version, there is no update button under file> account?

Chenchen Li on Mon, 20 Nov 2017 07:35:40


Hello,

You could check Windows Updates.

Please visit

Install Office updates 

Update Office with Microsoft Update 

Regards,

Celeste

Kaveh7 on Tue, 21 Nov 2017 04:28:19


Managed to update my excel to 16.0.4549.1000 via the second link you posted and it is now working!

thank you!