Excel VB Power Query

Question

I am using excel 2019, Windows 10 and have imported 2 tables via Power Query. The resulting query shows in the Workbook Query area. When I merge the two tables via a power query merge query, the merge query also shows up in the Workbook Query area. Everything is as it should be. The problem is, in order to repetitively use the merge query in visual basic code, I have to delete the instance of the merge query using visual basic code and I can't find a way to delete the merge query. Can anyone help me?

Thanks

Answer 1

If I assume that the name of the query is MyMerged:

Sub Test()
Dim Q As WorkbookQuery
For Each Q In ThisWorkbook.Queries
If Q.Name = "MyMerged" Then
Q.Delete
Exit For
End If
Next
End Sub

Andreas.

Answer 2

Thanks, this works perfectly with some minor changes based upon what I am trying to do.

Thanks

Answer 3

Thanks. This interesting. Not quite what I need but the "CommandBar" line looks interesting. Not sure I understand since I don't use the command bars alot but something to look into. Thanks

Answer 4

Hi,

Please check whether the following code is helpful.

Please add the following code in Your existing code.

Workbooks("Book3").Activate

Worksheets("Sheet1").Activate

Range("SalesReport[#All]").Select

Application.CommandBars("Queries and Connections").Visible = Not Application.CommandBars("Queries and Connections").Visible

Selection.ListObject.QueryTable.Delete

Selection.ClearContents

Modification 1: in the above code, please replace Book3 with the workbook name in which the merged query resides.

Modification 2: in the above code, please replace Sheet1 with the name of the worksheet in which the merged query resides.

Modification 3: in the above code, please replace SalesReport with the name of the merged query.

Hope, this helped.

Please respond if You require further assistance. I will try My Best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!