Source code for airregeicmd.ps

```
<#
```
```
.SYNOPSIS
```

Search a downloaded Irish aircraft registration database for a Irish aircraft registation and if found, return the aircraft manufacturer and model (tab-delimited)

```
 
```
```
.DESCRIPTION
```

First, create a subdirectory 'EI' in this script's parent folder.

Next, download the Irish aircraft registration database (see links section), unzip it and move the Excel file (and optionally the other files as well) to the 'EI' folder.

Now run this script with an aircraft registration as its only parameter (see examples section).

The script will first look for the most recent Excel file in the 'EI' folder.

When found, it will open the first sheet of the Excel file, and search for the specified aircraft registration in the first column of the Excel sheet.

If a match is found, the script will display a tab-delimited string with the registration, the manufacturer and the aircraft model (<registration><tab><manufacturer><tab><model>).

If the script was started by another PowerShell script, the calling PowerShell script may also read the manufacturer and model from the variables $Manufacturer and $Model, passed on by this script.

If the script was started by a batch file, the calling batch file can use 'FOR /F' on this PowerShell script's screen output to find the manufacturer and model.

Get-Help './AirRegEICmd.ps1' -Examples will show 2 examples of this script being called by another script.

```
 
```
```
.PARAMETER Registration
```

A valid Irish aircraft registration, i.e. EI-xxx (where x is a single character)

```
 
```
```
.PARAMETER TerminateExcel
```

Terminate Excel when the script is finished, if and only if Excel was not running when the script was started and the number of Excel processes is exactly one when the script is finished.

```
 
```
```
.PARAMETER Quiet
```

Ignore all errors and do not display any error messages; in case of errors, just terminate with return code 1.

```
 
```
```
.PARAMETER Debug
```
```
Show some progress messages
```
```
 
```
```
.PARAMETER Help
```
```
Show the script's help screen
```
```
 
```
```
.OUTPUTS
```

A tab-delimited string <registration><tab><manufacturer><tab><model> and manufacturer and model are also stored in output variables $Manufacturer and $Model.

```
 
```
```
.EXAMPLE
```
```
. ./AirRegEICmd.ps1 "EI-ALP"
```

Will return tab-delimited string "EI-ALP<tab>BAE SYSTEMS (OPERATIONS) LIMITED<tab>AVRO 643 CADET", and set variables $Manufacturer to "BAE SYSTEMS (OPERATIONS) LIMITED" and $Model to "AVRO 643 CADET"

```
 
```
```
.EXAMPLE
```
```
"EI-ALP" | . ./AirRegEICmd.ps1
```

Will also return tab-delimited string "EI-ALP<tab>BAE SYSTEMS (OPERATIONS) LIMITED<tab>AVRO 643 CADET", and set variables $Manufacturer to "BAE SYSTEMS (OPERATIONS) LIMITED" and $Model to "AVRO 643 CADET"

```
 
```
```
.EXAMPLE
```

Create and run the following PowerShell script:

===============================================================

$Registration = 'EI-ALP' ; $Manufacturer = '' ; $Model = ''

[void] ( . "$PSScriptRoot\AirRegEICmd.ps1" -Registration $Registration )

Write-Host ( "Registration : {0}`nManufacturer : {1}`nModel        : {2}" -f $Registration, $Manufacturer, $Model )

===============================================================

```
 
```

Besides setting variables $Manufacturer to "HiSystems GmbH" and $Model to "MK OktoKopter XL2", it will return:

```
 
```
```
Registration : EI-ALP
```

Manufacturer : BAE SYSTEMS (OPERATIONS) LIMITED

```
Model        : AVRO 643 CADET
```
```
 
```
```
.EXAMPLE
```

Create and run the following batch file:

===============================================================

REM Note that there should only be a TAB and nothing else between delims= and the doublequote

