Power Query Manager: Export, Import & Document Queries with VBA

Overview

Excel VBA Power Query

Power Query's M code lives inside the workbook and is not easily version-controlled or shared between files. This set of four VBA macros lets you extract, export, import, and remove Power Query definitions, giving you full control over your query library.

Extract dumps all query code to a new workbook for review. Export saves each query as a .txt file for version control. Import loads .txt files back as new queries. Remove lets you selectively delete queries with confirmation.

Use Cases

Version Control: Export queries as text files and track them in Git alongside your other code
Migration: Move queries between workbooks by exporting from one and importing into another
Documentation: Extract all M code into a single workbook for review or compliance audits
Cleanup: Selectively remove queries you no longer need with a confirmation prompt for safety

The VBA Code

Public Sub Extract_PQ_Advanced_Editor()
    Dim Qry_Cnt As Integer: Qry_Cnt = ActiveWorkbook.Queries.Count
    If Qry_Cnt = 0 Then MsgBox "No query found": Exit Sub
    
    Dim Qry_Cde() As String: ReDim Qry_Cde(1 To Qry_Cnt)
    Dim Qry_Nam() As String: ReDim Qry_Nam(1 To Qry_Cnt)
    Dim i As Integer
    
    On Error Resume Next
    For i = 1 To Qry_Cnt
        Qry_Cde(i) = ActiveWorkbook.Queries.Item(i).Formula
        Qry_Nam(i) = ActiveWorkbook.Queries.Item(i).Name
    Next i
    On Error GoTo 0
    
    Workbooks.Add
    For i = 1 To Qry_Cnt
        Range("A" & i).Value = Qry_Nam(i)
        Range("B" & i).Value = Qry_Cde(i)
    Next i
    Columns("A:B").EntireColumn.AutoFit
End Sub

Public Sub Power_Query_Exporter()
    Dim Qry_Cnt As Integer: Qry_Cnt = ActiveWorkbook.Queries.Count
    If Qry_Cnt = 0 Then MsgBox "No query found": Exit Sub
    
    Dim Qry_Cde() As String: ReDim Qry_Cde(1 To Qry_Cnt)
    Dim Qry_Nam() As String: ReDim Qry_Nam(1 To Qry_Cnt)
    Dim i As Integer
    
    For i = 1 To Qry_Cnt
        Qry_Cde(i) = ActiveWorkbook.Queries.Item(i).Formula
        Qry_Nam(i) = ActiveWorkbook.Queries.Item(i).Name
    Next i
    
    ' Select export folder
    Dim fd As Object: Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    Dim Folder_Path As String
    With fd
        .Title = "Select a Folder"
        If .Show = -1 Then Folder_Path = .SelectedItems(1) Else Exit Sub
    End With
    
    For i = 1 To Qry_Cnt
        Dim fileNumber As Integer: fileNumber = FreeFile
        Open Folder_Path & "" & Qry_Nam(i) & ".txt" For Output As #fileNumber
        Print #fileNumber, Qry_Cde(i)
        Close #fileNumber
    Next i
    MsgBox "Exported " & Qry_Cnt & " queries."
End Sub

Public Sub Power_Query_Importer()
    Dim Obj_Fle_Dlg As Object: Set Obj_Fle_Dlg = Application.FileDialog(3)
    With Obj_Fle_Dlg
        .Filters.Clear
        .Filters.Add "Text", "*.txt", 1
        .AllowMultiSelect = True
        .Show
        Dim Obj_Sct_Fle As Variant
        For Each Obj_Sct_Fle In .SelectedItems
            Dim Qry_Nam As String
            Qry_Nam = Mid(Obj_Sct_Fle, InStrRev(Obj_Sct_Fle, "") + 1)
            Qry_Nam = Left(Qry_Nam, InStrRev(Qry_Nam, ".") - 1)
            
            Dim Cde As String: Cde = ""
            Dim fNum As Integer: fNum = FreeFile
            Open Obj_Sct_Fle For Input As fNum
            Do Until EOF(fNum)
                Dim lineOfText As String
                Line Input #fNum, lineOfText
                Cde = Cde & lineOfText & vbCrLf
            Loop
            Close fNum
            
            ActiveWorkbook.Queries.Add Qry_Nam, Cde
            MsgBox "Added query: " & Qry_Nam
        Next Obj_Sct_Fle
    End With
End Sub

Public Sub Power_Query_Remover()
    Dim Qry As WorkbookQuery
    For Each Qry In ActiveWorkbook.Queries
        If MsgBox("Delete '" & Qry.Name & "'?", vbYesNo) = vbYes Then Qry.Delete
    Next
End Sub

Final Thoughts

Managing Power Query through VBA bridges the gap between Excel's data transformation capabilities and proper software development practices like version control and code sharing.