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

[Raddle]

If I am not allowed to post this here - apols in advance and please just let me know but this forum has been sorting things out for me for years

Is it possible to create a loop that will go through a list of Connection Only PQs, adding them as tables?
If I record a single macro, of me manually doing this, it does work, which is unreal.
However I have a list of say 10 queries and I would like it to do it for me.

This is attempt, which creates the new sheet, and then creates a Table ref, dies on the highlighted line and the data doesn't refresh

Sub LoopThruPQsMakingTables()


'declare a variant array
Dim strNames(1 To 2) As String



'populate the array
strNames(1) = "Table_1"
strNames(2) = "Table_2"

'declare a variant to hold the array element

Dim item As Variant

'loop through the entire array
For Each item In strNames
Sheets.Add After:=ActiveSheet
Debug.Print item


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=item;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM item ")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = item
.Refresh BackgroundQuery:=False ' this is where it appears to error and it also doesn't loop to second array item which I guess is logical
'.ListObject.QueryTable.Refresh BackgroundQuery:=False ' tried this also but didn't work

End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=True


Next item
End Sub


Any hints ?

[Raddle]

If when it has died, I select the table it created and right click refresh, it says the Query 'item' was not found .. if that helps.

[pbaldy]

When it's part of a string, you have to concatenate the value into the string:

("SELECT * FROM " & item & ")"

[Raddle]

Hey

I put in this and variations but it still is not finding 'item'

.CommandText = Array("SELECT * FROM " & item & "")
.CommandText = Array("SELECT * FROM " & item & ")"

This feels like it is very close though and thank you so much

[pbaldy]

Your first one looks right, I mistakenly had the parentheses included. Did you also correct the

Location=item

instance?

[Raddle]

OMG ... Mr Baldy ... you are a legend my friend!

I mean its raining like billy-o outside but inside here, it's all sunshine and what-not.

I can't thank you enough.

Amazing!!!!!

[pbaldy]

LOL! Happy to help.

[Raddle]

I'm drinking, literally.

[Raddle]

One final thing ..

To have it format the table as is it creating it I added

.TableStyle = "TableStyleMedium10" at the end of the list of setup info

But it didn't' error and the table wasn't orange either.

Can I add a line in this loop to format it?

[pbaldy]

I'm drinking, literally.

I'm jealous, literally.

I'm going to plead ignorance on the formatting question, I've never done what you're trying to do.

[pbaldy]

One thought, wondering if that should be a constant rather than a string:

.TableStyle = TableStyleMedium10

What does it look like in your recorded macro?

[Welshgasman]

No, it appears it needs to be a string?
https://www.mrexcel.com/board/thread...table.1138633/

[alansidman]

crosspost https://www.mrexcel.com/board/thread...sheet.1248094/

[Raddle]

Hi - quick follow up to this ..

If I do not want to SELECT * and just want specific columns from the query, how do I structure that?

So NOT . CommandText = Array("SELECT * FROM " & item & "")

But more along the lines of

.CommandText = Array("SELECT 'Entity', 'AnotherFieldName' FROM " & item & "")

I have tried a few things but either I break it and it just doesn't load, or I get Entity in the first row, not as a header.

Any help appreciated.

[pbaldy]

You don't want the single qoutes around field names.