FOR /F "tokens=1-3 delims=	" %%A IN ('powershell . ./AirRegEICmd.ps1 EI-ALP') DO (

```
	ECHO Registration : %%A
```
```
	ECHO Manufacturer : %%B
```
```
	ECHO Model        : %%C
```
```
)
```

===============================================================

```
 
```
```
It will return:
```
```
 
```
```
Registration : EI-ALP
```

Manufacturer : BAE SYSTEMS (OPERATIONS) LIMITED

```
Model        : AVRO 643 CADET
```
```
 
```
```
.LINK
```
```
Script written by Rob van der Woude:
```
```
https://www.robvanderwoude.com/
```
```
 
```
```
.LINK
```

Downloadable Irish aircraft registration database in Excel format:

https://www.iaa.ie/commercial-aviation/aircraft-registration-2/latest-register-and-monthly-changes-1

```
 
```
```
.LINK
```

Article "PowerShell - Read an Excel file using COM Interface" by Fran�ois-Xavier Cat:

https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html

```
#>
```
```
 
```
```
param (
```
```
	[Parameter( ValueFromPipeline )]
```

	[ValidatePattern("(^\s*$|[\?/]|^EI-[A-Z]{3}$)")]

```
	[string]$Registration,
```
```
	[switch]$TerminateExcel,
```
```
	[switch]$Quiet,
```
```
	[switch]$Help
```
```
)
```
```
 
```
```
$progver = "1.00"
```
```
 
```

$Registration = $Registration.ToUpper( )

```
$Manufacturer = ''
```
```
$Model = ''
```
```
$ExcelError = $false
```

[bool]$Debug = ( $PSBoundParameters.ContainsKey( 'Debug' ) )

```
 
```
```
function GetMonth( ) {
```
```
	param (
```
```
		[Parameter(Mandatory)]
```

		[ValidateSet("january","february","march","april","may","june","july","august","september","october","november","december")]

```
		[string]
```
```
		$month
```
```
	)
```
```
 
```

	( "","january","february","march","april","may","june","july","august","september","october","november","december" ).IndexOf( $month.ToLower( ) )

```
}
```
```
 
```

function GetLatestExcelFile( $folder ) {

```
	[string]$excelfile = ''
```
```
	[int]$latestyear = 0
```
```
	[int]$latestmonth = 0
```
```
	[int]$latestday = 0
```

	Get-ChildItem -Path $dbfolder -Filter '*-*-*.xlsx' | ForEach-Object {

```
		$filename = $_.BaseName
```
```
		$filedate = $filename.Split( '-' )
```

		# Initially selected file is the first one

```
		if ( $excelfile -eq '' ) {
```
```
			$excelfile = $_.FullName
```
```
			$latestyear = $filedate[2]
```

			$latestmonth = ( GetMonth( $filedate[1] ) )

```
			$latestday = $filedate[0]
```
```
		}
```

		# Next we'll look for a more recently released file

```
		if ( $filedate[2] -gt $latestyear ) {
```
```
			$excelfile = $_.FullName
```
```
			$latestyear = $filedate[2]
```

			$latestmonth = ( GetMonth( $filedate[1] ) )

```
			$latestday = $filedate[0]
```

		} elseif ( $filedate[2] -eq $latestyear ) {

			if ( ( GetMonth( $filedate[1] ) ) -gt $latestmonth ) {

```
				$excelfile = $_.FullName
```
```
				$latestyear = $filedate[2]
```

				$latestmonth = ( GetMonth( $filedate[1] ) )

```
				$latestday = $filedate[0]
```
```
			}
```
```
		}
```
```
	}
```
```
	$excelfile
```
```
}
```
```
 
```

function ShowHelp( $errormessage = '' ) {

```
	if ( !$Quiet ) {
```
```
		if ( $errormessage ) {
```
```
			Write-Host
```

			Write-Host "Error: " -ForegroundColor Red -NoNewline

```
			Write-Host $errormessage
```
```
		}
```
```
		Write-Host
```

		Write-Host ( "AirRegEICmd.ps1,  Version {0}" -f $progver )

		Write-Host "Search downloaded Irish aircraft registration database for a registation"

```
		Write-Host
```
```
		Write-Host "Usage:  " -NoNewline
```

		Write-Host ". ./AirRegEICmd.ps1  [ -Registration ]  EI-***  [ -TerminateExcel ]  [ -Help ]" -ForegroundColor White

```
		Write-Host
```
```
		Write-Host "Where:  " -NoNewline
```

		Write-Host "EI-***           " -NoNewline -ForegroundColor White

		Write-Host "is a valid Irish aircraft registration, e.g. EI-ALP"

		Write-Host "        -TerminateExcel  " -NoNewline -ForegroundColor White

		Write-Host "terminates Excel when the script is finished, " -NoNewline

		Write-Host "if " -NoNewline -ForegroundColor White

```
		Write-Host "and " -NoNewline
```

		Write-Host "only" -ForegroundColor White

		Write-Host "                         if " -NoNewline -ForegroundColor White

		Write-Host "Excel was not running when the script was started"

		Write-Host "        -Quiet           " -NoNewline -ForegroundColor White

		Write-Host "all errors are ignored and no error messages displayed"

		Write-Host "        -Help            " -NoNewline -ForegroundColor White

```
		Write-Host "shows this help screen"
```
```
		Write-Host
```

		Write-Host "Notes:  This script requires a downloaded Irish aircraft registration database,"

		Write-Host "        located in a subfolder 'EI' of this script's parent folder."

		Write-Host "        The Irish aircraft registration database can be downloaded at:"

		Write-Host "        https://www.iaa.ie/commercial-aviation/aircraft-registration-2" -ForegroundColor DarkGray

		Write-Host "        /latest-register-and-monthly-changes-1" -ForegroundColor DarkGray

		Write-Host "        This script also requires Excel, since the downloaded database is in"

```
		Write-Host "        Excel format."
```

		Write-Host "        The result, if any, of the search is displayed as tab-delimited text:"

		Write-Host "        <registration><tab><manufacturer><tab><model>"

		Write-Host "        Besides its screen output, this script will also set the `$Manufacturer"

		Write-Host "        and `$Model variables with the database search result."

```
		Write-Host "        Run " -NoNewline
```

		Write-Host "Get-Help `"./AirRegEICmd.ps1`" -Examples " -NoNewline -ForegroundColor White

```
		Write-Host "for some examples of"
```

		Write-Host "        `"nesting`" this script in other PowerShell or batch scripts."

		Write-Host "        Return code (`"ErrorLevel`") 1 in case of errors, otherwise 0."

```
		Write-Host
```

		Write-Host "Written by Rob van der Woude"

		Write-Host "https://www.robvanderwoude.com"

```
	}
```
```
	Exit 1
```
```
}
```
```
 
```

if ( $Help -or $Registration -match "(^\s*$|^-|\?|/)" ) {

```
	ShowHelp
```
```
	Exit 1
```
```
}
```
```
 
```
```
# Check if Excel is already running
```

$excelactive = [bool]( ( Get-Process -Name "Excel" -ErrorAction 'SilentlyContinue' | Measure-Object ).Count -gt 0 )

```
if ( $Debug ) {
```
```
	if ( $excelactive ) {
```

		Write-Host "Excel already active at start"

```
	} else {
```

		Write-Host "Excel not active at start"

```
	}
```
```
}
```
```
 
```

$dbfolder = ( Join-Path -Path $PSScriptRoot -ChildPath 'EI' )

```
if ( Test-Path $dbfolder ) {
```

	$excelfile = GetlatestExcelFile( $dbfolder )

```
	if ( $excelfile ) {
```
```
		if ( $Debug ) {
```

			Write-Host ( "Using Excel file `"{0}`"" -f $excelfile )

			$StopWatch = [System.Diagnostics.Stopwatch]::StartNew( )

			Write-Host ( "Start searching for {0} in Excel file at {1}" -f $Registration, ( Get-Date ) )

```
		}
```

		$found = $false # will be set to True when a matching aircraft is found in the database

		$ErrorActionPreference = 'SilentlyContinue' # Temporarily hide all error messages, we will handle the next one ourselves

```
		# Open an Excel COM object
```

		$objExcel = New-Object -ComObject Excel.Application -ErrorAction 'SilentlyContinue' -ErrorVariable ExcelError

		$ErrorActionPreference = 'Continue' # Reenable output of future error messages

```
		if ( $ExcelError ) {
```

			ShowHelp( "Microsoft Excel not found" ) # Actually we were unable to open Excel's COM object

```
		} else {
```

			$ErrorActionPreference = 'SilentlyContinue' # Temporarily hide all error messages, we will handle the next one ourselves

			# Open the Excel aircraft database file

			$workbook = $objExcel.Workbooks.Open( $excelfile )

			$ErrorActionPreference = 'Continue' # Reenable output of future error messages

			# Check if the Excel file was successfully opened

			if ( ![bool]( $objExcel.WorkBooks | Select-Object -Property Name ) ) {

				$ErrorActionPreference = 'SilentlyContinue' # Ignore possible error when trying to close the Excel file and program

```
				[void] $workbook.Close( )
```
```
				[void] $objExcel.Quit( )
```

				$ErrorActionPreference = 'Continue' # Reenable output of future error messages

				ShowHelp( "Unable to open Excel file `"{0}`"" -f $excelfile )

```
			}
```

			# Iterate through all rows in the first sheet of the Excel file

			$workbook.Sheets.Item( 1 ).UsedRange.Rows | ForEach-Object {

				# After the first match we don't need to check for other matches, hence the check of the $found variable

```
				if ( !$found ) {
```

					if ( $_.Columns.Item( 1 ).Text -eq $Registration ) {

```
						# We have a match!
```
```
						if ( $Debug ) {
```

							Write-Host ( "Found a match at {0}" -f ( Get-Date ) )

```
						}
```

						$found = $true # used instead of Break, which would also stop parent process; only slightly slower than Break

						$Manufacturer = $_.Columns.Item( 2 ).Text # Manufacturer is in column B

						$Model = $_.Columns.Item( 3 ).Text # Model is in column C

						# By not using Write-Host, we allow calling scripts to suppress screen output and use passed on global variables

						( "{0}`t{1}`t{2}" -f $_.Columns.Item( 1 ).Text, $Manufacturer, $Model ) | Out-String

```
					}
```
```
				}
```
```
			}
```
```
			[void] $workbook.Close( )
```
```
			[void] $objExcel.Quit( )
```
```
			if ( $Debug ) {
```

				Write-Host ( "Finished at {0} (elapsed time {1})" -f ( Get-Date ), $StopWatch.Elapsed )

```
				$StopWatch.Stop( )
```
```
			}
```
```
		}
```
```
	} else {
```
```
		if ( $Debug ) {
```

			Write-Host ( "Database folder `"{0}`" not found" -f $dbfolder )

```
		}
```
```
		if ( $Quiet ) {
```
```
			Exit 1
```
```
		} else {
```

			$message = "No downloaded Irish aircraft registration database was found.`n`nDo you want to open the download webpage for the database now?"

```
			$title   = "No Database Found"
```
```
			$buttons = "YesNo"
```

			Add-Type -AssemblyName 'System.Windows.Forms'

			$answer = [System.Windows.Forms.MessageBox]::Show( $message, $title, $buttons )

```
			if ( $answer -eq 'Yes' ) {
```

				$url = 'https://www.iaa.ie/commercial-aviation/aircraft-registration-2/latest-register-and-monthly-changes-1'

```
				Start-Process $url
```
```
			} else {
```

				ShowHelp( "No downloaded Irish aircraft registration database found, please download it and try again" )

```
			}
```
```
		}
```
```
	}
```
```
}
```
```
 
```

# Terminate Excel if requested and if this script's Excel process was the only instance

$processcount = ( Get-Process -Name "Excel" -ErrorAction 'SilentlyContinue' | Measure-Object ).Count

```
if ( $Debug ) {
```

	Write-Host ( "{0} active Excel processes when done" -f $processcount )

```
}
```

if ( $TerminateExcel -and !$excelactive -and ( $processcount -eq 1 ) ) {

```
	if ( $Debug ) {
```
```
		Write-Host "Terminating Excel now"
```
```
	}
```

	Get-Process -Name "Excel" -ErrorAction 'SilentlyContinue' | Stop-Process -ErrorAction 'SilentlyContinue'

```
}
```
```
 
```