Start and Stop Windows Services Using the CData Excel Add-In for PowerShell

Get useful data to system administrators and developers by executing Microsoft PowerShell scripts from the ribbon in Excel and get organized by enabling a spreadsheet as a destination for output. You can also use the Excel Add-in to start and stop services directly from Excel. Covered in this article are: How to Execute SQL in Excel that Pro

Write Output to a Spreadsheet: Create a Spreadsheet with the Results of a Script

Follow these steps to create a spreadsheet that lists the running services:

1. Define a connection. The CData PowerShell Scripts folder, located in the Public Documents folder, contains the example scripts for the Add-in. To be able to select scripts as database tables, set the Script Location property in the connection string to this folder.
2. To run the Service.ps1 script, click the From PowerShell button on the CData tab on the ribbon. This opens the Data Selection wizard. You can then organize the output of the PowerShell script into a spreadsheet. You can also execute SQL using the =CDataQUERY formula.
3. Execute the SQL query against the Service table to run the script:

Read Input from a Spreadsheet: Implement SQL in a Script and Inject Spreadsheet Data into a Script

When the Execute Query property in the connection string is set to False, the Add-in implements queries in the script. The example script Service.ps1 organizes information about services into a table, implements filtering with the PowerShell Where-Object, and supports updating a service's status.

• The param declaration injects the fields of a record into your PowerShell script:

  param(
    [String]$Id='',
    [String]$DisplayName='',
    [String]$Name='',
    [String]$Status='',
    [String]$ServiceName='',
    [String]$ServiceType='',
    [Boolean]$CanPauseAndContinue=0,
    [Boolean]$CanShutdown=0,
    [Boolean]$CanStop=0 ,
    [String]$_method='GET,POST,MERGE,DELETE')

• The query below gets information about system services:

  SELECT * FROM Service WHERE Status='Running'

  You can use the Add-In to execute SQL in Excel that provides input to a PowerShell script. The code below shows how you can use Select-Object, Sort-Object, and Where-Object to implement a SELECT query and a WHERE clause:

  if($_method -eq "GET"){
    $tempfilter = @()
  ...
  
  if($tempfilter -ne '') {
      $services = Get-Service | 
  	  Sort-Object DisplayName | 
  	    Select-Object DisplayName, Name, Status, ServiceName, ServiceType, CanPauseAndContinue, CanShutdown, CanStop | 
  		  Where-Object {iex ($tempfilter -join ' -AND ')}
  }

Start and Stop Services in Excel

The Add-in can also pass changes to the spreadsheet data into the PowerShell script -- for example, to implement an UPDATE command that can start and stop a service.

1. Retrieve the services from the Service table: on the CData tab, click the From PowerShell button to retrieve data.
2. Enter a new status to start or stop a service.
3. Click Update Rows to invoke your script.