# Create an Excel instance and hide it.
Write-Host "Starting Excel..."
$xl = New-Object -Comobject Excel.Application
$xl.Visible = $false

# Do the same for MS Word.
#$wd = New-Object -Comobject Word.Application
#$wd.Visible = $false

# Load all Excel files into an array.
Write-Host "Finding Files... " -NoNewline
$inputFiles = Get-ChildItem "D:\Foundry\In" -Filter *.xls*
$count = $inputFiles.Count
Write-Host "$count files found."
Write-Host ""
Write-Host "Processing Files..."
Write-Host ""
Write-Host "Status  Filename"
Write-Host "------  --------"




# Enumerate through each item.
# $inFile is used to reference each item in the table
Foreach ($inFile in $inputFiles)
{
    # Get the file path
    $inFilePath = $inFile.FullName
    $inFileName = $inFile.Name
    Write-Host "[ ok ]  $inFileName"

    # Open the file and open the first worksheet.
    $wb = $xl.workbooks.open($inFilePath)
    $ws = $wb.worksheets.item(1)

    # We'll get some basic info first.
    # Each sheet has multiple tables of varying length,
    # But there is still some information at predictable cell locations.
    # We use the "Range" to identify single cells mainly for consistency and convenience
    # There are other ways to do it, though.

    $ClientFullName = $ws.Range("B4").Text
    $VRCFullName = $ws.Range("D7").Text

    # Now, let me show you how it's done ;)
    # We search for the headers of each table.
    # Once we find one, we note it as the first.
    # THen, we continue finding it over and over again
    # We do this until we find the first one again, or find nothing at all.
    # This gives us all cells in the searched range with the text we searched for.
    $SearchTerm = "Question Sequence"
    $Range = $ws.UsedRange()
    $Match = $Range.Find($SearchTerm)
    $First = $Match
    Write-Host "        Reports at: " -NoNewline
    Do
    {
        $MatchCell = $Match.AddressLocal($false, $false)
        Write-Host "$MatchCell " -NoNewline
        $Match = $Range.FindNext($Match)
    } while ($Match -ne $NULL -and $Match.AddressLocal() -ne $First.AddressLocal())
    Write-Host ""
    Write-Host ""
}

# Quit Excel, then instruct the system to release the COM Object.
# Doing this is necessary, otherwise the process will never exit, even when we call the Quit() method.
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
Remove-Variable xl