Copy Excel Worksheet to Another Workbook using PowerShell

Tagged: PowerShell

Problem#

In this blog post, I will show you how to copy worksheet to another workbook using PowerShell.

As the context, the worksheet that will be copied will look as follows:

This worksheet will be copied as a new worksheet in the destination workbook.

Using Excel Com Object#

To copy the worksheet, we can use Copy method from the source worksheet object. The argument of this method is the worksheet in destination workbook. In this case, the copied worksheet is placed as the first worksheet (1-based index).

try {
    # Create excel object
    $excel = New-Object -ComObject Excel.Application
    
    # Specify worksheet that will be copied
    $workbookSource = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")       
    $worksheetSource = $workbookSource.Worksheets.Item(1)

    # Specify worksheet location in destination workbook
    $workbookDestination = $excel.Workbooks.Open("C:\Scripts\Book1.xlsx") 
    $worksheetDestination = $workbookDestination.Worksheets.Item(1)

    # Copy worksheet from source to destination workbook
    $worksheetSource.Copy($worksheetDestination)

    # Save excel file
    $workbookDestination.Save()    
}
finally {
    # Close excel file
    $workbookSource.Close()
    $workbookDestination.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 popular library to work with Excel spreadsheets in .NET.

Before using ImportExcel module, we have to install it.

Install-Module -Name ImportExcel

To copy worksheet using this module, we can use Add method from Worksheets collection object in the destination excel object. There are two parameters where the first one is the name of the worksheet in destination workbook and the second one is the source worksheet.

The difference with previous solution is in this solution we cannot specify the position where the copied worksheet will be placed. So, it will be automatically placed at the last position.

try {
    # Import the module
    Import-Module ImportExcel

    # Specify worksheet that will be copied
    $excelSource = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"
    $currentWorksheetIndex = $excelSource.Workbook.View.ActiveTab
    $worksheetSource = $excelSource.Workbook.Worksheets[$currentWorksheetIndex + 1]

    # Copy worksheet from source to destination workbook
    $excelDestination = Open-ExcelPackage -Path "C:\Scripts\Book1.xlsx"
    $excelDestination.Workbook.Worksheets.Add("Copied worksheet", $worksheetSource)
}
finally {
    # Close excel file
    Close-ExcelPackage $excelSource    
    Close-ExcelPackage $excelDestination
}

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

This solution is basically the same with previous solution since the module is based on EPPlus as well. They only differ in cmdlets that are used to create excel object.

try {
    # Import the module
    Import-Module PSExcel

    # Specify worksheet that will be copied
    $excelSource = New-Excel -Path 'C:\Scripts\Test.xlsx'
    $currentWorksheetIndex = $excelSource.Workbook.View.ActiveTab
    $worksheetSource = $excelSource.Workbook.Worksheets[$currentWorksheetIndex + 1]

    # Copy worksheet from source to destination workbook
    $excelDestination = New-Excel -Path "C:\Scripts\Book1.xlsx"
    $excelDestination.Workbook.Worksheets.Add("Copied worksheet", $worksheetSource)

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

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

Conclusion#

To copy worksheet to another workbook using PowerShell, we can use excel module/libraries.

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.