PowerShell – Read Excel files using COM Interface

PowerShell – Read Excel files using COM Interface

Recently, I had to process some information available in Excel sheets and ended up using PowerShell, which turned out to be a pretty easy to use solution.

PowerShell is now cross-platform, but this example uses the COM interface and will work only on Windows platform.

You will create a new COM object which will have the below structure:

  • Application
  • WorkBook
  • WorkSheet
  • Range

Your required data is mostly read from Range object.

Below snippet will create a new instance of Excel.

$excelObj = New-Object -com excel.application

A WorkBook can be obtained by passing the file name to the open method

$excelWb = $excelObj.workbooks.Open("C:\ExcelSampleFile.xlsx")

The file can be opened in read only mode by specifying extra parameters. Refer to the open() method documentation for more details. Second parameter specifies the way links in file are updated. And the third parameter if true, will open the file in read-only mode.

$excelWb = $sales.workbooks.Open("C:\ExcelSampleFile.xlsx",2,$true)

If you just need to open the last modified Excel file in the same folder as the PowerShell script, an easy option would be as below:

$excelFile = Get-ChildItem *.xlsx | Get-Item | sort LastWriteTime | Select-Object -Last 1
$excelWb = $sales.workbooks.Open($excelFile,2,$true)

Lets get the WorkSheet we want to work with, in this case get the first sheet.

$excelSheet = $excelWb.Sheets.Item(1)

Once you get a reference to the sheet, data can be accessed using several ways as shown below:

$excelSheet.Range("A3").Text
$excelSheet.Range("A3:A3").Text
$excelSheet.Range("A3","A3").Text
$excelSheet.UsedRange.Range("A3").Text
$excelSheet.Columns.Item(1).Rows.Item(3).Text
$excelSheet.Rows.Item(3).Columns.Item(1).Text
$excelSheet.cells.Item(1, 3).text
$excelSheet.cells.Item(1, 3).value2

To get the maximum used row in the sheet, below snippet can be used.

$rowMax = ($excelSheet.UsedRange.Rows).count

You can close the WorkBook using the close() method. The second parameter specifies whether changes are to be saved. Since we aren’t trying to modify the file, let’s pass false as the second argument and also quit() the Excel application opened.

$excelWb.Close($false)
$excelObj.quit()

Let us perform some cleanup too – to make sure that COM object references are freed up correctly. Credits to StackOverflow

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject(excelSheet)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject(excelObj)

Remove-Variable -Name sales

Leave a Reply

Your email address will not be published. Required fields are marked *