Set-SPExcelFileLocation
Sets properties of a trusted file ___location for Excel Services Application.
Syntax
Default (Default)
Set-SPExcelFileLocation
[-Identity] <SPExcelFileLocationPipeBind>
-ExcelServiceApplication <SPExcelServiceApplicationPipeBind>
[-AbortOnRefreshOnOpenFail]
[-Address <String>]
[-AssignmentCollection <SPAssignmentCollection>]
[-AutomaticVolatileFunctionCacheLifetime <Int32>]
[-ChartAndImageSizeMax <Int32>]
[-ConcurrentDataRequestsPerSessionMax <Int32>]
[-Confirm]
[-DefaultWorkbookCalcMode <DefaultWorkbookCalcMode>]
[-Description <String>]
[-DisplayGranularExtDataErrors]
[-ExternalDataAllowed <AllowExternalData>]
[-IncludeChildren]
[-LocationType <LocationType>]
[-ManualExtDataCacheLifetime <Int32>]
[-NewWorkbookSessionTimeout <Int32>]
[-PeriodicExtDataCacheLifetime <Int32>]
[-RequestDurationMax <Int32>]
[-RESTExternalDataAllowed]
[-SessionTimeout <Int32>]
[-ShortSessionTimeout <Int32>]
[-UdfsAllowed]
[-WarnOnDataRefresh]
[-WhatIf]
[-WorkbookSizeMax <Int32>]
[-ChartRenderDurationMax <Int32>]
[<CommonParameters>]
Description
The Set-SPExcelFileLocation cmdlet sets properties of a ___location from which Excel Services Application loads workbooks.
Excel Services Application loads only workbooks that are stored in a trusted file ___location.
Properties of trusted file locations control how workbooks can be used when they are loaded on Excel Services Application.
Excel Services Application always enforces the properties that are defined by the trusted file ___location from which a workbook was loaded.
The properties that are used by the trusted file ___location are determined by comparison of the file path for the workbook with the Address parameter of the trusted file ___location.
For permissions and the most current information about Windows PowerShell for SharePoint Products, see the online documentation at SharePoint Server Cmdlets.
Examples
EXAMPLE 1
Get-SPExcelFileLocation -ExcelServiceApplication "My Excel Service" | where { $_.externaldataallowed -eq "DclAndEmbedded"} | Set-SPExcelFileLocation -ExernalDataAllowed Dcl
This example gets every trusted file ___location for the Excel Services Application Web service application named My Excel Service that allows data connections to load from workbook files and specifies that the locations must use a data connection library to load data connections.
EXAMPLE 2
Get-SPExcelServiceApplication | Get-SPExcelFileLocation | where {$_.Address -eq "https://"} | Set-SPExcelFileLocation -Description "This is the default trusted file ___location for Excel Services Application. It allows any file from anywhere in SharePoint to load."
This example changes the description of the default file ___location for every Excel Services Application Web service application in the farm.
Parameters
-AbortOnRefreshOnOpenFail
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies that the load of a Excel Services Application file automatically fails if an automatic data refresh operation fails when the file is opened. The load is failed only if the following conditions are true:
-- User has read-only permissions to the Excel Services Application file. -- Workbook file has data connections that refresh automatically when the Excel file is opened.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-Address
Applicable: SharePoint Server 2010, SharePoint Server 2013
System.String
Specifies a Web folder, or file directory from which Excel Services Application can load workbooks.
The type must be a valid URL, in the form https://myPortal/myTeam; or a valid UNC path in form, \\server_name\folder_name
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-AssignmentCollection
Applicable: SharePoint Server 2010, SharePoint Server 2013
Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.
When the Global parameter is used, all objects are contained in the global store.
If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.
Parameter properties
| Type: | SPAssignmentCollection |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | True |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-AutomaticVolatileFunctionCacheLifetime
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the maximum time, in seconds, that a computed value for a volatile function is cached for automatic recalculations. Valid values include:
-1, calculate once when the workbook loads
0, always calculate
1 to 2073600, cache 1 second to 24 days
The type must be the integers -1 or 0, or an integer value in the range of 1 to 2073600.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ChartAndImageSizeMax
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the maximum size, in megabytes, of a chart or image that can be opened. The default value is 1.
The type must be a valid integer greater than 0.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ChartRenderDurationMax
Applicable: SharePoint Server 2010, SharePoint Server 2013
The maximum time (in seconds) spent rendering any single chart.
Valid values: -1 (no limit); from 1 through 2073600 (24 days).
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ConcurrentDataRequestsPerSessionMax
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the maximum number of concurrent external data requests allowed in each session. If a session must issue more than this number of requests, additional requests must be queued. The scope of this setting is the logical server. The default value is 5.
The type must be a positive integer.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-Confirm
Applicable: SharePoint Server 2010, SharePoint Server 2013
Prompts you for confirmation before executing the command.
For more information, type the following command: get-help about_commonparameters
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | cf |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-DefaultWorkbookCalcMode
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the calculation mode of workbooks. All of the settings except the File setting override the workbook settings. If this parameter is changed, it is applied to all new sessions on Excel Services Application when the ConfigDB properties cache is refreshed. The default setting is File.
The type must be one of the following: File, Manual, Auto, or AutoDataTables.
Parameter properties
| Type: | DefaultWorkbookCalcMode |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-Description
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies a friendly description for the new file ___location.
The type must be a valid string; for example, this is the file ___location of the monthly sales reports.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-DisplayGranularExtDataErrors
Applicable: SharePoint Server 2010, SharePoint Server 2013
Displays granular error messages for external data failures for files in this ___location.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ExcelServiceApplication
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the Excel Services Application Web service application that contains the SPExcelFileLocation list object.
The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services Application Web service application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.
Parameter properties
| Type: | SPExcelServiceApplicationPipeBind |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | True |
| Value from pipeline by property name: | True |
| Value from remaining arguments: | False |
-ExternalDataAllowed
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the type of external data access allowed for workbooks: None specifies that data refresh is disabled. Dcl specifies that data is refreshed only when it uses a connection file in a trusted data connection library. DclandEmbedded specifies that a data connection library can be used but is not required. The scope of this setting is the trusted ___location.
The default value is None.
The type must be one of the following: None, Dcl, or DclAndEmbedded.
Parameter properties
| Type: | AllowExternalData |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-Identity
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the FileLocation object to update. The name must be unique in the list of file locations.
The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid string identifying the file ___location, in the form https://myPortal/myTeam; a valid string identifying the path, in the form C:\folder_name; or an instance of a valid SPExcelFileLocation object.
Parameter properties
| Type: | SPExcelFileLocationPipeBind |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | 1 |
| Mandatory: | True |
| Value from pipeline: | True |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-IncludeChildren
Applicable: SharePoint Server 2010, SharePoint Server 2013
Indicates that subordinate URLs, directories and libraries are trusted. For example, if the trusted file ___location is https://portal, and IncludeChildren is true, then https://portal/subSite is also trusted, as is any subURL under the URL specified in Address.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-LocationType
Applicable: SharePoint Server 2010, SharePoint Server 2013
The type of ___location. LocationType indicates how Excel Services Application retrieves files from that ___location specified in Address. The default value is SharePoint.
The type must be one of the following: SharePoint, UNC, or HTTP.
Parameter properties
| Type: | LocationType |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ManualExtDataCacheLifetime
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the time, in seconds, that Excel Services Application waits before re-issuing a manual, or user-initiated, external data request. During this period, the results from the previous external data request may be returned from an external data cache.
Valid values are:
-1 Never refresh after first query
1 to 2073600 Caches 1 second to 24 days.
The default value is 300.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-NewWorkbookSessionTimeout
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the time, in seconds, that a session for a new, unsaved, workbook will remain active on Excel Services Application with no user activity. The default value 1800. Valid values include the following:
-1, session never expires
0, session expires at the end of a single request
1 to 2073600, remain active 1 second to 24 days.
The type must be -1, 0, or an integer in the range of 1 to 2073600.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-PeriodicExtDataCacheLifetime
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the time, in seconds, that Excel Services Application waits before re-issuing an on-open or periodic (that is, automatic) external data request. During this period, the results from the previous external data request may be returned from an external data cache. A value -1 specifies to never refresh after the first query.
The type must be -1, 0, or an integer in the range of 1 to 2073600.
The default value is 300.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-RequestDurationMax
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the maximum duration, in seconds, for a single request in a session. After this time elapses the request is timed out.
The type must be -1 (no limit) or an integer in the range of 1 to 2073600.
The default value is 300.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-RESTExternalDataAllowed
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies whether requests from the Representational State Transfer (REST) Application Programming Interface (API) are permitted to refresh external data connections.
The RESTExternalDataAllowed parameter has no effect if the External DataAllowed parameter is set to None.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-SessionTimeout
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the time, in seconds, that a session will remain active on Excel Services Application with no user activity. The default is 300 seconds. The default value is 300. Valid values include:
-1, session never expires
0, session expires at the end of a single request
1 to 2073600, remain active 1 second to 24 days
The type must be -1, 0, or an integer in the range of 1 to 2073600.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ShortSessionTimeout
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the time, in seconds, that a user has to make the initial interaction with a spreadsheet. After this time has elapsed, if the user is not active, then the session is closed. The value must always be less than SessionTimeout. If the ShortSessionTimeout value is greater than or equal to SessionTimeout, an error is returned. The default value is 75.
The type must be -1 or an integer in the range of 1 to 2073600.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-UdfsAllowed
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies that user-defined functions can be called by workbooks loaded from the trusted file ___location that is specified in Address.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-WarnOnDataRefresh
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies that a warning is displayed to the user on the first refresh of data for the workbook. The default value is True.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-WhatIf
Applicable: SharePoint Server 2010, SharePoint Server 2013
Displays a message that describes the effect of the command instead of executing the command.
For more information, type the following command: get-help about_commonparameters
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | wi |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-WorkbookSizeMax
Applicable: SharePoint Server 2010, SharePoint Server 2013
Specifies the maximum size, in megabytes, of a workbook that can be loaded. The default value is 10.
The type must be an integer value in the range of 1 to 2000.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable, -ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.