How do I Update an Excel Spreadsheet from another Sheet.

I am working on a project to change several thousand server from Symantec AntiVirus to Symantec End Point Protection. We keep track of the servers in an Excel spreadsheet that I call “Master.xlsx” and the updates are in another Spreadsheet called “Update-Date.xlsx”. The trick is to update the Master once a week with those servers that have changed from SAV to SEP. And to limit the update to the first 14 columns. There may be better or more elegant ways to do this, but here is my solution:

#<---- Begin Powershell Script ------------------------------------------>
$d=Show-Inputbox -message "Enter Update Spredsheet Name" `
-title "Update File" -default "Update-Date.xlsx"
$e=Show-Inputbox -message "Enter the Update Tab Name" `
-title "Tab Name" -default "10-23-2010"
$xlCellTypeLastCell = 11
$path = "D:\SavToSep\MASTER.xlsx"

$xl = New-Object -ComObject excel.application
$xl.visible = $True
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.open("D:\SavToSep\MASTER.xlsx")
$ws = $wb.WorkSheets.item("Servers")
$wb1 = $xl.Workbooks.open("D:\SavToSep\$D.xlsx")
$ws1 = $wb1.WorkSheets.item($E)
$ws1.activate()

$Rng = $ws1.UsedRange.Cells
$row = $Rng.Rows.Count
Write-Host "Row: " $row

for ($i = 2; $i -le $row; $i++) {
$wb1.activate()
$ws1.activate()
$x = $ws1.Cells.Item($i, 1).Value()
Write-Host "i: " $i
$Range = $ws1.range("a${i}:n$i")
$Range.Copy()
$wb.activate()
$ws.activate()
$mainRng = $ws.usedRange
$mainRng.Select()
$objSearch = $mainRng.Find($x)

	If ($objSearch -eq $Null) {
	$used = $ws.usedRange
	$lastCell = $used.SpecialCells($xlCellTypeLastCell)
	$lastrow = $lastCell.row
	$NewRow = $lastrow + 1
	#[void]$ws.Cells.Item($NewRow,1).Paste()
	[void]$ws.Cells.Item($NewRow,1).PasteSpecial(-4122)
	[void]$ws.Cells.Item($NewRow,1).PasteSpecial(-4163)
	Write-Host "New Row: " "A"$NewRow

}Else {
        $objSearch.Select()
	$R = $objSearch.row
	Write-Host "R: " $R
	#[void]$ws.Cells.Item("A$R").Paste()
	[void]$ws.Range("A$R").PasteSpecial(-4122)
	[void]$ws.Range("A$R").PasteSpecial(-4163)
	}
$wb1.Save()
$wb.Save()

$rc=Show-Msgbox -message "Do you want to view the Spredsheet?" `
-icon "Question" -button "YesNoCancel" -title "Hey $env:username!!"

Switch ($rc) {
"Yes" {$xl.Visible = $True }
# close and release resources
"No" {$wb.close($false)
 $xl.quit()
 spps -n excel
 }
 "cancel" {"When in doubt, punt."}
}

# <------------- End Script ----------------------------------------------->;

Advertisements

2 thoughts on “How do I Update an Excel Spreadsheet from another Sheet.

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