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
