[Excel VBA] How to Automatically Register Power Query Connections

Adding Power Query via VBA allows you to reload CSV or text files with a single click, simplifying daily data updates.

This article explains how to use the WorkbookQuery object to register a CSV data connection, along with sample code and key points.

Environment

Sample Code

Sub AddPowerQueryConnection()
    
    Dim csvPath  As String    ' Full path of the CSV file to load
    Dim mCode    As String    ' Power Query (M Language) code
    
    ' Build file path
    csvPath = ThisWorkbook.Path & "\sales_data_utf8.csv"
    
    ' ********** Power Query (M) Code  **********
    mCode = "let" & vbCrLf & _
            "    Source = Csv.Document(" & _
            "File.Contents(""" & csvPath & """)," & _
            "[""Delimiter"","",""," & _
            """Encoding"",""65001""])" & vbCrLf & _
            "in" & vbCrLf & _
            "    Source"
    ' **********************************************
    
    ' Check for existence to avoid duplication with existing queries
    Dim qryName As String: qryName = "PQ_CSVImport"
    Dim qry     As WorkbookQuery
    
    On Error Resume Next
    Set qry = ThisWorkbook.Queries(qryName)
    On Error GoTo 0
    
    If qry Is Nothing Then
        ' Add new if query does not exist
        ThisWorkbook.Queries.Add Name:=qryName, Formula:=mCode
    Else
        ' Update/Overwrite if it exists
        qry.Formula = mCode
    End If
    
    MsgBox "Power Query registration completed.", vbInformation
    
End Sub

Code Explanation

Advanced Ideas

• Load as a table on a separate sheet: Combine ActiveWorkbook.Connections.Add2 and ListObject.QueryTable.
• Dynamically change delimiters or column types: Replace the definition parts of Delimiter or Columns using string manipulation.
• Batch registration of multiple files: Manage filenames in an array and loop through them, changing the query name for each file.

Summary

Using WorkbookQuery allows you to insert and update Power Query M code directly from VBA.

Reloading external data, such as CSV files, can be completed with a single button, making monthly and weekly report updates more efficient. Checking for existing query names before adding or overwriting them makes query management within the workbook easier.

Why not try combining Power Query and VBA to automate your manual data updates?