Hey Mr PBaldy thanks for coming back .. I will try that.
meantime, is there anything in this excellent table creation script that would prevent excel from refreshing data into the table?
I have to delete the table and recreate it to get fresh data into the table, rather than it just refreshing the table with the existing query.
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & item & ";Extended Properties=""""" _
, Destination:=Range("$b$5")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & item & "") ' this is where you need to concatenate the array value name
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True ' I changed this from false but to no avail
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = item ' this doesn't need concatenating
.Refresh BackgroundQuery:=False
.TableStyle = "TableStyleMedium10"
.AutoFilter = False
.ShowTotals = True
!!!! the right data is visible in the Power Query but it is not refreshing to the Excel table as created above.
So sad. It was so beautiful until I spotted this ... hehehe