The use of Excel can be largely automated using Powershell. This is especially useful if you want to repeat office tasks frequently and don’t want to have to deal with the strange VBAScript syntax, if you want to document infrastructure data or if you want to process information from the non-Mircrosoft world in Microsoft Office. Beyond that, of course, numerous other use cases are conceivable.

Traversing an Excel workbook

The following script traverses a desired worksheet of an Excel Workbook and outputs each row whose first cell contains the string „CLAUS“.

$Filepath="C:\YOURPATH.xlsx"
	$Excel = New-Object -ComObject excel.application
	$Excel.Visible = $false
	$Workbook = $Excel.Workbooks.Open($Filepath)
	$Table =$workbook.Worksheets.Item("YOURTABLE")

	$Zeile=1
	$Spalte=1

	do {
     if ($Table.Cells.Item($Zeile,$Spalte).Text -eq 'CLAUS')
      {	
	    echo $Table.Cells.Item($Zeile,$Spalte).Text
      }
     
     $Zeile++
   	   }
  	while($Table.Cells.Item($Zeile,$Spalte).Text.Length -gt 0)

Changing the value of a cell

By means of the following script the content of a table cell can be changed.

$Filepath="C:\YOURPATH.xlsx"
	$Excel = New-Object -ComObject excel.application
	$Excel.Visible = $false
	$Workbook = $Excel.Workbooks.Open($Filepath)
	$Table =$workbook.Worksheets.Item("YOURTABLE")

	$Zeile=20
	$Spalte=1

	$Table.Cells.Item($Zeile,$Spalte).Text="Vornamen"