Richard Siddaway's Blog

One change in WMF 5.0 that I hadn’t got round to testing was the speed up in COM based operations.

COM – Component Object Model – was the Microsoft programming model before .NET.  Its old but still around – the interfaces for Internet Explorer and Office are all COM based for instance.

WMF 5.0 promises faster running for COM based applications.  To test it I tried an old script that opens an Excel spread sheet and populates some columns.

$xl = New-Object -comobject ‘Excel.Application’
$xl.visible = $true
$xlbooks =$xl.workbooks
$wkbk = $xlbooks.Add()
$sheet = $wkbk.WorkSheets.Item(1)

## create headers
$sheet.Cells.Item(1,1).FormulaLocal = ‘Value’
$sheet.Cells.Item(1,2).FormulaLocal = ‘Square’
$sheet.Cells.Item(1,3).FormulaLocal = ‘Cube’
$sheet.Cells.Item(1,4).FormulaLocal = ‘Delta’

$row = 2

for ($i=1;$i -lt 25; $i++){

$f = $i*$i

$sheet.Cells.Item($row,1).FormulaLocal = $i
$sheet.Cells.Item($row,2).FormulaLocal = $f
$sheet.Cells.Item($row,3).FormulaLocal = $f*$i
$sheet.Cells.Item($row,4).FormulaR1C1Local = ‘=RC[-1]-RC[-2]’

$row++
}

In the past working with Excel has been glacially slow. So slow that you could watch each individual change.

In WMF 5.0 its much, much faster.

In the past my advice has been to create a CSV file and then import the data from that into Excel. With the much better speed offered by WMF 5.0 I may have to reconsider that and think that working directly with Excel is now a viable proposition.

Its surprisingly easy to programatically add a drop down list to Word document

$Word = New-Object -Com Word.Application
$word.visible = $true
$template = "c:\test\template.docx"  
$Doc = $Word.Documents.Open($template)
$Doc.Activate()   

$cntrl = [Enum]::Parse([Microsoft.Office.Interop.Word.WdContentControlType], "wdContentControlDropdownList")

$objCC = $doc.ContentControls.Add($cntrl)

$objCC.DropdownListEntries.Add("PowerShell")
$objCC.DropdownListEntries.Add("Ruby")
$objCC.DropdownListEntries.Add("Perl")

Create the COM object for Word and set visible.  Open a template (in this case a blank document) file and activate.

Set the type of control you want to add and define the possible entries.

It should be possible to set the default text but the method for doing that appears to be very awkward.  I’ll publish that if I work it out.

Pages are towards the bottom of the hierarchy in OneNote – though we still haven’t dived into the content of pages yet.

You can find the pages in your notebooks like this:

$onenote = New-Object -ComObject OneNote.Application
$scope = [Microsoft.Office.Interop.OneNote.HierarchyScope]::hsPages
[ref]$xml = ”

$onenote.GetHierarchy($null, $scope, $xml)

$schema = @{one="http://schemas.microsoft.com/office/onenote/2013/onenote"}

$xpath = "//one:Notebook/one:Section"
Select-Xml -Xml ([xml]$xml.Value) -Namespace $schema -XPath $xpath |
foreach {
$psitem.Node.Name
}

There’s one drawback to this – all you get is the page names:

PowerShell Summit
Comedy
Personal information
Book Series
AD
Applications
CCNA
Exchange
Infrastructure Architecture
Operations
Security
Hyper-V
WMI
PS Deep Dive
Deep Dive US12

You need to play around with Xpath a bit more to get a meaningfiul structure – ideally notebook – section – page

I recently showed how to find the names of your OneNote notebooks.  The next level down is the section.  You can find these sections in a notebook like this:

$onenote = New-Object -ComObject OneNote.Application
$scope = [Microsoft.Office.Interop.OneNote.HierarchyScope]::hsPages
[ref]$xml = ”

$onenote.GetHierarchy($null, $scope, $xml)

$schema = @{one="http://schemas.microsoft.com/office/onenote/2013/onenote"}

$xpath = "//one:Notebook/one:Section"
Select-Xml -Xml ([xml]$xml.Value) -Namespace $schema -XPath $xpath |
foreach {
$node = $psitem.Node

$npath = Split-Path -Path $node.Path -Parent
 
$props = [ordered]@{
   Workbook =  Split-Path -Path $npath -Leaf
   Section = $node.Name
}
New-Object -TypeName PSObject -Property $props
}

The first part of the script where the application object is created, the scope set and you get the hierarchy is the same as before.  The two scripts diverge when you get to the Xpath you’re going to use.  To find the notebooks you used:

$xpath = "//one:Notebook"

which means get me any Nodes called one:Notebook

To find the section you use:

