PoshCode Archive  Artifact [3094f66e22]

Artifact 3094f66e2231732e4247988c684dd64662eada5223fdd2cd2d984f29c0587c9d:

• File Modify-Excel.ps1 — part of check-in [7af9fa78b9] at 2018-06-10 13:24:35 on branch trunk — Powershell to modify Excel (user: Trevor size: 1604)

# encoding: ascii
# api: powershell
# title: Modify Excel
# description: Powershell to modify Excel
# version: 0.1
# type: function
# author: Trevor
# license: CC0
# function: Release-Ref
# x-poshcode-id: 3376
# x-archived: 2012-12-29T04:17:29
# x-published: 2012-04-19T19:13:00
#
#

# ----------------------------------------------------- 
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)