External Data Refresh all just for one worksheet

[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

 

[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

 

[FSt1]

hi
forgot to mention. see this site on getting started.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

regards
FSt1

 

[Andy]

Excellent, works great, thanks

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