Cmdlet

Add-OfficeExcelConnection

Aliases: ExcelConnection

Module: PSWriteOffice
Aliases: ExcelConnection
Inputs: OfficeIMO.Excel.ExcelDocument
Source: AddOfficeExcelConnectionCommand.cs:31 Edit on GitHub

Adds Power Query or connection metadata that Excel can own and refresh.

Syntax

Add-OfficeExcelConnection
[-CommandText <String>]
[-Description <String>]
[-Document <ExcelDocument>]
-Name <String>
[-PassThru]
[-QueryTable <String>]
[-RefreshOnOpen]
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]

Parameter set: PowerQuery

Add-OfficeExcelConnection
[-Document <ExcelDocument>]
-Kind <Connection|QueryTable>
[-PassThru]
[-Sheet <OfficeSheetSelector>]
-Xml <String>
[<CommonParameters>]

Parameter set: Xml

Examples

Add a Power Query definition

Open-OfficeExcel -Path .\Reports\Sales-2026.xlsx {
    ExcelConnection -Name SalesQuery -CommandText 'let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content] in Source'
}

Stores a Power Query query that Excel can refresh; OfficeIMO writes the definition and never runs it.

Ship a workbook that refreshes from the warehouse

$query = 'let Source = Sql.Database("dw.example.com", "Sales"), Orders = Source{[Schema="dbo", Item="Orders"]}[Data] in Orders'
Open-OfficeExcel -Path .\Reports\Live-Sales.xlsx {
    ExcelConnection -Sheet Data -Name WarehouseOrders -CommandText $query -Description 'Orders from the sales warehouse' -QueryTable OrdersQuery -RefreshOnOpen
}

Adds a described query bound to a query table on the Data sheet and marks it to refresh when the workbook opens.

Parameters

-CommandText String optionalsets: PowerQuery: Power Query M expression stored as metadata.
-Description String optionalsets: PowerQuery: Connection description.
-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-Name String requiredsets: PowerQuery: Connection name stored in workbook metadata.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-QueryTable String optionalsets: PowerQuery: Native query-table metadata used by the connection.
-RefreshOnOpen SwitchParameter optionalsets: PowerQuery: Request refresh-on-open metadata.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-Kind OfficeExcelPackageMetadataKind requiredsets: Xml: Metadata kind to add.; Possible values: Connection, QueryTable
-Xml String requiredsets: Xml: XML payload to add as package metadata.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.