Log In
Register
Home
Active Member
January 5, 2022
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Active Member
January 5, 2022
Offline
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.
1 Guest(s)
