Get the SSL Certs by Web Site

I need to check several Web servers for sites that might have an expiring certification.
This pulls the information and adds it to an Excel Spreadsheet.
Note that this only works on 2008 servers.

$date = Get-Date
$rDate = (Get-Date -format "MM-dd-yyyy")
$strPath = "C:\Scripts\PS1\serverinfo-$rDate.xlsx" 
$xl = New-Object -comobject Excel.Application 
$xl.Visible = $True
$xl.DisplayAlerts = $False

$wb = $a.Workbooks.Add() 
$ws = $b.Worksheets.Item(1) 
$ws.Name = $rDate
$ws.Cells.Item(1,1) = "Machine Name" 
$ws.Cells.Item(1,2) = "FriendlyName" 
$ws.Cells.Item(1,3) = "NotAfter" 
$ws.Cells.Item(1,4) = "Issuer" 

$xRow = 2
$cert = dir IIS:\SslBindings | ? {$_.Port -eq 443} | Select *
foreach ($i in $cert) { 
$x = $i.thumbprint
$y = get-item cert:\LocalMachine\My\$x
$FN = $y.FriendlyName
$NA = $y.NotAfter
$IS = $y.Issuer
$ws.Cells.Item($xRow,1) =$comp 
$ws.Cells.Item($xRow,2) =$FN 
$ws.Cells.Item($xRow,3) =$NA 
$ws.Cells.Item($xRow,4) =$IS 
$xRow++
}

This version pulls the info and puts it into a Tab delimited file. I had to run this on each individule server
due to some restriction in my enviroment.

$comp = $env:computername
$strPath = "C:\Scripts\PS1\Certificates\$comp-CertInfo.csv"  # <-- You might want to point this at a share
$date = Get-Date
$rDate = (Get-Date -format "MM-dd-yyyy")
IF(Test-Path $strPath) { Remove-Item $strPath }
Add-Content -Path $strPath "Certificate Inventory `t$rDate`n"
Add-Content -Path $strPath "Machine Name`tFriendlyName `tNotAfter `tIssuer"

$OS = Get-WmiObject Win32_OperatingSystem
$osVer = $os.version
If ($osVer -like "6*") {
$cert = dir IIS:\SslBindings | ? {$_.Port -eq 443} | Select *
foreach ($i in $cert) { 
$x = $i.thumbprint
$y = get-item cert:\LocalMachine\My\$x
$FN = $y.FriendlyName
$NA = $y.NotAfter
$IS = $y.Issuer
Add-Content -Path $strPath "$comp `t$FN `t$NA `t$IS `n";
	}
}
Notepad $strPath

Finding Group members in AD

This is a simple script that will pull up the names of the members of a given group in AD. I use Excell to collect all the names.

 

$group = Read-Host "Enter group name to find"
$row = 2
$as = Get-ADGroup $group | Get-ADGroupMember

$xlSummaryAbove = 0 
$xlSortValues = $xlPinYin = 1 
$xlAscending = 1 
$xlDescending = 2
$xl = New-Object -comobject excel.application 
$xl.Visible = $true 
$wb = $xl.Workbooks.Add() 
$ws = $wb.Worksheets.Item(1) 
$row = 2
 $ws.Cells.Item(1,1) = 'First Name'
 $ws.Cells.Item(1,2) = 'Last Name'
 $ws.Cells.Item(1,3) = 'EMail Address'
 $ws.Cells.Item(1,4) = 'samAccountName'
 $range = $ws.range('A1:D1')
 $range.font.bold = 'true' 
foreach ($item in $as) { 
$name = Get-ADUser $item.samAccountName
 $ws.Cells.Item($row,1) = $name.GivenName
 $ws.Cells.Item($row,2) = $name.surname
 $ws.Cells.Item($row,3) = $item.name
 $ws.Cells.Item($row,4) = $name.samAccountName 
 $row++
  }

# one-column sort --> works 
$range1 = $ws.range('A2:D2500')
$range2 = $ws.range('B2')
[void]$range1.sort($range2, $xlAscending)
[void]$range1.entireColumn.Autofit()

