This script started out as a project to figure out how to automate Excel web queries in PowerShell, so MS Excel is a prerequisite for this script. If you have Excel, but have never used query tables, it is accessible by going to the Data tab, and then choosing “From Web”. An overview from TechRepublic is here. Web Queries have been around since Excel 2000, but I think it is a pretty obscure feature.
I was hoping to use the functionality for an internal project which had failed to work satisfactorily with any of the standard ways of pulling down web pages. When I began to automate Excel in PowerShell, I found that things which I had mastered in VbScript were actually a bit harder in PowerShell. This script shows the techniques of how to force Excel to close, and how to use SaveAs a CSV file.
Using a Web Query did not work for my internal project either, and this languished in my half-written pile for some time. Earlier in the week, I had to stay late at the office while waiting for an appointment and decided to take the time to make something practical of what I had worked on.
Holidays can be a difficult to work with when calculating workdays. I use TimeAndDate.com, which not only pulled off a most excellent domain name, but also is a very good resource for time and date information. I urge you to visit this page often, especially if you plan to use this script which takes holiday data from them. They have put the data in a very friendly format for web queries; go to the website at http://www.timeanddate.com/holidays/ to see the countries available and their URLs. Visit the URL for your country to see what sort of data is available from the website.
Get-Holidays.ps1 begins by getting a list of already open Excel process IDs and saves them. It then opens Excel, does the web query and saves the results as a randomly named CSV file. I try to close Excel gracefully, then delete the Excel PID which was not open when the script started. The Import-CSV is used to import the data. (Yes, I know I could have collected it from the spreadsheet directly). The temporary file is deleted. The data is manipulated so that the text dates become date objects, and some of the fields are renamed.
The demo date stuff at the bottom shows how to select for a particular type of holiday and how to format the date object to show the date without the timestamp.
I have this written for the US http://www.timeanddate.com/holidays/us/, but you can see that the URL for the UK it is www.timeanddate.com/holidays/uk/, and for Canada is www.timeanddate.com/holidays/Canada/. As written you have to change the the URL in the code to change the country. I have added example code which gets the US National Holidays for 2015. [Edit 12/2017: The example no longer works, below is a better one]
#Example. Get holidays for upcoming year, display in two columns with Holiday and Date with day.
$year = (get-date).year +1
$holidays =Get-Holidays $year
#$holidays now is an object with the holidays for the current year, which you can manipulate
$holidays |
select @{Name="Date"; Expression = {Get-Date($_.date) -format D}}, @{Name="Holiday"; Expression = {$_.holidayName}}, type |
Out-GridView -Title "Holidays for $year"