#region Browse for File Function

	Function Browsefor-File {
	PARAM($FilterIndex)
	[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") ##| Out-Null
	$dialog = New-Object System.Windows.Forms.OpenFileDialog
	$dialog.Filter = 'CSVFiles|*.csv|TXT Files|*.txt|Excel Files|*.xl*|VBS Files|*.vbs|Powershell Files|*.ps1|All Files|*.*'
	$dialog.FilterIndex = $FilterIndex
	$dialog.InitialDirectory = $ScriptPath 
	$dialog.Multiselect = $false
	$dialog.RestoreDirectory = $true
	$dialog.Title = "You Must Select a CSV File of Computer List"
	$dialog.ValidateNames = $true
	Write-Host IF Show Dialog Browse Box Does Not Run with Powershell - Update for WIN7 - Windows6.1-KB2819745-x64-MultiPkg.msu
	$dialog.ShowDialog()				##This browse does not comeup with just powershell / Only in GUI
	$dialog.FileName
	$Global:Browsefile = $dialog.FileName
	$BrowsePath =$Browsefile.Trimend($Browsefile.Split("\")[-1])	#Trimming Split to Determine Path to BrowseFile
		If($Browsefile -eq ""){
		Write-Host -ForegroundColor Red EXITING No File Selected
		$wsh.popup("You Must Select a File",0,"Exiting")
		break
		}
		If($Browsefile -gt ""){
		$err = $wsh.popup("You Selected " + $Browsefile,2,"Script Will Continue in 5 Seconds",1)
		if ($err -eq 1){}		#$wsh.Popup("What did you Click OK for?",3,"You Selcted OK")}
		if ($err -eq 2){$wsh.Popup("This Script Has Been Cancelled",2,"You Selected CANCEL");Break}
		}
	}#End Function
	
#Endregion


<#               1 2             4 5            7
------ DDMMSSss: N 42°14'23.89"  E 40°6'41.4"  -1'
#>


#Region Beginning of Script

# - Beginning of Script
$wsh = new-object -comobject wscript.shell		#This is REQUIRED For Popup Messages and some other stuff.
$cr = "`r"		#Carriage Return ` Tick
$cr2 = "`r`r"		#Carriage Return ` Tick
#This works with GUI and Powershell, and Windows 7
$ScriptName = $MyInvocation.MyCommand.Name
$RunDirName = $MyInvocation.MyCommand.Path.Split("\")[-2] + "\" #Notice the "\" - Putting it up here, rather in script. This is needed for this script
$ScriptPath = $MyInvocation.MyCommand.Path.Trim($MyInvocation.MyCommand.Path.Split("\")[-1])	#The [-1] is the Last Item in an Array.
#$wsh.Popup("ScriptName:" + $cr + $scriptname + $cr + $cr + "ScriptPath:" + $cr + $scriptpath + $cr + $cr + "Run Directory Name:" + $cr + $rundirname,1,"Confirming Path Variables ")
#Endregion

$Count = 0 
$LoopCount = 0


# Browsing for file and Type
	BrowseFor-File 2
#Importing CSV File
	$list = Get-Content $browsefile


	$fdate = (Get-Date -Format "MM-dd-HHmmss")
	$Global:ExcelFile = $browsefile.TrimEnd($Browsefile.Split("\")[-1]) + $BrowseFile.Split("\")[-1].Replace(".txt","") + "_$fdate.xlsx"
	if (Test-Path $excelfile) {Remove-Item $excelfile -force}

#Creating Excel File Function We are creating the spreadsheet inlline with the script, not calling any function.
	#Create-Excel
	
	#Region Create Excel Sheet !!!  Creating excel Here and leaving it open, will not save the spreadsheet until the Script is finished.
		
			#Region Starting New  New SpreadSheet
				$objExcel = New-Object -ComObject Excel.Application
				$objExcel.visible = $True
				$objWB = $objExcel.WorkBooks.Add()
				$objSheet = $objWB.sheets | where {$_.name -eq 'Sheet1'}
				#$objSheet = $objWB.Worksheet.Item(1)
				$objSheet.Activate()
			#Endregion
			
			#Region Formatting Headers
			#A
			      $objSheet.Cells.Item(1,1) = "Group"
				$objExcel.columns.item("A:A").columnwidth=15
				$objExcel.columns.item("A:A").HorizontalAlignment= -4108
			#B	
				$objSheet.Cells.Item(1,2) = "Unit"
				$objExcel.columns.item("B:B").columnwidth=20
				$objExcel.columns.item("B:B").HorizontalAlignment= -4108
			#C
			      $objSheet.Cells.Item(1,3) = "DDMMmmm"
				$objExcel.columns.item("C:C").columnwidth=30
				$objExcel.columns.item("C:C").HorizontalAlignment= -4108
			#D
			      $objSheet.Cells.Item(1,4) = "DDMMSSss"
				$objExcel.columns.item("D:D").columnwidth=30
				$objExcel.columns.item("D:D").HorizontalAlignment= -4108
			#E
				$objSheet.Cells.Item(1,5) = "Elevation"
				$objExcel.columns.item("E:E").columnwidth=13
				$objExcel.columns.item("E:E").HorizontalAlignment= -4108

			#Endregion
			
			#Region Formatting The Spreadsheet
				#$objSheet.Range("A1:I1").HorizontalAlignment = -4108
				$objSheet.Range("A1:E1").Font.Size = 12
				$objSheet.Range("A1:E1").Wraptext = $False 
				$objSheet.Range("A1:E1").Font.Bold = $True
				$objSheet.Range("A1:E1").Interior.ColorIndex = 0
				$objSheet.Range("A1:E1").Font.ColorIndex = 1
			#Adding a Row to Freeze Panes
				$intRow = $objSheet.UsedRange.Rows.Count + 1
				$objSheet.Cells($intRow, 1).Value = "-"
				$objSheet.Cells($intRow, 2).Value = "-"
				$objSheet.Cells($intRow, 3).Value = "-"
				$objSheet.Cells($intRow, 4).Value = "-"
			#Freezing Panes
				$objExcel.Rows.Item("3:3").Select();$objExcel.Application.ActiveWindow.FreezePanes = $true
			#Endregion

			
			#Region Saving and Closing New Excel
				$objWB.SaveAs($excelfile)
				$objWB.Close()
				$objExcel.Application.Quit()
			#Endregion
			
	#>	
	#Endregion

	#Region Opening Excel File Again, After Creating New One.
		$objExcel = New-Object -ComObject Excel.Application
		$objExcel.visible = $true
		$objWb = $objExcel.WorkBooks.Open($ExcelFile, $True)
		$objSheet = $objExcel.ActiveWorkBook.WorkSheets("Sheet1")
	#Endregion 

	#Add a New Line to Spreadsheet
		$intRow = $objSheet.UsedRange.Rows.Count + 1

$primesw = [Diagnostics.Stopwatch]::StartNew()


#  -4108 Center
#  -4152 Right
#  -4131 Left

	Foreach($l in $List){
	
	$lt = ($primesw.elapsed.ToString()).SubString(6,4)
	If($lt -eq "10.0" -or $lt -eq "20.0" -or $lt -eq "30.0" -or $lt -eq "40.0" -or $lt -eq "50.0" -or $lt -eq "00.0"){Write-Host $primesw.elapsed.ToString()}
		If($l -like "*STOP*"){Break}
		If($l -eq "" -or $l -eq $null){}

		If($l -like "*GROUP:*"){
		$intRow = $objSheet.UsedRange.Rows.Count + 1
		$Group = "";$Group = $l.Split(":")[-1]
		}
		
		
		If($l -like "*GROUP:*"){
			$objSheet.Cells($intRow, 1).Value = $l.Split(":")[-1]
			$objSheet.Cells($intRow, 1).Font.Bold = $True
		}
		If($l -like "*UNIT:*"){
			$intRow = $objSheet.UsedRange.Rows.Count + 1
			$Unit = ""
			$objSheet.Cells($intRow, 2).Value = $l.Split(":")[-1]
			$objSheet.Cells($intRow, 2).Font.ColorIndex = 3
			$objSheet.Cells($intRow, 2).Font.Bold = $True
		}
		If($l -like "*DDMMmmm:*"){
			$objSheet.Cells($intRow, 3).Font.ColorIndex = 0
			
			$DDMMmmm = "";$DDMMmmm = $l.Split(":")[-1]
			$objSheet.Cells($intRow, 3).Value = $DDMMmmm.Split(" ")[1] + " " + $DDMMmmm.Split(" ")[2] + "  " + $DDMMmmm.Split(" ")[4] + " " + $DDMMmmm.Split(" ")[5] 
			$objSheet.Cells($intRow, 5).Value = $DDMMmmm.Split(" ")[7]
		}
		If($l -like "*DDMMSSss:*"){
			$objSheet.Cells($intRow, 4).Font.ColorIndex = 0

			$DDMMSSss = "";$DDMMSSss = $l.Split(":")[-1]
			$objSheet.Cells($intRow, 4).Value = $DDMMSSss.Split(" ")[1] + " " + $DDMMSSss.Split(" ")[2] + " " + $DDMMSSss.Split(" ")[4] + " " + $DDMMSSss.Split(" ")[5]
		}
	}#Ending foreach($l in $list)


<#			    1 2              4 5              7
"""""------ DDMMSSss: N 42°14'23.89""  E 40°6'41.4"""  -1'
#>



	$intRow = $intRow + 1




	#Saving Excel and Closing Excel	
		$objExcel.ActiveWorkBook.Save()
		#$objWb.Close()
		#$objExcel.Application.Quit()
		#$objExcel = ""
		#$objWb = ""
		#$objSheet = ""

	#Kill Excel
	#get-process | where { $_.Name -eq "excel" } | foreach { $_.Kill() }

	 
	#Stopwatch Times
	Write-host;Write-Host -BackgroundColor DarkMagenta -ForegroundColor Yellow Stopping the Timer
	$primesw.Stop()
	$Time = ($primesw.elapsed.ToString())
	If(($Time.Split(":")[-1]) -gt "00"){$timename = " Seconds"};If(($Time.Split(":")[-2]) -gt "00"){$timename = " Minutes"};If(($Time.Split(":")[-3]) -gt "00"){$timename = " Hours"}
	Write-Host -BackgroundColor DarkMagenta -ForegroundColor White ($primesw.elapsed.ToString() + "$TimeName")

	$wsh.Popup("Finished Creating Spreadsheet" + $cr2 + ($primesw.elapsed.ToString() + "$TimeName") + $cr2 + "Waiting here to review Script Window",0,"DO NOT Click OK Until You Are Finished Reading the Windows")


break



#Sample Console testing
$excelfile = "\\tcd6040n01\App_Packages\Regtools\Powershell.Scripts\!.Eplus.Deployment.NewFormat\!!!.Beaker\Master\zz.Ping.Beaker.Compile.Printer.List\zEC_Test_PSPing.Loop.xlsx"

#OPEN Excel
	$objExcel = New-Object -ComObject Excel.Application
	$objExcel.visible = $true
	$objWb = $objExcel.WorkBooks.Open($ExcelFile, $True)
	$objSheet = $objExcel.ActiveWorkBook.WorkSheets("Sheet1")
	$intRow = 5
	$intRow = $objSheet.UsedRange.Rows.Count


#CLOSE Excel
	$objExcel.ActiveWorkBook.Save()
	$objWb.Close()
	$objExcel.Application.Quit()
	$objExcel = ""
	$objWb = ""
	$objSheet = ""





			<#Region Simple Ping - PowerShell Ping - IPADDRESS
				#PARAM ($address)
				write-host -backgroundcolor Black -foregroundcolor Yellow Trying to Ping 	$l.PrinterName	$l.ipaddress	"	#	$count"
				$Ping = "";$Reply = "";$PingAddress = "";$PingStatus = ""
				$ping = new-object System.Net.Networkinformation.Ping
				$reply = $ping.Send($ipaddress,30,[byte]1)
				$pingstatus = $reply.status
				$pingaddress = $reply.Address

				If($Pingstatus -eq "Success"){$SuccessCount = $SuccessCount + 1;write-host -backgroundcolor Black -foregroundcolor Green "	    $PrinterName  - $ipaddress  -  $PingStatus"}
				If($Pingstatus -eq "TimedOut"){$TimedOutCount = $TimedOutCount + 1;write-host -backgroundcolor Black -foregroundcolor Magenta "	    $PrinterName  - $ipaddress  -  $PingStatus"}
				
				$pdate = (Get-Date -Format "MM-dd @ HH:mm:ss")
				"$PingStatus,$Pdate,$PrinterName,$IPAddress" >> $TextCSV
			#Endregion
			#>


			<#Region PSPing
				$ReplyCount = 0;$Reply1 = "";$Reply2 = "";$Reply3 = "";$Reply4 = "";$Reply5 = ""
				$psping = ""
				$psping = psping $IPAddress
					Foreach($p in $psping){
						If($p -like "psping*" -or `
						   $p -like "Copyright*" -or `
						   $p -like "*Sysinternals*" -or `
						   $p -like "Pinging*" -or `
						   $p -like "*iterations*" -or `
						   $p -like "*ping*" -or `
						   $p -like "*sent*" -or `
						   $p -like "*minimum*"){}
						If($p -like "Error*"){$PingStatus = "Timedout"}
						If($p -like "Reply*"){
							$PingStatus = "Success"
							$ReplyCount = $ReplyCount + 1
							If($ReplyCount -eq 1){$Reply1 = ($p.Split(":")[-1]).Replace(" ","")}
							If($ReplyCount -eq 2){$Reply2 = ($p.Split(":")[-1]).Replace(" ","")}
							If($ReplyCount -eq 3){$Reply3 = ($p.Split(":")[-1]).Replace(" ","")}
							If($ReplyCount -eq 4){$Reply4 = ($p.Split(":")[-1]).Replace(" ","")}
							If($ReplyCount -eq 5){$Reply5 = ($p.Split(":")[-1]).Replace(" ","")}
						}
					}
						If($Pingstatus -eq "Success"){$SuccessCount = $SuccessCount + 1;write-host -backgroundcolor Black -foregroundcolor Green "	    $PrinterName  - $ipaddress  -  $PingStatus - $Count"}
						If($Pingstatus -eq "TimedOut"){$TimedOutCount = $TimedOutCount + 1;write-host -backgroundcolor Black -foregroundcolor Magenta "	    $PrinterName  - $ipaddress  -  $PingStatus - $Count"}
						
						$pdate = (Get-Date -Format "MM-dd @ HH:mm:ss")
						write-host -BackgroundColor Black -ForegroundColor yellow "          $PingStatus,$Reply1,$Reply2,$Reply3,$Reply4,$Reply5"
						"$Pdate,$PingStatus,$Reply1,$Reply2,$Reply3,$Reply4,$Reply5,$PrinterName,$IPAddress" >> $TextCSV
			#Endregion
			#>

# This Creates an Excel Color Chart 10 Columns Wide  1-56. First Font Color Black (1), Then run again With Font Color White
# The Spreadsheet must be manually opened.

$LoopCount = 0
$Column = 10
Do{
If($Column -eq 10){$Column = 0;$introw = $introw + 1}
$LoopCount = $LoopCount + 1
$Column = $Column + 1
$objSheet.Cells($intRow, $Column).Value =$LoopCount
$objSheet.Cells($intRow, $Column).Interior.ColorIndex = $LoopCount
$objSheet.Cells($intRow, $Column).Font.ColorIndex = 2
$objSheet.Cells($intRow, $Column).Font.Size = 18
}
Until($LoopCount -eq "56")