How to change Excel metadata using PowerShell?

ImportExcel - PowerShell module

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

So far we have been working on data or worksheets, but today we will look closer at some workbook-level properties and metadata.

Workbook properties

Open any Excel file and go to File > Info. You should see something like that on the right side:

Excel workbook properties – short version

When you look at the bottom there is a link to Show All Properties. Click it for more details:

Excel workbook properties – long version

In addition, we could access those with Powershell as well.

Preparation

Firstly, we are going to prepare our environment now to make sure the file does not exist.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
#Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo008.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

# random workbook
1..100 | Export-Excel -WorksheetName HowToMetadata -Path $excelFiles

After that, we will check what information can be pulled with the ImportExcel module. For this, we are going to use Get-ExcelWorkbookInfo function.

# read the workbook properties
Get-ExcelWorkbookInfo -Path $excelFiles

<#
CorePropertiesXml     : #document
Title                 : 
Subject               : 
Author                : 
Comments              : 
Keywords              : 
LastModifiedBy        : 
LastPrinted           : 
Created               : 01/01/0001 00:00:00
Category              : 
Status                : 
ExtendedPropertiesXml : #document
Application           : 
HyperlinkBase         : 
AppVersion            : 
Company               : 
Manager               : 
Modified              : 01/01/0001 00:00:00
LinksUpToDate         : False
HyperlinksChanged     : False
ScaleCrop             : False
SharedDoc             : False
CustomPropertiesXml   : #document
#>

As we can see there is not much of a detail there. Let’s change it.

Changing Excel workbook properties with PowerShell

The module’s capability at the moment does not allow to directly update the metadata of the workbook, so we are going to dig a little bit deeper. The properties are hiding behind Workbook.Properties class. Running the following PowerShell piece will produce result similar to the one above.

# open Excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles

# see the workbook properties
$excelPackage.Workbook.Properties

Additionally, if we want to update some of the properties we can use the method below. Also, you may notice the pattern here, I will explain why it’s here in a second.

# updating some of the 
$excelPackage.Workbook.Properties.Title = 'Back to the future'
$excelPackage.Workbook.Properties.Subject = 'DMC DeLorean'
$excelPackage.Workbook.Properties.Author = 'Marty McFly'
$excelPackage.Workbook.Properties.Comments = 'No comments'
$excelPackage.Workbook.Properties.Keywords = 'back to the future'
$excelPackage.Workbook.Properties.Category = 'Time travel'
$excelPackage.Workbook.Properties.Created = (Get-date).AddDays(1)
$excelPackage.Workbook.Properties.Modified = '11/05/1955 12:34:56'
$excelPackage.Workbook.Properties.LastModifiedBy = 'Emmett Brown, Dr'
$excelPackage.Workbook.Properties.Company = 'Universal Studios'
$excelPackage.Workbook.Properties.HyperlinkBase = 'M:\'
$excelPackage.Workbook.Properties.Status = 'In transition'
$excelPackage.Workbook.Properties.Manager = 'Robert Zemeckis'er worksheets without overwriting the data

# close the package and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Show

Firstly, before we open the Properties, let’s have a look at what Get-ExcelWorkbookInfo can tell us about the workbook:

# read the workbook properties after changes been made
Get-ExcelWorkbookInfo -Path $excelFiles

<#
CorePropertiesXml     : #document
Title                 : Back to the future
Subject               : DMC DeLorean
Author                : Marty McFly
Comments              : No comments
Keywords              : back to the future
LastModifiedBy        : Emmett Brown, Dr
LastPrinted           : 
Created               : 09/02/2021 02:18:06
Category              : Time travel
Status                : In transition
ExtendedPropertiesXml : #document
Application           : 
HyperlinkBase         : file:///M:/
AppVersion            : 
Company               : Universal Studios
Manager               : Robert Zemeckis
Modified              : 05/11/1955 12:34:56
LinksUpToDate         : False
HyperlinksChanged     : False
ScaleCrop             : False
SharedDoc             : False
CustomPropertiesXml   : #document
#>

That looks fine at first glance. Finally, we are ready to open the Properties and be amazed:

Excel properties after updates

Looks much better, but… hold on! Can you spot the odd thing/things? That’s right Last Modified date is before Created date. Even better, the Created date says Tomorrow (sic!). Not sure about you, but I always wanted to travel in time and looks like we had a little success here.

Bonus: ultimateHidden

Earlier in the series, we were hiding (and very hiding) tabs. Guess what? We can hide all of them.

# open the excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# ultimately hide tabs
$excelPackage.Workbook.View.ShowSheetTabs = $false

# save changes and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Show
All tabs are hidden

In the same way we will get rid of the scrollbars:

# open the excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# ultimately hide tabs
$excelPackage.Workbook.View.ShowVerticalScrollBar = $false
$excelPackage.Workbook.View.ShowHorizontalScrollBar = $false

# save changes and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Show
Hidden scrollbars

Summary

To sum up, I have created an Excel file without scrollbars and without tabs. All of that happened… tomorrow!

Thank you,
Mikey

Leave a Reply

%d bloggers like this: