Class WorkbookQuery (Excel VBA)

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

Item

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

Dim wqy as WorkbookQuery
Set wqy = ActiveWorkbook.Queries(NameOrIndex:=1)

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

For Each

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

Dim wqyQuerie As WorkbookQuery
For Each wqyQuerie In ActiveWorkbook.Queries
	
Next wqyQuerie

Queries.Add

Adds a new WorkbookQuery object to the Queries collection.

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.

Optional arguments

The following argument is optional

Description - The description of the query

Description - The description of the query

Queries.Count

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

Dim lngCount As Long
lngCount = ActiveWorkbook.Queries.Count

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.

ActiveWorkbook.Queries.FastCombine = True

Queries.Item

Returns a single object from a collection.

Item (NameOrIndex)

NameOrIndex: The name or index number of the item.

Dim wqyQuery As WorkbookQuery
Set wqyQuery = ActiveWorkbook.Queries(NameOrIndex:=1)

Delete

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

Delete (DeleteConnection)

Description

The description of the query.

ActiveWorkbook.Queries(1).Description =

Formula

The Power Query M formula for the object.

ActiveWorkbook.Queries(1).Formula =

Name

The name of the query.

ActiveWorkbook.Queries(1).Name =

Refresh