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.
Open any Excel file and go to File > Info. You should see something like that on the right side:
When you look at the bottom there is a link to Show All Properties. Click it for more details:
In addition, we could access those with Powershell as well.
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:
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.
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
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
To sum up, I have created an Excel file without scrollbars and without tabs. All of that happened… tomorrow!