vba - Refresh All Queries in Workbook -

this works .xls books, can altered .xlsx workbooks well? or syntax work both?

option explicit public sub refreshqueries()   dim wks worksheet   dim qt querytable   each wks in worksheets     each qt in wks.querytables         qt.refresh backgroundquery:=false     next qt   next wks   set qt = nothing   set wks = nothing end sub 

edit -- seems syntax refresh .xlsx workbooks, not queries sql server. how can refreshed via vba.

first, no macro work in .xlsx workbook because .xlsx workbooks can't contain macros - need save macro-enabled workbook has extension .xlsm.

in excel 2007 , later, user created external data connections sql server data sources (amongst others) result not in querytables member, in listobject possess querytable object can accessed via listobject.querytable property - see dick kusleika's answer this question. following code should refresh both types of queries:

option explicit public sub refreshqueries()    dim wks worksheet   dim qt querytable   dim lo listobject    each wks in worksheets     each qt in wks.querytables         qt.refresh backgroundquery:=false     next qt      each lo in wks.listobjects         lo.querytable.refresh backgroundquery:=false     next lo    next wks    set qt = nothing   set wks = nothing end sub 

i wasn't familiar listobject type don't know if can have listobject on worksheet doesn't have querytable, might cause error in above code - might need check this.