Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Data Refresh all just for one worksheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Data Refresh all just for one worksheet
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 refre****() 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 "Andy" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Data Refresh all just for one worksheet
hi
forgot to mention. see this site on getting started. http://www.mvps.org/dmcritchie/excel/getstarted.htm regards FSt1 "Andy" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Data Refresh all just for one worksheet
Excellent, works great, thanks
"FSt1" wrote: 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 refre****() 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 "Andy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic refresh of external data | Excel Worksheet Functions | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
External data refresh | Excel Discussion (Misc queries) | |||
external data refresh | Excel Discussion (Misc queries) | |||
External Data Refresh not working | Excel Discussion (Misc queries) |