Thursday 14 March 2013

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

2 comments:

  1. Thank you so much for this post. Quick question: in the first script ("Reading the built-in comments property") you say "Get the Property items for each of the three builtin properties we're using". However, this call would only return a reference to the Comments property, am I correct? Thanks so much.

    ReplyDelete
    Replies
    1. Thanks for pointing that out Return2ankara! I'd copied (then modified) the code from "And the full script" section, but hadn't updated the comments. I've updated it now!

      Delete