$xpath = "//one:Notebook/one:Section"

which means any nodes called one;Section that are children of a one:Notebook node.

Remember XML is case sensitive.

Once you have the section nodes – which look like this:

name             : Quick Notes
ID               : {9EFAE9AC-0388-424A-8211-02E8FFE50666}{1}{B0}
path             : https://d.docs.live.net/43cfa46a74cf3e96/Documents/Personal (Web)/Quick Notes.one
lastModifiedTime : 2014-09-04T17:48:07.000Z
color            : #B7C997
Page             : {OneNote: one place for all of your notes, OneNote Basics}

You can extract the data you want. The path property can be used to extract the name of the note book with a little bit of Split-Path magic.

Next time you’ll see how to get down to the individual pages

When OneNote first came out there wasn’t an API for it as you get for Word or Excel. A community module enabled you to work with the XML that formed the OneNote note books but it wasn’t updated after Office 2007 and doesn’t really work with later versions of OneNote.

I was looking at the Office developer site and noticed that there was some information on OneNote http://msdn.microsoft.com/en-us/library/office/jj680118(v=office.15).aspx.  This series will investigate how to script against OneNote and also expalin how to use Select-XML and XPath on the way.

A good starting point would be to discover the OneNote notebooks:

$onenote = New-Object -ComObject OneNote.Application
$scope = [Microsoft.Office.Interop.OneNote.HierarchyScope]::hsPages
[ref]$xml = ”

$onenote.GetHierarchy($null, $scope, $xml)

$schema = @{one="http://schemas.microsoft.com/office/onenote/2013/onenote"}
$xpath = "//one:Notebook"
Select-Xml -Xml ([xml]$xml.Value) -Namespace $schema -XPath $xpath |
foreach {
$psitem.Node.Name
}

The starting point – like all good Office applications – is a COM object that exposes the OneNote object model.  As an aside isn’t time office moved away from COM and we had a proper .NET API or even better a PowerShell module.

You also need to define the scope – in this case get all pages. The enumeration is described here http://msdn.microsoft.com/en-us/library/office/jj680119(v=office.15).aspx

You also need to create a [ref] object to hold the output

The GetHierarchy() method is used to read through the notebooks. The $null argument means start at the top

The schema can be found inside the XML produced so to avoid a circular argument I’ll set that in a variable – it has to be a hash table as shown

Define the XPath – in this case get the nodes labelled one:Notebook

Select-XML will extract the required nodes – notice how the object has to be presented.

A simple foreacch iterates over the nodes which look like this

name             : Personal (Web)
nickname         : Personal (Web)
ID               : {F8CC78D5-9CC3-40C8-847B-96B15E3D6AD2}{1}{B0}
path             : https://<a path>  (Web)/
lastModifiedTime : 2014-09-04T17:48:07.000Z
color            : #FFD869
Section          : {Quick Notes, Unfiled Notes, PowerShell Summit}
SectionGroup     : SectionGroup

And you can select the name of the notebook.

Back in this post http://richardspowershellblog.wordpress.com/2012/10/15/powershell-3-and-word/

I showed that this code

$word = New-Object -ComObject "Word.application"           
$word.visible = $true           
$doc = $word.Documents.Add()           
$doc.Activate()           
           
$word.Selection.Font.Name = "Cambria"           
$word.Selection.Font.Size = "20"           
$word.Selection.TypeText("PowerShell")           
$word.Selection.TypeParagraph()           
           
$word.Selection.Font.Name = "Calibri"           
$word.Selection.Font.Size = "12"           
$word.Selection.TypeText("The best scripting language in the world!")           
$word.Selection.TypeParagraph()           
           
$file = "c:\test1.doc"           
$doc.SaveAs([REF]$file)           
           
$Word.Quit()

Wouldn’t work because of an error in the way [REF] was treated. I had a comment left on the post saying it worked on PowerShell v4.

I’ve tested on PowerShell v4 on Windows 8.1 with Office 2013. It works. I can’t vouch for other combinations but it looks the problem has been resolved.

If you have the opportunity please try it and let me know if it doesn’t work for your particular combination of PowerShell v4, Windows and Office

You can also use the SoftwareLicensingProduct CIM class to test the status of your Office products.

Get-CimInstance -ClassName SoftwareLicensingProduct -Filter "Name LIKE ‘Office%’" |
where PartialProductKey |
select Name, ApplicationId, LicenseStatus

You need to be careful with Office as you might find a lot more options than you expected. On my machine I found this:

Get-CimInstance -ClassName SoftwareLicensingProduct -Filter "Name LIKE ‘Office%’" | select Name -Unique | sort name

