Loop through Power Query Connections adding them as table to new XL Worksheet

[Raddle]

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

[Raddle]

.. so on further investigation, the issue with the routine above, appears to be that it is not creating an actual connection between the table and the PQ.
It is slurping the data from the PQ and sticking it in a table, but upon refresh, although it doesn't error, it doesn't know to request the data from the PQ name.
In Table properties indeed, there is no connection named according to the 'item' variable.

Any help will be explosive .....

[pbaldy]

I was hoping someone would jump in that had a better understanding of what you're trying to accomplish. I've worked mainly from Access automating Excel, I haven't worked much from within Excel. From my Access experience I'd be suggesting opening a recordset on the Access data and then using CopyFromRecordset to drop that into Excel. Since we've resolved the original issue, perhaps a new thread is in order to get more attention on this?