PoshCode Archive  Artifact [0d9bc736e4]

Artifact 0d9bc736e4b199ae7042f214ee4f8e6b18f3938e6f7ade852ae9ec18e4851d4a:

  • File Release-Ref.ps1 — part of check-in [d8cd74028d] at 2018-06-10 13:24:34 on branch trunk — Powershell to modify Excel (user: unknown size: 1586)

# encoding: ascii
# api: powershell
# title: 
# description: Powershell to modify Excel
# version: 0.1
# type: function
# license: CC0
# function: Release-Ref
# x-poshcode-id: 3375
# x-archived: 2012-12-29T04:38:10
#
#
[sourcecode language="css"]
##
# ----------------------------------------------------- 
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
} 
# ----------------------------------------------------- 
 
$objExcel = new-object -comobject excel.application  
$objExcel.Visible = $True  
$objWorkbook = $objExcel.Workbooks.Open("C:\Users\username\Desktop\scripts\groupmaps.xlsx") 

# write into cell B2 (column 2, line 2):
#$objWorkbook.ActiveSheet.Cells.Item(2,2)= "Test a Write"

# read cell content
#$content = $objWorkbook.ActiveSheet.Cells.Item(3,1).Text
#"Cell B2 content: $content"
$RowNum = 2

While ($objWorkbook.ActiveSheet.Cells.Item($RowNum, 1).Text -ne "") {
 
    
    
    $lusername = $objWorkbook.ActiveSheet.Cells.Item($RowNum,2).Text
    Import-Csv C:\Users\username\Desktop\scripts\usermaps.csv | foreach {
		if ($lusername -eq $_.ousername){
			#Write-Host Match $lusername $_.ousername $_.nusername
			$objWorkbook.ActiveSheet.Cells.Item($RowNum,4)= $_.nusername
			$objWorkbook.ActiveSheet.Cells.Item($RowNum,5)= $_.DisplayName
		}
	}
	           
    $RowNum++
}
 
 
#close 
$a = Release-Ref($objWorkbook) 
$a = Release-Ref($objExcel)  
[/sourcecode]