VBA to Create Excel Table from PowerQuery Connection only

[Raddle]

Hi



Following on from this thread Loop through Power Query Connections adding them as table to new XL Worksheet - Page 2 (accessforums.net) where we were able to create tables from connections.

However, the flaw is that the table doesn't appear to have a meaningful connection behind it.
This means that although the data loads first time round, when you change the source data and refresh the table, the new data does appear in Power Query, but does not copy across to the Excel table.

I believe this is because the routine fails to create a meaning Connection behind the table.

In Data / Connections, all we see is an entry named Connection ...

Can anyone help with how to create a table from a connection, WITH the corresponding connection ? (Apologies for such poor description - not sure how else to put it)

I recorded a macro, where I manually 'Load To', in order to create a table and this will refresh when the source data changes.

This also has a more meaningful name in Data / Connections - something like Query - Connection to QueryName

However I am not able to parameterise this from an array of pre existing connection names.

As advised this is a new thread specific to the connection rather than just the tables.

Day three now .. this is hurting

[Gicu]

Could this help?
https://www.excelcampus.com/vba/powe...ly-all-tables/

EDIT: and another link:
https://stackoverflow.com/questions/...r-query-in-vba

Please post what you currently have.

Cheers,

[Raddle]

SSConnections.zipHey Gicu

Thanks so much for replying. So the first thread is doing the opposite of what I need to do. It is taking existing Excel tables and creating PQs from those.

The second thread is pretty much what I have now (see below) but I suspect the issue with it (about to test it now) is that it does not create a meaningful data connection as part of the routine, meaning that you cannot refresh the data in the table after it is created.

It is like it creates a point in time snapshot and changes to source data are not reflected.

If you manually create the table **right click query / Load To Table /** you end up with a clear data connection, as can be seen in Data / Connections on the Excel ribbon.

Any help hugely appreciated.


Sub LoopToCreate() ' LoopToCreateSheetsAndTablesFromQueries
'declare a variant array

Dim ws As Worksheet


Dim StartTime As Double
Dim MinutesElapsed As String


'populate the array
On Error Resume Next

Qnames = Array("QueryNameHere")



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

' Create the tables from the list of queries

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 = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = item ' this doesn't need concatenating
.Refresh BackgroundQuery:=False
.TableStyle = "TableStyleMedium10"
.AutoFilter = False
.ShowTotals = True


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

'Create connections between the queries and the tables, without which the tables will not refresh even though the data is in PQ - this is where I have had a go at creating a meaningful data connection but it is not working obvs
With ActiveWorkbook.Connections("Connection")
.name = item
.Description = "Connection for " & item & " query in Power Query"

End With

Next item

End Sub

This is what you get if you Load To Table 'manually'

[Raddle]

Hey Gicu

Thanks so much for replying. So the first thread is doing the opposite of what I need to do. It is taking existing Excel tables and creating PQs from those.

The second thread is pretty much what I have now (see below) but I suspect the issue with it (about to test it now) is that it does not create a meaningful data connection as part of the routine, meaning that you cannot refresh the data in the table after it is created.

It is like it creates a point in time snapshot and changes to source data are not reflected.

If you manually create the table **right click query / Load To Table /** you end up with a clear data connection, as can be seen in Data / Connections on the Excel ribbon.

Any help hugely appreciated.


Sub LoopToCreate() ' LoopToCreateSheetsAndTablesFromQueries
'declare a variant array

Dim ws As Worksheet


Dim StartTime As Double
Dim MinutesElapsed As String


'populate the array
On Error Resume Next

Qnames = Array("QueryNameHere")



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

' Create the tables from the list of queries

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 = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = item ' this doesn't need concatenating
.Refresh BackgroundQuery:=False
.TableStyle = "TableStyleMedium10"
.AutoFilter = False
.ShowTotals = True


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

'Create connections between the queries and the tables, without which the tables will not refresh even though the data is in PQ - this is where I have had a go at creating a meaningful data connection but it is not working obvs
With ActiveWorkbook.Connections("Connection")
.name = item
.Description = "Connection for " & item & " query in Power Query"

End With

Next item

End Sub