Office 15, OfficeO365ProPlusR_Grace edition
Office 15, OfficeO365ProPlusR_Retail edition
Office 15, OfficeO365ProPlusR_Subscription1 edition
Office 15, OfficeO365ProPlusR_Subscription2 edition
Office 15, OfficeO365ProPlusR_Subscription3 edition
Office 15, OfficeO365ProPlusR_Subscription4 edition
Office 15, OfficeO365ProPlusR_Subscription5 edition
Office 15, OfficeO365ProPlusR_SubTrial1 edition
Office 15, OfficeO365ProPlusR_SubTrial2 edition
Office 15, OfficeO365ProPlusR_SubTrial3 edition
Office 15, OfficeO365ProPlusR_SubTrial4 edition
Office 15, OfficeO365ProPlusR_SubTrial5 edition
Office 15, OfficeO365SmallBusPremR_Grace edition
Office 15, OfficeO365SmallBusPremR_Retail edition
Office 15, OfficeO365SmallBusPremR_Subscription1 edition
Office 15, OfficeO365SmallBusPremR_Subscription2 edition
Office 15, OfficeO365SmallBusPremR_Subscription3 edition
Office 15, OfficeO365SmallBusPremR_Subscription4 edition
Office 15, OfficeO365SmallBusPremR_Subscription5 edition
Office 15, OfficeO365SmallBusPremR_SubTrial1 edition
Office 15, OfficeO365SmallBusPremR_SubTrial2 edition
Office 15, OfficeO365SmallBusPremR_SubTrial3 edition
Office 15, OfficeO365SmallBusPremR_SubTrial4 edition
Office 15, OfficeO365SmallBusPremR_SubTrial5 edition
Office 15, OfficeProjectProCO365R_Subscription edition
Office 15, OfficeProjectProCO365R_SubTest edition
Office 15, OfficeProjectProCO365R_SubTrial edition
Office 15, OfficeProjectProDemoR_BypassTrial180 edition
Office 15, OfficeProjectProMSDNR_Retail edition
Office 15, OfficeProjectProO365R_Subscription edition
Office 15, OfficeProjectProO365R_SubTest edition
Office 15, OfficeProjectProO365R_SubTrial edition
Office 15, OfficeProjectProR_Grace edition
Office 15, OfficeProjectProR_OEM_Perp edition
Office 15, OfficeProjectProR_Retail edition
Office 15, OfficeProjectProR_Trial edition
Office 15, OfficeProPlusDemoR_BypassTrial180 edition
Office 15, OfficeProPlusMSDNR_Retail edition
Office 15, OfficeProPlusR_Grace edition
Office 15, OfficeProPlusR_OEM_Perp edition
Office 15, OfficeProPlusR_Retail edition
Office 15, OfficeProPlusR_Trial edition
Office 15, OfficeVisioProCO365R_Subscription edition
Office 15, OfficeVisioProCO365R_SubTest edition
Office 15, OfficeVisioProCO365R_SubTrial edition
Office 15, OfficeVisioProDemoR_BypassTrial180 edition
Office 15, OfficeVisioProMSDNR_Retail edition
Office 15, OfficeVisioProO365R_Subscription edition
Office 15, OfficeVisioProO365R_SubTest edition
Office 15, OfficeVisioProO365R_SubTrial edition
Office 15, OfficeVisioProR_Grace edition
Office 15, OfficeVisioProR_OEM_Perp edition
Office 15, OfficeVisioProR_Retail edition
Office 15, OfficeVisioProR_Trial edition

which was a lot more than I expected.

It is possible to use WMI to set the product key – use the SoftwareLicensingService class

My capacity planning series on the Scripting Guy blog finished last week. Didn’t get chance to post about it as I was at Microsoft in Seattle.

Full series and associated powertip postings:

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/18/powertip-compare-the-contents-of-files-with-powershell.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/18/the-admin-s-first-steps-capacity-planning-part-3.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/11/powertip-use-powershell-to-format-dates.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/11/the-admin-s-first-steps-capacity-planning-part-2.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/04/powertip-view-network-statistics-with-powershell.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/04/the-admin-s-first-steps-capacity-planning.aspx

Enjoy

To create a named range in an Excel spreadsheet

$xl = New-Object -ComObject ‘Excel.Application’
$wkbk = $xl.Workbooks.Add()
$sheet = $wkbk.WorkSheets.Item(1)
$range = $xl.Range("A1", "D4")
$range.Name = "Test"

Just to show how to work with named ranges

$range2 = $xl.Range("Test")
$range2.Borders.Color=0
$range2.Borders.ColorIndex=26
$range2.Borders.Weight=2
$xl.visible = $true

Just been puzzling out why I haven’t been getting any RSS feeds for a few days.  Looks like when I hooked up my Office365 account to Outlook it took out all the RSS feeds.  Fun time to come putting them back