Hide Excel Worksheets Using PowerShell

Tagged: PowerShell

Problem#

In this blog post, we will show you how to hide Excel worksheets using PowerShell.

As the context, our original worksheets will look as follows:

Then, after hiding all the sheets except the fourth one (Sheet4), the worksheets will look as follows:

Using Excel Com Object#

After creating the object, we will open the Excel file to get workbook object we want to manipulate. Then, we hide current worksheet (the first one) until the third one by setting Visible property of the worksheet to $false.

try {
    # Open excel file
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
    
    # Hide worksheets
    $workbook.ActiveSheet.Visible = $false # $workbook.Worksheets(1).Visible = $false
    $workbook.Worksheets(2).Visible = $false
    $workbook.Worksheets(3).Visible = $false
    
    # Save excel file
    $workbook.Save()    
}
finally {
    # Close excel file
    $workbook.Close()

    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)    
}

We enclose the code above with try-finally block because we want to make sure it will release the resource after modifying Excel that is performed at finally block.

Using ImportExcel Module#

ImportExcel is an external module that is built based on EPPlus which is a very well-known library to work with Excel spreadsheets in .NET.

Before using ImportExcel module, we have to install it.

Install-Module -Name ImportExcel

And then following previous solution pattern, we have to create Excel object first before hiding the sheet using Hidden property.

There is a caveat for this module that we cannot hide active worksheet unless we use VeryHidden enum value. This is the same behavior derived from EPPlus which is the underlying library of this module.

try {
    # Import the module
    Import-Module ImportExcel

    # Open excel file
    $excel = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"
    
    # Hide worksheets
    $excel.Workbook.Worksheets[2].Hidden = [OfficeOpenXml.eWorkSheetHidden]::Hidden
    $excel.Workbook.Worksheets[3].Hidden = [OfficeOpenXml.eWorkSheetHidden]::Hidden
}
finally {
    # Close excel file
    Close-ExcelPackage $excel    
}

VeryHidden will not allow user to unhide the sheet, but we can unhide programatically by using Visible.

Similar to previous solution, we enclose the script with try-finally block to avoid memory leak.

Using PSExcel Module#

PSExcel is another module based on EPPlus library. You can find all the examples in github repository.

Before using this module, we have to install it.

Install-Module -Name PSExcel

In order to use the object, we must import the module first. Then, we create Excel object by specifying the path of our Excel file. This object will be used to get worksheet object that will be hidden by setting Hidden property to [OfficeOpenXml.eWorkSheetHidden]::Hidden.

There is a caveat for this module that we cannot hide active worksheet unless we use VeryHidden enum value. This is the same behavior derived from EPPlus which is the underlying library of this module.

try {
    # Import the module
    Import-Module PSExcel

    # Open excel file
    $excel = New-Excel -Path 'C:\Scripts\Test.xlsx'

    # Hide worksheets
    $excel.Workbook.Worksheets[2].Hidden = [OfficeOpenXml.eWorkSheetHidden]::Hidden
    $excel.Workbook.Worksheets[3].Hidden = [OfficeOpenXml.eWorkSheetHidden]::Hidden

    # Save excel file
    $excel | Save-Excel
}
finally {
    $excel | Close-Excel
}

VeryHidden will not allow user to unhide the sheet, but we can unhide programatically by using Visible.

We also enclose the script with try-finally block to avoid memory leak.

Conclusion#

To hide Excel worksheets using PowerShell, we can use excel module/libraries to manipulate excel.

We can use Excel Com Object which is based on .NET Framework. We can also use PowerShell external modules like PSExcel and ImportExcel. ImportExcel is an excellent module which is based on EPPlus library, a popular C# libary for woking with Excel from .NET.

PSExcel is the alternative of ImportExcel module. PSExcel is also based on EPPlus but this module is no longer maintained as stated in its GitHub repository. If it doesn’t provide functionality that you need, you should contribute to the source code by yourself.

There is a caveat for both ImportExcel and PSExcel. These modules cannot hide active worksheet unless we use VeryHidden enum value. This is the same behavior derived from EPPlus which is the underlying library of this module.

VeryHidden will not allow user to unhide the sheet, but we can unhide programatically by using Visible.