Home > Powershell, Scripting, Toolbox > Script of the day – writing to a cell in Excel

Script of the day – writing to a cell in Excel

Ever needed to inject info to a cell in an Excel spreadsheet – repeatedly . . . you can do so from Powershell . . like so:

Function write-excel ($ExcelFile, $WorkSheet, $Column, $Row, $Value) 
{
<#
	.SYNOPSIS
	 Write value(s) to Excel cells 
	.DESCRIPTION
	 No return - Updates an Excel Cell
	.PARAMETER  
		e.g. Specify the requested parameter, if not specified this will be prompted
	.EXAMPLE
		PS C:\> write-excel $ExcelFile $WorkSheet $Column $Row $Value
#>
	$File = (ls $ExcelFile).FullName
	# Open Excel
	$Excel = New-Object -Com Excel.Application
	
	# Prevent Overwrite and Macro Prompt
	$Excel.displayalerts=$False 

	# Open Template File 

	$WorkBook = $Excel.Workbooks.Open($File)
	$WorkSheet = $WorkBook.Worksheets.Item("$WorkSheet")
	$Worksheet.Cells.Item($Row,$Column) = $Value

	# Setting All Variables to Null

	$WorkBook.SaveAs($ExcelFile,1)
	$Excel.Quit()
 	$WorkBook = $Null
	$WorkSheet = $Null
	$Excel = $Null

	# Releasing Object Wrapper

	[GC]::Collect()
}

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: