#Alan dot Kaplan at VA dot GOV, 3-7-14
#This gets the US Holidays from the website http://www.timeanddate.com
#It is an example of how to get web tables with Excel

Function Get-Holidays([int]$CalYear){
    $import = ""
    #Get a random filename ending in .CSV
    $TempName = $env:temp+[System.IO.Path]::GetRandomFileName()
    $TempName = $tempName.Substring(0, $TempName.LastIndexOf('.'))+".csv"

    #Capture existing Excel PIDs so you can slam it closed later. Three lines by 
    #Alex K. Angelopoulos, http://www.vistax64.com/powershell/241658-cant-get-excel-exe-close.html
    $before = @(Get-Process [e]xcel | %{$_.Id})
    $oXL = New-Object -comobject Excel.Application
    $ExcelId = Get-Process excel | %{$_.Id} | ?{$before -notcontains $_}

    $oXL.Visible = $False
    $oWKS = $oXL.Workbooks.Add(1)
    $oXL.ActiveSheet.name = "Table"
    $sheet = $oXL.activesheet
    #Go to the website at http://www.timeanddate.com/holidays/ to see additional country URLs
    #Example, for UK it is www.timeanddate.com/holidays/uk/, Canada is www.timeanddate.com/holidays/Canada/
    $QTable = $sheet.QueryTables.Add("URL;http://www.timeanddate.com/holidays/us/$calYear",$sheet.Application.Range("A1") )
    $QTable.Name = $CalYear
    $QTable.FieldNames = $True
    $QTable.RowNumbers = $False
    $QTable.FillAdjacentFormulas = $False
    $QTable.PreserveFormatting = $True
    $QTable.RefreshOnFileOpen = $False
    $QTable.BackgroundQuery = $True
    $QTable.RefreshStyle = 1
    $QTable.SavePassword = $False
    $QTable.SaveData = $True
    $QTable.AdjustColumnWidth = $True
    $QTable.RefreshPeriod = 0
    $QTable.WebPreFormattedTextToColumns = $True
    $QTable.WebConsecutiveDelimitersAsOne = $True
    $QTable.WebSingleBlockTextImport = $False
    $QTable.WebDisableDateRecognition = $False
    $QTable.WebDisableRedirections = $False

    #this and the out null stuff is to try to avoid "true from being echoed to the console
    $QTable.Refresh() | Out-Null
    Sleep -seconds 1
    $bRefresh = $true
    
    While ($bRefresh){
        $bRefresh = $QTable.Refreshing 
    Sleep -Milliseconds 500
    }
 
    #Cleanup used parts of sheet
    $range = $sheet.Cells.Item(2,1).entirerow 
    $range.delete() | Out-Null
    #save as type 6 -- CSV file using temp name
    $oXL.ActiveWorkBook.Saveas($TempName,6)
    $oxl.Quit()
    #Polite release of oXL com objects
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($oxl)){}

    #Then, just in case, kill only the instance of Excel we identified above by PID
    Stop-Process -Id $ExcelId -Force -ErrorAction SilentlyContinue

    $import = Import-Csv $TempName
    sleep -Seconds 1
    Remove-Item $TempName -Force 

    #could quit here, but I like the date to have the year information, plus cleaner column names
    $HDList = ForEach ($holiday in $import){
            [PSCustomObject]@{
            HolidayName = $holiday.'Holiday Name'
            Date = Get-date ([string]$holiday.Date+$calYear)
            Type = $holiday.'Holiday Type'
            Observed = $holiday.'Where it is observed'
            }
    }
    $HDList
}

#Example.  Get US National Holidays for 2015, display in two columns with Holiday and Date with day.
$holidays =Get-Holidays 2015
#$holidays now is an object with the holidays for 2015, which you can manipulate
$holidays | where {$_.type -like "National Holiday"} | 
    select @{Name="Holiday"; Expression = {$_.holidayName}}, @{Name="Date"; Expression = {Get-Date($_.date) -format D}} |
    ft -AutoSize