External Data Refresh all just for one worksheet

A

Andy

I have a worksheet with multiple external queries linked to Access. I want a
user to be able to refresh all external data, but just within this one
spreadsheet and not across the whole file.

Is there any VBA that I can put behind a button just to refresh all queries
within this one worksheet?

Thanks
 
F

FSt1

hi
the multiple MSQ's might be a problem. there is the refreshall command but
it can create problems if you have more that 2 or 3 for that it allows a
background refresh meaning you can continue working while the refresh is in
progress. sometimes the data don't refresh fast enough so i have never
recomended to allow a background refresh even for a single MSQ.
instead what i would do is select the upper left cell of the MSQuery range
of each query and refresh each individually.

sub refreshit()
Sheets("sheet1").activate 'change if needed
range("A1").QueryTable.Refresh BackgroundQuery:=False
'need above line for each MSQ on the sheet
Msgbox "Refresh complete."
end sub

the above code can be attached to a command button or toolbar icon. i
recomend the command button because the button follows the file.

regards
FSt1
 
A

Andy

Excellent, works great, thanks

FSt1 said:
hi
the multiple MSQ's might be a problem. there is the refreshall command but
it can create problems if you have more that 2 or 3 for that it allows a
background refresh meaning you can continue working while the refresh is in
progress. sometimes the data don't refresh fast enough so i have never
recomended to allow a background refresh even for a single MSQ.
instead what i would do is select the upper left cell of the MSQuery range
of each query and refresh each individually.

sub refreshit()
Sheets("sheet1").activate 'change if needed
range("A1").QueryTable.Refresh BackgroundQuery:=False
'need above line for each MSQ on the sheet
Msgbox "Refresh complete."
end sub

the above code can be attached to a command button or toolbar icon. i
recomend the command button because the button follows the file.

regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top