How to: Trust Workbook Locations Using Script

The workbooks that you want to access must be placed in a trusted location. If not, the calls to open the workbook will fail.

You can add a URL to the trusted location list by using the SharePoint 3.0 Central Administration page. Alternatively, you can use script to create a batch file.

For more information about how to trust a location using the SharePoint 3.0 Central Administration page, see How to: Trust a Location.

The following example shows how to add a location to the trusted location list.

Adding a URL or Site to the Trusted Location List

To create a batch file

1. Copy and paste the following syntax into Notepad:

   @ECHO off
   REM
   REM Script to add a URL or site to the trusted location list
   REM Last Updated: 11/2/05
   
   if "%1" == "/?" GOTO Help
   if "%1" == "" GOTO Help
   
   SET LocationType=SharePoint
   for /f "usebackq skip=3" %%i in (`%windir%\system32\cscript /nologo %windir%\system32\iisvdir.vbs /query "Office Web Services"`) do set variable=%%i
   set sspname=%variable:~1%
   
   :Arguments
   if "%2" == "Unc" SET LocationType=Unc
   if "%2" == "UNC" SET LocationType=Unc
   if "%2" == "unc" SET LocationType=Unc
   
   if EXIST "%commonprogramfiles%\Microsoft Shared Debug\web server extensions\12\BIN\stsadm.exe" (
        SET STSADM="%commonprogramfiles%\Microsoft Shared Debug\web server extensions\12\BIN\stsadm.exe"
   )
   
   if EXIST "%commonprogramfiles%\Microsoft Shared\web server extensions\12\BIN\stsadm.exe" (
        SET STSADM="%commonprogramfiles%\Microsoft Shared\web server extensions\12\BIN\stsadm.exe"
   )
   
   if NOT EXIST %STSADM% (
       ECHO Error:  STSADM.EXE cannot be found in your system.
       GOTO Error
       )
   ECHO.
   ECHO Adding %1 to the Trusted Locations list ...
   ECHO Type = %LocationType%
   ECHO SSP Name = %sspname%
   %STSADM% -o add-ecsfiletrustedlocation -ssp %sspname% -location %1 -includechildren true -locationtype %LocationType% -allowexternaldata dclandembedded 
   -warnondatarefresh true -abortonrefreshonopenfail false
   if NOT %ERRORLEVEL% == 0 (
           ECHO Error:  Error running stsAdm
           GOTO Error
           )
   ECHO.
   ECHO ********************************
   ECHO %1 has been added
   ECHO ********************************
   ECHO.
   PAUSE
   GOTO End
   
   :Error
   ECHO.
   ECHO ********************************
   ECHO Failed!
   ECHO ********************************
   ECHO.
   PAUSE
   GOTO End
   
   :Help
   ECHO.
   ECHO The command line syntax:
   ECHO    AddToTrusted http://<servername>/<customsite>
   ECHO    AddToTrusted \\<Servername>\<Foldername> unc
   ECHO.
   ECHO Examples:
   ECHO    C:\AddToTrusted http://<servername>/<customsite>
   ECHO    C:\AddToTrusted \\<Servername>\<Foldername> unc
   PAUSE
   GOTO End
   :End
   

2. Save the file as AddToTrusted.bat.

To add an HTTP site to a trusted location list

1. Start the command prompt console.

2. Click Start, and then click Run.

3. In the Open field box, type cmd. The command prompt console appears.

4. Use the cd command to navigate to the folder where you have saved AddToTrusted.bat. For example:

   cd c:\<SampleBatchFiles>
   

5. Type AddToTrusted http://<servername>/<customsite>.

6. If the operation is successful, you will see one of the following messages:

   Adding http://<servername>/<customsite> to the Trusted Locations list ...
   Type = SharePoint
   Operation completed successfully.
   

****************************** http:/// has been added ****************************** Press any key to continue . . .

7. Press any key to continue.

To add a UNC-based network file share to the trusted location list

1. Start the command prompt console.

2. Click Start, and then click Run.

3. In the Open field box, type cmd. The command prompt console appears.

4. Use the cd command to navigate to the folder where you have saved AddToTrusted.bat. For example:

   cd c:\<SampleBatchFiles>
   

5. Type AddToTrusted \\<Servername>\<Foldername> unc

6. If the operation is successful, you will see one of the following messages:

   Adding \\<Servername>\<Foldername> to the Trusted Locations list ...
   Type = Unc
   Operation completed successfully.
   

****************************** \<Foldername> has been added ****************************** Press any key to continue . . .

7. Press any key to continue.

Security

You need administrator rights to add a location to the trusted location list.

See Also

Tasks

Step 3: Accessing the Web Service
Walkthrough: Developing a Custom Application Using Excel Web Services
Step 3: Deploying and Enabling UDFs

Concepts

Excel Services Alerts
Excel Services Known Issues and Tips
Excel Services Best Practices