Finding a File Type across several machines

I did this as one of my very first Powershell scripts and I noticed that it did no work as well as I liked so here is another attempt.
This one serches AD for a list of Servers and then looks for MDF ( SQL DB files ) on the D:, E: and F: drives.

#
function Get-Servers {
	$strCategory = "computer"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")

$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
    {$objComputer = $objResult.Properties; $objComputer.name}
}

function Get-MDF{ 
	Param(    
	[string] $srv   
	)  
	
	Get-ChildItem -path ("\\" + $srv) -Recurse -Filter "*.mdf" -ea continue |
		foreach { 
	       $ws.Cells.Item($row, 3) = $_.FullName 
                $ws.Cells.Item($row, 4) = $_.Length       
                $ws.Cells.Item($row, 5) = $_.LastAccessTime    
                $ws.Cells.Item($row, 6) = $_.CreationTime
                $ws.Cells.EntireColumn.AutoFit()
                $row++ }
				}
	
$xlCellTypeLastCell = 11	
$a  = Get-Date -format g
$row = 2 
$xl = New-Object -c excel.application 
$xl.visible = $True
$xl.DisplayAlerts = False
 
$wb = $xl.workbooks.add() 
$ws = $wb.sheets.item(1) 
$ws.Range("A1:T1").Font.Bold = $true 
$ws.Range("A:A").Font.Bold = $True 
$ws.Cells.Item(1, 1) = "Computer Name"
$ws.Cells.Item(1, 2) = "Date Run: "  + $a
$ws.Cells.Item(1, 3) = "FullName"
$ws.Cells.Item(1, 4) = "Length"    
$ws.Cells.Item(1, 5) = "LastAccessTime"    
$ws.Cells.Item(1, 6) = "CreationTime"

# You could say $computerlist = gc "c:\temp\servers.txt"
# you will need a list of computers, one computer name per line
# 
$computerlist = Get-Servers
 foreach ($srv in $computerlist) 
 { 
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row + 1
$ws.Cells.Item($row, 1) = $srv
$response = Get-WmiObject -query "Select * From Win32_PingStatus Where Address = '$srv'" 
  
  if( ($response -eq $null) -or ($response.StatusCode -ne 0)) { 
    $ws.Cells.Item($row, 2).Font.ColorIndex = 3 
    $ws.Cells.Item($row, 2) = "Does Not Ping"
    $row++ 
    } else { if ($response.TimeToLive -le 64) 
       { 
      $ws.Cells.Item($row, 2).Font.ColorIndex = 5
      $ws.Cells.Item($row, 2) = "probably is a Unix host"
      $row++
       } Else { 
      # Change the path and the extension. If you want all files, remove the -include *.???
      # This might be faster get-childitem  -path \$srvc$ -filter "*.mdb" -r -ea continue |
    	$string = ""
		if(Test-Path \\$srv\D$){
		$srvD = ($srv + "\D$")
				Get-MDF $srvD }
		Else { $string =  "No D: Drive"
		$ws.Cells.Item($row, 2) = $string
			$ws.Cells.Item($row, 2).Font.ColorIndex = 5
		}		
		If (Test-Path \\$srv\E$) { 
			$srvE = ($srv + "\E$")	
		Get-MDF $srvE }
		Else { $string = $string + " No E: Drive"
		$ws.Cells.Item($row, 2) = $string
		$ws.Cells.Item($row, 2).Font.ColorIndex = 5
		}
				
		If (Test-Path \\$srv\F$) { 
		$srvF = ($srv + "\F$")
				Get-MDF $srvF }
		Else { $string = $string + " No F: Drive" 
		$ws.Cells.Item($row, 2) = $string
		$ws.Cells.Item($row, 2).Font.ColorIndex = 5
		}
		}
			$ws.Cells.EntireColumn.AutoFit()
			$row++
  }
 }
 $xlWorkbookNormal = -4143 
