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
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.