0

I have a worksheet that is set up to eventually have many power queries. I want to be able to use the pivot table and performance features of Excels Data Model. I have automated getting queries from a SharePoint folder using VBA, but I would like them to be added to the data model too.

I have tried the following VBA.

Sub CreateQueryAndAddToDataModel()
    Dim queryName As String
    Dim connectionName As String
    Dim connectionStr As String
    
    ' Set the query and connection names
    queryName = "MyPowerQuery"
    connectionName = "MyConnection"
    
    ' Set the connection string with the query name
    connectionStr = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & queryName & ";"
    
    
    Dim M_Script As String
    M_Script = "Let Source = Csv.Document(Web.Contents(SHAREPOINTPATH)) in Source"
    
    
    
    ' Create the query and connection
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:=M_Script
    
    ' Add the connection to the workbook
    ThisWorkbook.Connections.Add Name:=connectionName, Description:="", _
        ConnectionString:=connectionStr, CommandText:="", lCmdtype:=xlCmdSql
    
    ' Refresh the connection to load data into the data model
    ThisWorkbook.Connections(connectionName).Refresh
End Sub

This creates the query, but does not add the connection to the Data model.

SuchAgoodDoge
  • 1
  • 1

1 Answers1

0

I just found this is answered in this stack overflow thread.

Function LoadToDataModel(w As Workbook, query As WorkbookQuery, error As Integer) As Boolean
On Error GoTo Load_Error
    ' This code loads the query to the Data Model
    w.Connections.Add2 "Query - " & query.Name, _
        "Connection to the '" & query.Name & "' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , """" & query.Name & """", 6, True, False

    LoadToDataModel = True
    
Load_Exit:
    Exit Function
    
Load_Error:
    LoadToDataModel = False
    error = Err.Number
    Resume Load_Exit
End Function
SuchAgoodDoge
  • 1
  • 1