Forum Discussion

JerryDNYC's avatar
JerryDNYC
Copper Contributor
Oct 16, 2022
Solved

VBA to delete Power Query Connections or Folder

I have a master workbook with 10 Power Query connections. That workbook is a Read-Only file, and has a macro that saves the workbook with a different file name.   Once the file has been saved I wan...
excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Oct 17, 2022

    JerryDNYC You need code like this to delete the queries:

    Sub DelQueries()
    Dim q As WorkbookQuery
    For Each q In ActiveWorkbook.Queries
        If MsgBox("Delete query '" & q.Name & "'?", vbYesNo) = vbYes Then
            q.Delete
        End If
    Next
End Sub
JKPieterse's avatar
JKPieterse
Silver Contributor
Oct 17, 2022

JerryDNYC You need code like this to delete the queries:

Sub DelQueries()
    Dim q As WorkbookQuery
    For Each q In ActiveWorkbook.Queries
        If MsgBox("Delete query '" & q.Name & "'?", vbYesNo) = vbYes Then
            q.Delete
        End If
    Next
End Sub
  • sal_iv's avatar
    sal_iv
    Copper Contributor
    Feb 08, 2023

    JKPieterse Is it possible to delete the queries within the active worksheet alone. 

    I tried using the below query, but it fails with error. 

    Sub Delete_Queries()
           Dim q As WorkbookQuery
           For Each q In ActiveSheet.Queries

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Feb 08, 2023

      sal_iv That requires a different approach:

       

      Sub DelQueries()
    Dim q As WorkbookQuery
    Dim c As WorkbookConnection
    
    For Each c In ActiveWorkbook.Connections
        If c.Ranges.Count > 0 Then
            If c.Ranges(1).Parent.Name = ActiveSheet.Name Then
                c.Delete
            End If
        End If
    Next
End Sub
      • sal_iv's avatar
        sal_iv
        Copper Contributor
        Feb 08, 2023

        Thanks for the Quick response!!

        For me the above quries doesn't remove the Queries in the worksheet.
        In the Data & Connections pane, I can see I only have Queries. (Sorry I'm new to these.)

        So I edited your code to the following.

        ```
        Sub DelQueries()
            Dim q As WorkbookQuery
            Dim c As WorkbookConnection

            For Each q In ActiveWorkbook.Queries
                If q.Ranges.Count > 0 Then
                    If q.Ranges(1).Parent.Name = ActiveSheet.Name Then
                        q.Delete
                    End If

                Next
        End Sub
        ```

        However, it errors out “Object doesn't support this property” for the line "If q.Ranges.Count > 0 Then"

  • JerryDNYC's avatar
    JerryDNYC
    Copper Contributor
    Oct 17, 2022
    Worked perfectly. Thank you Jan!

Resources