$wb.SaveAs("C:\Scripts\find_MDFfiles.xlsx", $xlWorkbookNormal )
# close and release resources 
$wb.close($false) 
$xl.quit() 
spps -n excel # this will shut down all instances of Excel, so be carefull.
 
#—- End Script ————–>

Building Excel Reports with PowerShell

I found this on;
Blog Name: The Lonely Administrator
Blog URL: http://jdhitsolutions.com/blog
“Building Excel Reports with PowerShell”

I have yet to figure out how to do a Ping Back so I just copy and paste the whole thing.
Some of the ways he does things are not the way I would do them, but the results are just what you would want.
This script collects Hard Disk usage and displays the results in an Excel file complete with a Graph.
It also puts each computer on your list on its own worksheet.
Lots of good stuff here and much of it can be used for other purposes.

cmdletbinding()] # Coment this and the next line to do just the machine you are running the script on
Param([string[]]$computername=$env:computername) # This will do a list of machines
# $computername=$env:computername # Uncomment to do just the machine you are running the script on
#I hope it goes without saying that Excel needs to be installed
Write-Verbose "Creating Excel application" 
$xl=New-Object -ComObject "Excel.Application" 
$wb=$xl.Workbooks.Add()

#we'll need some constants
$xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

Foreach ($computer in $computername) {
    #get disk data
    Write-Verbose "Getting disk data from $computer" 
    $disks=Get-WmiObject -Class Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"

    Write-Verbose "Adding Worksheet" 
    $ws=$wb.Worksheets.Add()

    $cells=$ws.Cells

    $cells.item(1,1)="Disk Drive Report"

    #define some variables to control navigation
    $row=3
    $col=1

    #insert column headings
    Write-Verbose "Adding drive headings" 

    "Drive","SizeGB","FreespaceGB","UsedGB","%Free","%Used" | foreach {
        $cells.item($row,$col)=$_
        $cells.item($row,$col).font.bold=$True
        $col++
    }

    Write-Verbose "Adding drive data" 
    foreach ($drive in $disks) {
        $row++
        $col=1
        $cells.item($Row,$col)=$drive.DeviceID
        $col++
        $cells.item($Row,$col)=$drive.Size/1GB
        $cells.item($Row,$col).NumberFormat="0"
        $col++
        $cells.item($Row,$col)=$drive.Freespace/1GB
        $cells.item($Row,$col).NumberFormat="0.00"
        $col++
        $cells.item($Row,$col)=($drive.Size - $drive.Freespace)/1GB
        $cells.item($Row,$col).NumberFormat="0.00"
        $col++
        $cells.item($Row,$col)=($drive.Freespace/$drive.size)
        $cells.item($Row,$col).NumberFormat="0.00%"
        $col++
        $cells.item($Row,$col)=($drive.Size - $drive.Freespace) / $drive.size
        $cells.item($Row,$col).NumberFormat="0.00%"
    }

    Write-Verbose "Adding some style" 

    #add some style
    $range=$ws.range("A1")
    $range.Style="Title"
    #or set it like this
    $ws.Range("A3:F3").Style = "Heading 2"

    #adjust some column widths
    Write-Verbose "Adjusting column widths" 
    $ws.columns.item("C:C").columnwidth=15
    $ws.columns.item("D:F").columnwidth=10.5
    $ws.columns.item("B:B").EntireColumn.AutoFit() | out-null

    #add some conditional formatting
    Write-Verbose "Adding conditional formatting" 

    #get the starting cell
    $start=$ws.range("F4")
    #get the last cell
    $Selection=$ws.Range($start,$start.End($xlDirection::xlDown))
    #add the icon set
    $Selection.FormatConditions.AddIconSetCondition() | Out-Null
    $Selection.FormatConditions.item($($Selection.FormatConditions.Count)).SetFirstPriority()
    $Selection.FormatConditions.item(1).ReverseOrder = $True
    $Selection.FormatConditions.item(1).ShowIconOnly = $False
    $Selection.FormatConditions.item(1).IconSet = $xlIconSet::xl3TrafficLights1
    $Selection.FormatConditions.item(1).IconCriteria.Item(2).Type=$xlConditionValues::xlConditionValueNumber
    $Selection.FormatConditions.item(1).IconCriteria.Item(2).Value=0.8
    $Selection.FormatConditions.item(1).IconCriteria.Item(2).Operator=7
    $Selection.FormatConditions.item(1).IconCriteria.Item(3).Type=$xlConditionValues::xlConditionValueNumber
    $Selection.FormatConditions.item(1).IconCriteria.Item(3).Value=0.9
    $Selection.FormatConditions.item(1).IconCriteria.Item(3).Operator=7
    
    #insert a graph
    Write-Verbose "Creating a graph" 
    $chart=$ws.Shapes.AddChart().Chart
    $chart.chartType=$xlChart::xlBarClustered

    $start=$ws.range("A3")
    #get the last cell
    $Y=$ws.Range($start,$start.End($xlDirection::xlDown))
    $start=$ws.range("F3")
    #get the last cell
    $X=$ws.Range($start,$start.End($xlDirection::xlDown))

    $chartdata=$ws.Range("A$($Y.item(1).Row):A$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
    $chart.SetSourceData($chartdata)

    #add labels
    $chart.seriesCollection(1).Select() | Out-Null
    $chart.SeriesCollection(1).ApplyDataLabels() | out-Null
    #modify the chart title
    $chart.ChartTitle.Text = "Utilization"
    Write-Verbose "Repositioning graph" 
    $ws.shapes.item("Chart 1").top=40
    $ws.shapes.item("Chart 1").left=400

    Write-Verbose "Renaming the worksheet"  
    #rename the worksheet
    $name=$disks[0].SystemName
    $ws.Name=$name
    #select A1
    $ws.Range("A1").Select() | Out-Null
} #foreach

#delete extra sheets
Write-Verbose "Deleting extra worksheets"
$xl.worksheets.Item("Sheet1").Delete()
$xl.Worksheets.Item("Sheet2").Delete()
$xl.Worksheets.Item("Sheet3").Delete()

#make Excel visible 
$xl.Visible=$True

$filepath=Read-Host "Enter a path and filename to save the file"

if ($filepath) {
    Write-Verbose "Saving file to $filepath" 
    $wb.SaveAs($filepath)
    $xl.displayAlerts=$False
    $wb.Close()
    $xl.Quit()
}

#end of script
###### End Posh Script ######## 

How do I set up Auto Filters in Excel?

#How do I set up Auto Filters in Excel?

#This function sets up a spreadsheet and then sets Auto filters
Someone asked how to select both Jack and Elizebeth.
$xl.Selection.AutoFilter 2, Jack, $xlAnd, Elizabeth #Jack & Elizabeth items only

Thanks to an anonymous reader, we can now do a more than two.

$xlFilterValues = 7 # found in MS documentation
$filterList = “Jack”,”Elizabeth” # array
$xl.Selection.AutoFilter(2, $filterList ,$xlFilterValues)

###### Start Posh Script ########
 Function XLAutoFilter { 
 $xlTop10Items = 3
 $xlTop10Percent = 5
 $xlBottom10Percent = 6
 $xlBottom10Items = 4
 $xlAnd = 1
 $xlOr = 2
 $xlNormal = -4143
 $xlPasteValues = -4163 # Values only, not formulas 
 $xlCellTypeLastCell = 11 # to find last used cell
 $xl = New-Object #NAME? Excel.Application 
 $xl.Visible = $true
 $xl.DisplayAlerts = $False
 $wb = $xl.Workbooks.Add()
 $ws = $wb.Worksheets.Item(1)
 $ws = $wb.Sheets.Add()
 $ws = $wb.Sheets.Add()
 $ws1 = $wb.worksheets | where {$_.name #NAME? Sheet1} #<------- Selects sheet 1
 $ws2 = $wb.worksheets | where {$_.name #NAME? Sheet2} #<------- Selects sheet 2
 $ws3 = $wb.worksheets | where {$_.name #NAME? Sheet3} #<------- Selects sheet 3
 $ws4 = $wb.worksheets | where {$_.name #NAME? Sheet4} #<------- Selects sheet 4
 $ws5 = $wb.worksheets | where {$_.name #NAME? Sheet5} #<------- Selects sheet 5
 $ws1.Tab.ColorIndex = 8
 $ws2.Tab.ColorIndex = 7
 $ws3.Tab.ColorIndex = 6
 $ws4.Tab.ColorIndex = 5
 $ws5.Tab.ColorIndex = 4
 $ws1.name = Detail
 $ws2.name = JackOnly
 $ws3.name = Top2
 $ws4.name = LowestHighest
 $ws5.name = Top25Percent
 $ws1.cells.Item(1,1) = Amount
 $ws1.cells.Item(1,2) = SalesPerson
 $ws1.cells.Item(2,1) = 1
 $ws1.cells.Item(2,2) = Jack
 $ws1.cells.Item(3,1) = 2
 $ws1.cells.Item(3,2) = Elizabeth
 $ws1.cells.Item(4,1) = 3
 $ws1.cells.Item(4,2) = Renee
 $ws1.cells.Item(5,1) = 4
 $ws1.cells.Item(5,2) = Elizabeth
 $ws1.cells.Item(6,1) = 5
 $ws1.cells.Item(6,2) = Jack
 $ws1.cells.Item(7,1) = 6
 $ws1.cells.Item(7,2) = Renee
 $ws1.cells.Item(8,1) = 7
 $ws1.cells.Item(8,2) = Elizabeth
 $ws1.cells.Item(9,1) = 8
 $ws1.cells.Item(9,2) = Jack
 $ws1.cells.Item(10,1) = 9
 $ws1.cells.Item(10,2) = Renee
 $ws1.cells.Item(11,1) = 10
 $ws1.cells.Item(11,2) = Jack
 $ws1.cells.Item(12,1) = 11
 $ws1.cells.Item(12,2) = Jack
 $ws1.cells.Item(13,1) = 12
 $ws1.cells.Item(13,2) = Elizabeth
 $ws1.cells.Item(14,1) = 13
 $ws1.cells.Item(14,2) = Renee
 $ws1.cells.Item(15,1) = 14
 $ws1.cells.Item(15,2) = Elizabeth
 $ws1.cells.Item(16,1) = 15
 $ws1.cells.Item(16,2) = Jack
 $ws1.cells.Item(17,1) = 16
 $ws1.cells.Item(17,2) = Renee
 $ws1.cells.Item(18,1) = 17
 $ws1.cells.Item(18,2) = Elizabeth
 $ws1.cells.Item(19,1) = 18
 $ws1.cells.Item(19,2) = Jack
 $ws1.cells.Item(20,1) = 19
 $ws1.cells.Item(20,2) = Renee
 $ws1.cells.Item(21,1) = 20
 $ws1.cells.Item(21,2) = Renee
 $used = $ws1.usedRange
 $lastCell = $used.SpecialCells($xlCellTypeLastCell)
 $lastrow = $lastCell.row
 $r = $ws1.Range("A1:B$lastrow")
 $ws1.Range("A1:B$lastrow").Copy()  
 $ws2.Select() | Out-Null
 [void]$ws2.Range("A1").PasteSpecial(-4163)  
 $ws3.Select() | Out-Null
 [void]$ws3.Range("A1").PasteSpecial(-4163)  
 $ws4.Select() | Out-Null
 [void]$ws4.Range("A1").PasteSpecial(-4163)  
 $ws5.Select() | Out-Null
 [void]$ws5.Range("A1").PasteSpecial(-4163)  
 #  
 $ws5.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 $xl.Range("A1").Select() | Out-Null
 $xl.Selection.AutoFilter(1, 2, $xlTop10Items) #top 2 
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 #$xl.Selection.AutoFilter 1, 25, $$xlTop10Percent #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $$xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 #$xl.Selection.AutoFilter 1, >19,XLOr , <2 #Lowest and Highest 
 #$xl.Selection.AutoFilter 2, Jack #Jack items only
 $ws5.cells.Item.EntireColumn.AutoFit  
 $ws2.Select()  
 $ws2.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 #$xl.Selection.AutoFilter 1, 2, $xlTop10Items #top 2
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 #$xl.Selection.AutoFilter 1, 25, $xlTop10Percent #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 #$xl.Selection.AutoFilter 1, >19,XLOr , <2 #Lowest and Highest 
 $xl.Selection.AutoFilter(2, Jack) #Jack items only 
 $ws2.cells.Item.EntireColumn.AutoFit  
 $ws4.Select()  
 $ws4.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 #$xl.Selection.AutoFilter 1, 2, $xlTop10Items #top 2
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 #$xl.Selection.AutoFilter 1, 25, $xlTop10Percent #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 $xl.Selection.AutoFilter(1, >19,$xlOr , <2) #Lowest and Highest  
 #$xl.Selection.AutoFilter 2, Jack #Jack items only
 $ws4.cells.Item.EntireColumn.AutoFit  
 # Top25Percent 
 $ws5.Select()  
 $ws5.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 #$xl.Selection.AutoFilter 1, 2, $xlTop10Items #top 2
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 $xl.Range("A1").Select() | Out-Null
 $xl.Selection.AutoFilter(1,"25",$xlTop10Percent) #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 #$xl.Selection.AutoFilter 1, >19,XLOr , <2 #Lowest and Highest 
 #$xl.Selection.AutoFilter 2, Jack, $xlAnd, Elizabeth #Jack & Elizabeth items only
 $ws5.cells.Item.EntireColumn.AutoFit  
 } # End Function
############# End POSH Script ###############

Get rid of the Excel Com Object, Once and for All

From Technet, November of 2007.

When you run $x.Quit() The Excel com object is left running. This can be a problem.
If you are writing and testing a script, you could wind up with 10-15 or more instance of Excel running in the background. Here is one way of solving that problem.

$x = New-Object -com Excel.Application
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

How do I record an Error in Excell

###### Start Posh Script ######## 

<# ==============================================================================================
Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2011
NAME: ErrorView.ps1
AUTHOR: OldDog
DATE  : 5/18/2012
COMMENT: Ping does not return an Error
 Use Test-Connection instead
==============================================================================================
#>

If ($Error) {
               $ErrorView = "CategoryView"
                $y = $error | select Exception
                $e = $y -split (":")
                $x = $e[2]
                $ws1.Cells.Item($row, 3) = $x
                $ws1.rows.Item($row).Font.ColorIndex = "3"
                $row++
                $Error.Clear()
                $range = $ws1.usedRange
                [void]$range.entireColumn.Autofit()
                $ErrorView = $null
}

Finding a Character in Excel using [Regex]

I have a list of over 500 new cmdlets for Powershell and SharePoint. I wanted to put this list in an Excel spreadsheet for my own nefarious purposes. When I did that the  Cmdlet name somehow got a number added to the end of it. Like  so:

Backup-SPFarm12

Backup-SPSite13

Clear-SPLogLevel14

Clear-SPMetadataWebServicePartitionData15

I was wondering if there is a way, in Powershell to remove these numbers from the name. I tried a few things, with no luck.

Then I did some research online and found that I was over thinking the problem. I just need to split on the first number. But how?

 $Rng1 = $ws1.Cells.Item($i, 2).value()
    $a = $Rng1
    $b = [regex]::split($a,("\d"))  # (“\d”) is a number, so it splits on the first number in the string
    $b[0] # This is everything to the left of the first number
    $ws1.Cells.Item($i, 2).value() = $b[0] # This overwrites the contents of the cell with everything BUT the number

Here is the whole script:

 function Release-Ref ($ref) {
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
} 

$XLNormal = -4143
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2
$xlYes = 1
$xlCellTypeLastCell = 11 

#Create an instance of the Excel.Application
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.open("C:\SharePoint Server 2010 PowerShell cmdlets.xlsx")
$ws = $wb.Worksheets.Item(1)
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"}
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row + 1
For($i = 8; $i -lt $row; $i++){
   Write-Progress -Activity "Searching $row #'s..." `
     -PercentComplete ($i/$row*100) -CurrentOperation `
     "$i of $Row to go" -Status "Please wait."     
   $Rng1 = $ws1.Cells.Item($i, 2).value()
    $a = $Rng1
    $b = [regex]::split($a,("\d"))
    $b[0]
    $ws1.Cells.Item($i, 2).value() = $b[0]
} 
[void]$ws1.cells.entireColumn.Autofit() 
$wb.Save()
Release-Ref $ws
Release-Ref $wb
$xl.Quit()
Release-Ref $xl

Powershell and Excel Merging Cells

How do I merge cells in Column A,B and C in excel?

Like this:

$ws1.Cells.Item($row,1) = "Note: Users listed are either in the BuiltIn\Administrators group or are in a group that is in the BuiltIn\Administrators group."

 $MergeCells = $ws1.Range("A${Row}:C$row")
 $MergeCells.Select()
 $MergeCells.MergeCells = $true

Using the BorderAround Method in Excel

I was messing around with the borders function in Excel and I noticed that there is also a BorderAround function.

1. Here is the Borders Function:

$xl = new-object -comobject Excel.Application
# make Excel visible
$xl.visible = $true
$xl.DisplayAlerts = $False
# open a workbook
$wb = $xl.workbooks.add()
# Get sheet1
$ws1 = $wb.worksheets | where {$_.name -eq "sheet1"}

$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2
$xlMedium = -4138
$xlThick = 4

$selection = $ws1.usedRange
[void]$selection.select()
$selection.Borders.Item($xlEdgeLeft).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeLeft).ColorIndex = $xlAutomatic
$selection.Borders.Item($xlEdgeLeft).Color = 1
$selection.Borders.Item($xlEdgeLeft).Weight = $xlMedium
$selection.Borders.Item($xlEdgeTop).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeBottom).LineStyle = $xlContinuous
$selection.Borders.Item($xlEdgeRight).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideVertical).LineStyle = $xlContinuous
$selection.Borders.Item($xlInsideHorizontal).LineStyle = $xlContinuous

This puts borders around all the cells in the used range.

I tried using the BorderAround Function like this:

$selection = $ws1.usedRange
[void]$selection.select()

$selection.BorderAround

And I got this:

MemberType          : Method
OverloadDefinitions : {Variant BorderAround (Variant, XlBorderWeight, XlColorIndex, Variant)}
TypeNameOfValue     : System.Management.Automation.PSMethod
Value               : Variant BorderAround (Variant, XlBorderWeight, XlColorIndex, Variant)
Name                : BorderAround
IsInstance          : True

So I plugged in some numbers here and there and wound up with this:

$selection.BorderAround(1,4,3); # 1 = Continuous 4 = thick and 3 = Red

Which puts a thick Red border around the whole selection.

So, my question was, What does this mean:
Specifies the line style for the border.

Name	Value	Description
xlContinuous	1	Continuous line.
xlDash	-4115	Dashed line.
xlDashDot	4	Alternating dashes and dots.
xlDashDotDot	5	Dash followed by two dots.
xlDot	-4118	Dotted line.
xlDouble	-4119	Double line.
xlLineStyleNone	-4142	No line.
xlSlantDashDot	13	Slanted dashes.

OverloadDefinitions : {Variant BorderAround (Variant, XlBorderWeight, XlColorIndex,Variant)}

And I was directed to MSDN (http://msdn.microsoft.com/en-us/library/bb209714(office.12).aspx)

Name	Required/Optional	Data Type	Description
LineStyle	Optional	Variant	One of the constants of XlLineStyle specifying the line style for the border.
Weight	Optional	XlBorderWeight	The border weight.
ColorIndex	Optional	XlColorIndex	The border color, as an index into the current color palette or as a XlColorIndex constant.
Color	Optional	Variant	The border color, as an RGB value.

BTW, as far as I know Powershell does not do RGB, so the last variant is not used.