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

[Raddle]

Hi

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 what I get from the first one that works:

Sub Macro7()
'
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Pensions;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Pensions]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Pensions"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub


So I think I need something like an array of the query names and then replace the Pensions ref in here with Array(i) but I can't quite nail it.

Any hints ?

 

[Raddle]

Ok so working through this ... got there in the end. Key is obvs to concatenate the array names and it all works beautifully.

Thanks to Mr B on another forum (and if you see this buddy, thank you again.)

Sub LoopForArrayStaticTest()
'declare a variant array
Dim strNames(1 To 2) As String
Dim Ws As Worksheet

' The fix was to make sure the TWO concatenations were correctly setup. One for LOCATION and second in the SQL array .... Thanks again Mr B.

'populate the array
strNames(1) = "Queryname1"
strNames(2) = "Queryname2"

'loop through the entire array
For Each item In strNames
Sheets.Add After:=ActiveSheet
ActiveSheet.name = item
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 & "") ' this is where you need to concatenate the array value name
.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 ' this doesn't need concatenating
.Refresh BackgroundQuery:=False


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

Next item
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Loop through Power Query Connections adding them as table to new XL Worksheet
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.