Class Queries (Excel VBA)

The collection of WorkbookQuery objects introduced in Office 2016. To use a Queries class variable it first needs to be instantiated, for example

Dim qrs as Queries
Set qrs = ActiveWorkbook.Queries

For Each

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

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

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 (String) - The description of the query.

Count

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

Dim lngCount As Long
lngCount = ActiveWorkbook.Queries.Count

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

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)