Class WorkbookQuery (Excel VBA)

An object that represents a query that was created by Power Query. Introduced in Office 2016.

Set

To use a WorkbookQuery class variable it first needs to be instantiated, for example

Dim strName As String: strName = 
Dim strFormula As String: strFormula = 
Dim wb As Workbook: Set wb = 
Dim wqy As WorkbookQuery
Set wqy = wb.Queries.Add(Name:=strName, Formula:=strFormula, Description:="")

The following procedures can be used to set variables of type WorkbookQuery: Workbook.Queries, Queries.Item and Queries.Add

For Each

Here is an example of processing the WorkbookQuery items in a collection.

Dim wb As Workbook: Set wb = 
Dim wqyQuerie As WorkbookQuery
For Each wqyQuerie In wb.Queries
	
Next wqyQuerie

Queries.Add

Adds a new WorkbookQuery object to the Queries collection.

Syntax : expression.Add (Name, Formula, Description)

Dim myConnection As WorkbookConnection
Dim mFormula As String
mFormula = _
"let Source = Csv.Document(File.Contents(""C:\data.txt""),null,""#(tab)"",null,1252) in Source"
query1 = ActiveWorkbook.Queries.Add("query1", mFormula)

Arguments

The following arguments are required:

Name (String) - The name of the query.

Formula (String) - The Power Query M formula for the new query.

The following argument is optional

Description (String) - The description of the query.

Queries.Count

Returns an integer that represents the number of objects in the collection.

Queries.FastCombine

True to enable the fast combine feature, as long as the workbook is open.

For silent refresh operations, use the FastCombine property in conjunction with the Application.DisplayAlerts property set to False.

Queries.Item

Returns a single object from a collection.

Syntax : expression.Item (NameOrIndex)

NameOrIndex: The name or index number of the item.

Queries.Parent

Returns the parent object for the specified object. Read-only.

Delete

Deletes this query and its underlying connection and removes it from the Queries collection.

Description

The description of the query.

Formula

The Power Query M formula for the object.

Name

The name of the query.

Parent

Returns the parent object for the specified object. Read-only.