VBA to add to data model when creating Power Query tables`

[Raddle]

Hi

Does anyone know how to add to the data model, as part of a CVBA script that creates tables from existing Power Query queries?

This is that I have which creates the tables on new tabs nicely but I am hoping there is a switch I can add to add this to the data model at the same time.

Tried recording the macros, but can't seem to spot where it does it. Any help hugley appreciated.



Sub DC_Directorate_LoopToDynamicCreate() ' LoopToCreateSheetsAndTablesFromQueries
Dim ws As Worksheet
Dim Qconn As WorkbookQuery

' On Error Resume Next
' Acnts_DeleteSheets

cnt = ActiveWorkbook.Queries.Count 'Get the number of connections - this allows the loop to drain down

If Not cnt = 0 Then 'if there are still some in the queue then continue
For i = cnt To 1 Step -1 'Set the queue to the latest number
Set Qconn = ActiveWorkbook.Queries.Item(i) 'Set the QConn name to the one from the queue
'this is where we exclude certain names
' Debug.Print Qconn.name
'Section below to exclude helpers
If Qconn.Name <> "Sample File" And _
Qconn.Name <> "Transform File" And _
Qconn.Name <> "Transform File (2)" And _
Qconn.Name <> "Sample File (2)" And _
Qconn.Name <> "Sample File (3)" And _
Qconn.Name <> "Transform Sample File (3)" And _
Qconn.Name <> "Transform Sample File" And _
Qconn.Name <> "Transform Sample File (2)" And _
Qconn.Name <> "Parameter1" And _
Qconn.Name <> "Parameter2" And _
Qconn.Name <> "Source" And _
Qconn.Name <> "Sample File Parameter1" Then


'loop through the entire array

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = Qconn
Range("H1") = "Data - " & Qconn ' Put a title on the page
Range("H1").Select
' Debug.Print Qconn.Name


With Selection.Font
.Name = "Calibri"
.Size = 22
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleDouble
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
' Selection.Font.Underline = xlUnderlineStyleSingle

ActiveSheet.Tab.ColorIndex = 9 '3=Red , 4=green,5=blue,6=yellow,etc...

' Create the tables from the list of queries but how to add to data model at the same time ????

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Qconn & ";Extended Properties=""""" _
, Destination:=Range("$b$5")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM " & Qconn & "") ' this is where you need to concantenate the array value name
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True ' use PQ date formats etc
.RefreshOnFileOpen = False
.BackgroundQuery = False ' This updates the table connection properties
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = Qconn ' this doesn't need concatenation
.Refresh BackgroundQuery:=False

End With


'Create connections between the queries and the tables, without which the tables will not refresh even though the data is in PQ
With ActiveWorkbook.Connections("Connection")
.Name = Qconn
.Description = "Connection for ccc" & Qconn & " query in Power Query"

End With

End If
Next i

Else: MsgBox "No dice!"

End If

End Sub

 

[severynm]

It looks to me like this can be specified only when initially creating the WorkbookConnection - not after, as nearly all its properties are readonly. I am not familiar with creating connections this way, but are you able change how you are making the connection to make use of the CreateModelConnection parameter in Connections.Add when creating the connection?

 

[John_w]

Tried recording the macros, but can't seem to spot where it does it.

The 6th argument of the Connections.Add2 method, CreateModelConnection, specifies whether the connection is added to the Data Model or not.

    Function Add2(Name As String, Description As String, ConnectionString, CommandText, [lCmdtype], [CreateModelConnection], [ImportRelationships]) As WorkbookConnection

CreateModelConnection:=True - the Connection is added to the Data Model
CreateModelConnection:=False - the Connection is not added to the Data Model

In addition, it looks like the 5th argument, lCmdtype, is set to 6 (xlCmdType.xlCmdTableCollection) for Data Model connections.

Example generated by Macro Recorder:

    Workbooks("Book1").Connections.Add2 "Query - REPORT", _
        "Connection to the 'REPORT' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=REPORT;Extended Properties=" _
        , """REPORT""", 6, True, False

 

[Raddle]

Guys

Thank you both for sending those contributions. This is pushing the limit of my VBA here but I will persevere and come back when (if) I crack it.

Thank you again - never lets me down this forum x