Copy Paste Excel Range of Values from One Workbook to Another using PowerShell
Problem
In this article, we will show you how to copy paste range of values using PowerShell.
As the context, source of data we want to copy will look as follows:
And we will paste into destination workbook at cell B2 intentionally. It will look as follows:
Using Excel Com Object
Before copying the data, we must define the range of data in the source file. In this case, we use UsedRange property which means we use the range of data that already exists in the worksheet.
Therefore, we don’t need to define the range specifically like A1:E3. We use Copy method from the range object to store the source data to the memory before pasting it into the destination.
Then, we must also specify the location we want to paste in another workbook. In this case, we will paste at B2 on purpose to show that we can paste in different location compared to the source file. To paste to the destination, we use Paste method from the range object in destination workbook.
try {
# Create excel object
$excel = New-Object -ComObject Excel.Application
# Open the workbook we want to copy
# Get active worksheet from the workbook, then specify the range we want to copy
# In this case we copy the range that has data on it by default (UsedRange)
$workbookCopy = $excel.Workbooks.Open("C:\Scripts\Test.xlsx")
$sheetCopy = $workbookCopy.ActiveSheet
$rangeCopy = $sheetCopy.UsedRange
$rangeCopy.Copy()
# Open the workbook we want to paste
# We will paste on its active worksheet specifically on B2
$workbookPaste = $excel.Workbooks.Open("C:\Scripts\Book1.xlsx")
$sheetPaste = $workbookPaste.ActiveSheet
$rangePaste = $sheetPaste.Range("B2")
$sheetPaste.Paste($rangePaste)
# Save excel file that has been pasted
$workbookPaste.Save()
}
finally {
# Close excel file
$workbookCopy.Close()
$workbookPaste.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
This solution follows the same pattern as in previous solution. First, we need to define the range of data in the source workbook. Then, we also need to define the location we want to paste in the destination workbook.
The actual copy operation will be performed using Copy method from source excel range object while the destination excel range object is passed as the argument of the method.
try {
# Import the module
Import-Module ImportExcel
# Get active worksheet from the source workbook, then specify the range we want to copy
# In this case we copy the range that has data on it
$excelCopy = Open-ExcelPackage -Path "C:\Scripts\Test.xlsx"
$currentWorksheetIndex = $excelCopy.Workbook.View.ActiveTab
$sheetCopy = $excelCopy.Workbook.Worksheets[$currentWorksheetIndex + 1]
$rangeCopy = $sheetCopy.Cells[$sheetCopy.Dimension.Address]
# Open the workbook we want to paste
# We will paste on its active worksheet specifically on B2
$excelPaste = Open-ExcelPackage -Path "C:\Scripts\Book1.xlsx"
$currentWorksheetIndex = $excelPaste.Workbook.View.ActiveTab
$sheetPaste = $excelPaste.Workbook.Worksheets[$currentWorksheetIndex + 1]
$rangePaste = $sheetPaste.Cells["B2"]
# Copy excel range from the source to the destination
$rangeCopy.Copy($rangePaste)
}
finally {
# Close excel file
Close-ExcelPackage $excelCopy
Close-ExcelPackage $excelPaste
}
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.
First, we need to define the range of data in the source workbook. Then, we also need to define the location we want to paste in the destination workbook.
The actual copy operation will be performed using Copy method from source excel range object while the destination excel range object is passed as the argument of the method.
try {
# Import the module
Import-Module PSExcel
# Get active worksheet from the source workbook, then specify the range we want to copy
# In this case we copy the range that has data on it
$excelCopy = New-Excel -Path "C:\Scripts\Test.xlsx"
$currentWorksheetIndex = $excelCopy.Workbook.View.ActiveTab
$sheetCopy = $excelCopy.Workbook.Worksheets[$currentWorksheetIndex + 1]
$rangeCopy = $sheetCopy.Cells[$sheetCopy.Dimension.Address]
# Open the workbook we want to paste
# We will paste on its active worksheet specifically on B2
$excelPaste = New-Excel -Path "C:\Scripts\Book1.xlsx"
$currentWorksheetIndex = $excelPaste.Workbook.View.ActiveTab
$sheetPaste = $excelPaste.Workbook.Worksheets[$currentWorksheetIndex + 1]
$rangePaste = $sheetPaste.Cells["B2"]
# Copy excel range from the source to the destination
$rangeCopy.Copy($rangePaste)
# Save excel file that has been pasted
$excelPaste | Save-Excel
}
finally {
# Close excel file
$excelCopy | Close-Excel
$excelPaste | Close-Excel
}
We also enclose the script with try-finally block to avoid memory leak.
Conclusion
To copy paste range of values 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.