I have a query that pulls data from an MSAccess backend table and creates a table of hours booked by a selected person. The spreadsheet user can change the selected person by a combo box with VBA code that changes a cell parameter followed by “ThisWorkbook.RefreshAll”.
Most of the time this works fine, but occasionally I get a “Download Failed” alongside the query.
If I select Data/RefreshAll/RefreshAll from the ribbon or press Ctrl+Alt+F5 the query loads OK.
A VBA Refresh does not appear to act in the same manner as the Ribbon refresh. How do I make the VBA Refresh code work as well as the Ribbon Refresh?
GrahamG
Hi Graham,
Is it just one query you mentioned or there are more objects to refresh?
Instead of RefreshAll, try referencing the object:
ThisWorkbook.Worksheets("Sheet1").ListObjects("TableName").QueryTable.Refresh BackgroundQuery:=False
If there are more objects to refresh, you can even play around and build combinations of object refresh as above then run RefreshAll. In excel interface, you can set the object you refresh in code to not refresh on RefreshAll.
Hi Catalin,
Thank you for the reply, over Christmas as well.
I only have four queries in the spreadsheet and the same one occasional gives the error “Download Failed.”
I tried your suggestion, and it works. However, if I leave the cursor hovering over the spin button the VBA code for it keeps triggering and gives several 50290 errors? But by including in the spin button code activation of another worksheet, adding Application.ScreenUpdating=False/True and a couple of other bits of code it now works.
However, I am suspicious of the spin button and may change it to a List Box.
As a PS. I recorded a macro for selecting the “Data/RefreshAll/RefreshAll” from the ribbon and got VBA code of “ThisWorkbook.RefreshAll.” If when I get the error, I run this recorded macro then it does not clear the “Download Failed” error but selecting “Data/RefreshAll/RefreshAll” from the ribbon does. Is this because Windows is written in C?
Again thanks.
