Excel Power: Automatic Pull & Sort Microsoft MPs From Microsoft TechNet Wiki

I don’t know about you, but for myself the TechNet Wiki for the Microsoft MPs has become the one-stop-shop when downloading the Microsoft MPs. Even though it’s a great website it has one huge drawback: you can’t sort the MPs on date so you can see in a single glance which MP is the latest & greatest.

Yes, with PS you can do a lot and already there are many PS scripts out there which contact the Wiki. But as far a I know there isn’t a PS script which pulls all MPs, together with their download links and sort them on their date.

So therefore I decided to use another approach: Excel. This is a very powerful application and can do so many things for you, fully automated. So why not pull the list of all Microsoft MPs –with their download links intact - from the Wiki website, and sort them on their publishing date as well?

And as it turned out, it was very easy to make it work. In about 15 minutes I got it working. And no, it doesn’t have anything fancy. It does what I state it does and that’s it. So feel free to make it more perfect and give it a posh look.

Also know that your computer, where you run this Excel sheet from requires your computer to have the Short date set to US style (M/d/yyyy) since otherwise the automatic sorting won’t work.

So this is the setting your computer must be set to, at least during the time you run this Excel sheet:

Why you ask? Well, the same date format is used by the Wiki as well. And when your computer don’t use the same date format while sorting, it will sort the MPs on their month number only, resulting in a strange sorting .

Another thing to reckon is that the Excel workbook uses a macro in order to sort the MPs on their date. I didn’t author that macro but found it on the web and modified it for this Excel workbook. So all credits for that macro go to Allen Wyatt who shares this macro on his blog.

But because this Excel workbook contains a macro, it has a different file format, xlsm, an Excel workbook with one or more macro’s. So when you open this workbook, you’ll get several security warnings.

One more thing: Since the data source pulls the WHOLE Wiki webpage into Excel, the first 60 rows are hidden, as goes for the rows after the last MP, in order to keep the Excel workbook a bit better looking.

I know, it’s really basic, but it works:

How to get it working:

1. Download the file from my OneDrive to your system;
2. When downloaded, unblock it
   
3. Open the file, enable the content:
   
4. On the computer: Check the Short date settings on the system running this Excel workbook and when required modify it to M/d/yyyy and don’t forget to APPLY these new settings:
   
5. In the Excel workbook: Go to Data > click on Refresh All
   
6. Excel will connect with the TechNet Wiki page, download all the information and pipe it into the Excel workbook. The previous mentioned macro will sort that data so the latest MP shows up on top:
   
   As you can see, the BizTalk Server 2013 R2 MP is the latest & greatest MP .
7. When you’ve modified the Short date setting (in Step 4) for running this Excel workbook, don’t forget to set it back to d/M/yyyy: