Reading, Updating and Adding Custom or Built-in Office Document Properties

I really love PowerShell (though I regularly curse a lot when I'm debugging it). It's great for all those tasks where you don't want to do something manually, can't afford the time to write program, but can spend a few minutes knocking out a script!

This post is about writing custom and built-in properties to office documents, and writing an entry for each template to a database.

My brief was to update approximately 1000 templates;

• Read the built-in comments property, and check if it has an existing version string, in the format v#
• Update the built-in author and company properties, and clear the comments property
• Add the template to a database table, using a stored procedure that returns the new unique ID for the template (unique row ID)
• Create two new custom properties that will store the template unique id and version

The script contains three functions, one for adding the template to the database, Add-TemplateToDatabase, one for returning an physical office location id (based on the templates file path), Get-OfficeCode, and the main function that processes each template, Update-Template.

Finally, we get a collection of templates, loop through them, sending each template to the Update-Template function based on the file type.

Before looking at the full script, here's the bits that do the reading and writing of properties:

Reading the built-in comments property:

$wordApplication = New-Object -ComObject word.application
$document = $application.documents.open("c:\document.docx");
$binding = "System.Reflection.BindingFlags" -as [type];        
$builtinProperties = $document.BuiltInDocumentProperties
$pComments = "Comments"
[Array]$commentsArgs = $pComments
$builtinPropertiesType = $builtinProperties.GetType()
#Get the Property item for the Comments property
$commentsProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $commentsArgs)
#Get the value of the Comments property, so we can check if it contains a version string.
$commentsPropValue  = [System.__ComObject].InvokeMember("value",$binding::GetProperty,$null,$commentsProp,$null);
$document.Saved = $false;
$document.save();
$wordApplication.Quit();

Writing a new value to the built-in comments property

$wordApplication = New-Object -ComObject word.application
$document = $application.documents.open("c:\document.docx");
$binding = "System.Reflection.BindingFlags" -as [type];        
$builtinProperties = $document.BuiltInDocumentProperties
$pComments = "Comments"
$pCommentsValue = "Add this comment to the comments property";
[Array]$commentsArgs = $pComments
$builtinPropertiesType = $builtinProperties.GetType()
#Get the Property items for each of the three builtin properties we're using
$commentsProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $commentsArgs)
[Array]$commentsArgs = $pCommentsValue;
$builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $commentsProp, $commentsArgs)
$document.Saved = $false;
$document.save(); 
$wordApplication.Quit();

Creating / Updating a custom property

$wordApplication = New-Object -ComObject word.application
$document = $application.documents.open("c:\document.docx");
$binding = "System.Reflection.BindingFlags" -as [type];   
$customProperties = $document.CustomDocumentProperties
$typeCustomProperties = $customProperties.GetType()
#Define the two new properties and their values
$cpUid = "templateuid"
$cpUidValue = [int]$newUid;
$cpVersion = "templateversion"
$cpVersionValue = $currentversion;       
[array]$arrayArgsC = $cpUid,$false, 1, $cpUidValue;
[array]$arrayArgsD = $cpVersion,$false, 1, $cpVersionValue       
Try
{
    #Add the properties to the template
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) | out-null
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) | out-null
}
Catch [system.exception]
{
    #If the property already existed, get the property, delete the property, then readd it.
    $propertyObject1 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpUid)
    $propertyObject2 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpVersion)
    $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject1,$null)
    $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject2,$null)
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) |  Out-Null
    $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) |  Out-Null
} 
$document.Saved = $false;
$document.save();
$wordApplication.Quit();
 

And the full Script:

function Add-TemplateToDatabase
{
  Param(
    [String]$FileName,
    [String]$FilePath,
    [Int]$TemplateVersion,
    [Int]$OfficeUid  
  )  
  
  #Connect to the database
  $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=sqlserver; Initial Catalog=database; Integrated Security=SSPI")
  $conn.Open();
  $newRecordId = -1;
  $a = $null
  try
  {
    #Create the SQL command, and assign it with the parameters
    $cmd = $conn.CreateCommand()
    $cmd.CommandType = "StoredProcedure"
    $cmd.CommandText ="spOI_AddNewTemplate"
    $cmd.Parameters.AddWithValue("@FileName", [String]$FileName) | out-null;
    $cmd.Parameters.AddWithValue("@FilePath", [String]$FilePath) | out-null;    
    $cmd.Parameters.Add("@CurrentVersion", [System.Data.SqlDbType]"Int") | out-null;
    $cmd.Parameters["@CurrentVersion"].Value = $TemplateVersion;      
    $cmd.Parameters.Add("@OfficeId", [System.Data.SqlDbType]"Int") | out-null;
    $cmd.Parameters["@OfficeId"].Value = $OfficeUid;  
    $cmd.Parameters.Add("@Uid", [System.Data.SqlDbType]"Int")  | out-null;
    $cmd.Parameters["@Uid"].Direction = [System.Data.ParameterDirection]"Output";
    #Execute the query
    $output = $cmd.ExecuteNonQuery();
    #Extract the new UID for the template from the output parameter, @Uid
    $op = $cmd.Parameters.Item("@Uid");
    $newRecordId = $op.SqlValue.Value;
    $cmd.Dispose();  
  }
  catch [System.Exception]{
    $a = $_;
  }
  finally{
    $conn.Close();  
  }  
  #Return the templates new database Uid
  return $newRecordId;
}

function Get-OfficeCode
{
  Param(
    [String]$FilePath
  )

  if($FilePath.EndsWith("\") -eq $false)
  {
    $FilePath = [String]::Format("{0}\", $FilePath);
  }  
  if($FilePath.Contains("UK"))
  {
    return 1;
  }
  if($FilePath.Contains("DE"))
  {
    return 2;
  }
  if($FilePath.Contains("FR"))
  {
    return 3;
  }  
}

function Update-Template
{
  Param(
    [Parameter(Mandatory=$true)]$application,
    [Parameter(Mandatory=$true)]$file    
  )
  
  $document = $null;
  switch ($application.ToString())
  {
    "Microsoft.Office.Interop.Excel.ApplicationClass"
      {$document = $application.Workbooks.Open($file.FullName);}
    "Microsoft.Office.Interop.PowerPoint.ApplicationClass"
      {$document = $application.Presentations.Open($file.FullName);}
    "Microsoft.Office.Interop.Word.ApplicationClass"
      {$document = $application.documents.open($file.FullName);}      
    default
      {return "Application not supported. Use either Excel.Application, PowerPoint.Application or Word.Application."; }
  }
  
  try{
    $binding = "System.Reflection.BindingFlags" -as [type];    
    $builtinProperties = $document.BuiltInDocumentProperties
    $pComments = "Comments"
    #After getting comments, we're going to clear out the comments property, so we set the value to ""
    $pCommentsValue = "";
    $pAuthor = "Author";
    $pAuthorValue = "Ince & Co";
    $pCompany = "Company";
    $pCompanyValue = "Ince & Co";
    [Array]$commentsArgs = $pComments
    [Array]$authorArgs = $pAuthor;    
    [Array]$companyArgs = $pCompany;    
    $builtinPropertiesType = $builtinProperties.GetType()
    #Get the Property items for each of the three builtin properties we're using
    $commentsProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $commentsArgs)
    $authorProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $authorArgs)
    $companyProp = $builtinPropertiesType.InvokeMember("Item", $binding::GetProperty, $null, $builtinProperties, $companyArgs)
    #Get the value of the Comments property, so we can check if it contains a version string.
    $commentsPropValue  = [System.__ComObject].InvokeMember("value",$binding::GetProperty,$null,$commentsProp,$null);
    #Set the default value for the templates version.
    $currentversion = 1;
    #If the comments section is not null or empty, look for the version string, strip off the "v" and cast the resulting string to an int.
    if(![String]::IsNullOrEmpty($commentsPropValue))
    {
      Write-Host "Document"$file.Name"comments property is: $commentsPropValue" -foregroundcolor Yellow;
      if($commentsPropValue.Trim().StartsWith("v"))
      {
        try
        {
          $currentversion = ([Int32]::Parse($commentsPropValue.Trim().SubString(1)))++;
          Write-Host "Document"$file.Name" had a custom version, $currentversion, that will be used." -foregroundcolor DarkYellow;
        }
        catch
        {
          Write-Host "Error in Document"$file.Name": couldn't convert version to an integer $commentsPropValue" -foregroundcolor Red;
          $currentversion = 1;
        }
      }
    }  
    
    #
    [Array]$commentsArgs = $pCommentsValue;
    [Array]$authorArgs = $pAuthorValue;
    [Array]$companyArgs = $pCompanyValue;    
    try
    {
      $builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $commentsProp, $commentsArgs)
      $builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $authorProp, $authorArgs)
      $builtinPropertiesType.InvokeMember("Value", $binding::SetProperty,$null, $companyProp, $companyArgs)
    }
    catch
    {    
    Write-Host "Error writing company and author properties";
    }
    
    #The templates folder stucture contains a directory that indicates a offices a template is used in. Here we get the office Location string derived from the template path
    $officecode = Get-OfficeCode -FilePath $file.Directory.FullName;
    $newUid = -1;    
    #Insert a record of the template into the database, and store the return value (which is the unique id of the template created when it was added to the database)
    $newUid = Add-TemplateToDatabase -FileName $file.Name -FilePath $file.Directory.FullName -TemplateVersion $currentversion -OfficeUid $officecode;
    Write-Host $newUid.ToString();
    $message = [String]::Format("Template {0} added to the database with UID: {1}", $file.Name, $newUid.ToString());
    Write-Host $message -foregroundcolor green;
    #return;
    
    #Now that we have the templates UID, and version, we can write them to two new custom properties of the documents
    #Get the custom properties
    $customProperties = $document.CustomDocumentProperties
    $typeCustomProperties = $customProperties.GetType()
    #Define the two new properties and their values
    $cpUid = "templateuid"
    $cpUidValue = [int]$newUid;
    $cpVersion = "templateversion"
    $cpVersionValue = $currentversion;    
    [array]$arrayArgsC = $cpUid,$false, 1, $cpUidValue;
    [array]$arrayArgsD = $cpVersion,$false, 1, $cpVersionValue    
    
    Try
    {
      #Add the properties to the template
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) | out-null
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) | out-null
    }
    Catch [system.exception]
    {
      #If the property already existed, get the property, delete the property, then readd it.
      $propertyObject1 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpUid)
      $propertyObject2 = $typeCustomProperties.InvokeMember("Item", $binding::GetProperty,$null,$customProperties,$cpVersion)
      $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject1,$null)
      $typeCustomProperties.InvokeMember("Delete", $binding::InvokeMethod,$null,$propertyObject2,$null)
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsC) |  Out-Null
      $typeCustomProperties.InvokeMember("add", $binding::InvokeMethod,$null,$customProperties,$arrayArgsD) |  Out-Null
    } 
    $document.Saved = $false;
    $document.save();  
    Write-Host "Document"$file.FullName"successfully updated, with version number $uid" -foregroundcolor Green;
  }
  catch
  {
    Write-Host "Error:$_" -ForegroundColor Red;
  }  
  $document.Close();
  $document = $null;
}

#Initialise all of the application objects
$wordApplication = New-Object -ComObject word.application
$powerpointApplication = New-Object -ComObject powerpoint.application
$excelApplication = New-Object -ComObject excel.application
#Get a list of templates, then pass them to the Update-Template function
$items = Get-ChildItem -Path C:\MyBigListOfTemplates -Recurse

#Loop through each of the items and call Update-Template, passing in the appropriate application object.
foreach ($i in $items){
  if($i.PSIsContainer){continue;}
  if($i.Name.ToLower().EndsWith("xlsx") -or $i.Name.ToLower().EndsWith("xltm") -or $i.Name.ToLower().EndsWith("xltx") -or $i.Name.ToLower().EndsWith("xls"))
  {
    Update-Template -application $excelApplication -file $i
  }
  if($i.Name.ToLower().EndsWith("potx") -or $i.Name.ToLower().EndsWith("pot"))
  {
    Update-Template -application $powerpointApplication -file $i
  }
  if($i.Name.ToLower().EndsWith("dotm") -or $i.Name.ToLower().EndsWith("dotx") -or $i.Name.ToLower().EndsWith("docx") -or $i.Name.ToLower().EndsWith("dot") -or $i.Name.ToLower().EndsWith("doc"))
  {
    Update-Template -application $wordApplication -file $i
  }
}
$wordApplication.Quit();
$powerpointApplication.Quit();
$excelApplication.Quit();

And out of interest / completeness, the stored procedure (that returns the Uid) looks like this:

CREATE PROCEDURE [dbo].[spOI_AddNewTemplate]   
  @FileName nchar(256), 
  @FilePath nchar(512),
  @CurrentVersion int = 1,
  @OfficeId int,
  @Uid int OUTPUT
AS
BEGIN  
  SET NOCOUNT ON;    
  Declare @tblOIInsertedId table (Id int not null)
  Insert into OfficeIntegrationTemplateVersions  
  ([FileName], FilePath, CurrentVersion,VersioningEnabled,Active,QuickPrintEnabled,FooterJpg,OfficeId)
  Output inserted.TemplateUid into @tblOIInsertedId(Id)
  values(@FileName,@FilePath,@CurrentVersion,1,1,0,0,@OfficeId)    
  Select @Uid = (Select Top(1) Id From @tblOIInsertedId) 
END