Cmdlet

Set-OfficeExcelNamedRange

Aliases: ExcelNamedRange

Module: PSWriteOffice
Aliases: ExcelNamedRange
Inputs: OfficeIMO.Excel.ExcelDocument
Source: SetOfficeExcelNamedRangeCommand.cs:43 Edit on GitHub

Creates or updates a defined name.

Syntax

Set-OfficeExcelNamedRange
[-Document <ExcelDocument>]
[-Global]
[-Hidden]
[-Name] <String>
[-PassThru]
[-Range] <String>
[-Sheet <OfficeSheetSelector>]
[-ValidationMode <Sanitize|Strict>]
[<CommonParameters>]

Parameter set: Default

Examples

Name an input cell

Open-OfficeExcel -Path .\Finance\Budget-2027.xlsx {
    ExcelSheet 'Assumptions' {
        ExcelNamedRange TaxRate 'B2'
    }
}

Names B2 on the Assumptions sheet TaxRate so formulas can use =Amount*TaxRate.

Define the inputs of a model

New-OfficeExcel -Path .\Finance\Pricing-Model.xlsx {
    ExcelSheet 'Inputs' {
        ExcelCell A1 'Exchange rate EUR/USD'
        ExcelCell B1 1.08
        ExcelCell A2 'Target margin'
        ExcelCell B2 0.32 -NumberFormat '0%'
        ExcelNamedRange EurUsd 'B1'
        ExcelNamedRange TargetMargin 'B2'
    }
    ExcelSheet 'Prices' {
        ExcelCell A1 'List price (USD)'
        ExcelCell B1 -Formula '=1000*EurUsd/(1-TargetMargin)'
    }
}

Writes the model inputs and names each one, so the calculation sheets read like the business rules they implement.

Parameters

-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-Global SwitchParameter optional: Force a workbook-global name even inside a sheet block.
-Hidden SwitchParameter optional: Mark the defined name as hidden.
-Name String requiredposition: 0: Name of the defined range.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-Range String requiredposition: 1: A1 cell range on the selected worksheet.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-ValidationMode ExcelDefinedNameValidationMode optional: Validate or sanitize the defined name.; Possible values: Sanitize, Strict

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.