Powershell module: MS-Office

Functions

`get-msOfficeVersion`	Returns the «abbreviated» Office version number, for example `16`
`enable-msOfficeDeveloperTab`	Enable the Developer tab on the Ribbon.
`get-msOfficeComObject`	Gets a COM Object for automation of an Office application
`grant-msOfficeVBAAccess`	Grants access to the VBA object model by setting `AccessVBOM` to 1 under a certain registry key. (See Programmatic access to Visual Basic Project is not trusted])
`get-msOfficeInstallationRoot`	Returns the directory name (installation root) under which Office is installed. (see also this link)

get-msOfficeComObject

get-msOfficeComObject gets the application object of an Office product and allows to use the Object Model's COM interface:

$xls = get-msOfficeComObject excel
$xls.workbooks.add(…)

get-msOfficeComObject calls get-activeObject (of the COM PowerShell module) to get a COM object of the desired (running) office application. If the application is not running, a new object is created using new-object -com … and then, the application is made visible.

TODO: Can this function be combined with Excel's application.activateMicrosoftApp somehow?

Source code

MS-Office.psm1

#
#  V0.6
#
set-strictMode -version latest

function get-msOfficeVersion {
   return ( (get-item hklm:\Software\Classes\excel.application\curVer).getValue('')  -replace '.*\.(\d+)', '$1' )
}

function get-msOfficeRegRoot {
   return "hkcu:\Software\Microsoft\Office\$(get-msOfficeVersion).0"
}


function get-msOfficeProducts {

   return (new-object psObject -property ( [ordered] @{ name = 'Excel'     ; exe = 'excel.exe'   ; devTools = $true  })),
          (new-object psObject -property ( [ordered] @{ name = 'Access'    ; exe = 'msAccess.exe'; devTools = $false })),
          (new-object psObject -property ( [ordered] @{ name = 'Word'      ; exe = 'winWord.exe' ; devTools = $true  })),
          (new-object psObject -property ( [ordered] @{ name = 'Visio'     ; exe = 'visio.exe'   ; devTools = $false })),
          (new-object psObject -property ( [ordered] @{ name = 'Outlook'   ; exe = 'outlook.exe' ; devTools = $true  })),
          (new-object psObject -property ( [ordered] @{ name = 'PowerPoint'; exe = 'powerpnt.exe'; devTools = $true  }))
}

function enable-msOfficeDeveloperTab {

   param (
      [switch] $off
   )

   $regKeyOfficeRootV = get-msOfficeRegRoot

   foreach ($prod in get-msOfficeProducts | where-object devTools) {

      $regKeyOfficeApp ="$regKeyOfficeRootV/$($prod.name)"
      if (test-path $regKeyOfficeApp) {
         $value = 1
         if ($off) { $value = 0 }
         if (-not (test-path "$regKeyOfficeApp\Options")) {
            write-host "$regKeyOfficeApp\Options does not exist, creating it"
            new-item -path "$regKeyOfficeApp\Options"
         }
         set-itemProperty "$regKeyOfficeApp\Options" -name DeveloperTools -type dWord -value $value
      }
      else {
          write-host "Not found for $regKeyOfficeApp"
      }
   }
}

function grant-msOfficeVBAaccess {

   $regKeyOfficeRoot = get-msOfficeRegRoot

   foreach ($prod in get-msOfficeProducts | where-object devTools) {

      $regKeyOfficeApp ="$regKeyOfficeRoot/$($prod.name)"
      if (test-path $regKeyOfficeApp) {
         $secKey = "$regKeyOfficeApp/Security"
         if (test-path $secKey) {
            write-host "found: $secKey"
            set-itemProperty $secKey -name AccessVBOM -type dWord -value 1
         }
         else {
            write-host "not found: $secKey"
         }
      }
      else {
         write-host "not found $regKeyOfficeApp"
      }
   }
}

function get-msOfficeComObject {

   param (
      [string] $app
   )

   $progId = "$app.application"

   $officeObj = get-activeObject $progId
   if ($officeObj -eq $null) {
      write-debug "no obj found for $progId"
      $officeObj = new-object -com $progId
      if ($app -eq 'outlook') {
         #
         # Outlook does not have a .visible property on the application object!
         #
         # 6 = olFolderInbox
         #
         $officeObj.GetNamespace('MAPI').GetDefaultFolder(6).display()
      }
      else {
         $officeObj.visible = $true
      }
   }
   return $officeObj
}

function get-msOfficeInstallationRoot {

    foreach ($prod in (get-msOfficeProducts)) {
       $ret = Get-ItemPropertyValue "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\$($prod.exe)" path -errorAction ignore
       if ($ret -ne $null) {
           return $ret
       }
    }

    write-textInConsoleWarningColor "No installation root found for MS Office"
    return $null
}

Github repository ps-modules-MS-Office, path: /MS-Office.psm1

MS-Office.psd1

@{
   RootModule        = 'MS-Office.psm1'
   ModuleVersion     = '0.7'
   RequiredModules   = @(
      'COM'
   )
   FunctionsToExport = @(
      'get-msOfficeVersion',
      'enable-msOfficeDeveloperTab',
      'grant-msOfficeVBAaccess',
      'get-msOfficeComObject',
      'get-msOfficeRegRoot',
      'get-msOfficeInstallationRoot'
   )
   AliasesToExport   = @(
   )
}

Github repository ps-modules-MS-Office, path: /MS-Office.psd1

History

V.2	Add `get-msOfficeComObject`
V.3	Add `get-msOfficeInstallationRoot`
V.4	Add `-off` option to `enable-msOfficeDeveloperTab`.
V.6	Add `get-msOfficeRegRoot` in manifest.
V.7	If `app\Options` registry key does not exist when calling `enable-msOfficeDeveloperTab`, it is created.