Home Blog Archive Single Blog

How to parse excel worksheets with Powershell

I get asked many times to write a script to parse Excel files

First , we need to tell Powershell what file we're interested in, and let it see its content.

$FilePath = "C:\Users\me\Documents\myexcelfile.xls"

We then need to create a new excel object ,so that PS know what kind of format the file will be in

$objExcel = New-Object -ComObject Excel.Application

After having created the objext, we can use a variable to store the content of the workbook in a variable:

$WorkBook = $objExcel.Workbooks.Open($FilePath)

We assign the sheets name to a variable like so:

$Overview = "Overview"
$positions= "Positions"

We then need to assign the excel file worksheets to a variable , and after that we can get the content of ecery cell by its name:

$Overview_sheet = $WorkBook.sheets.item($Overview)
$PositionSheet = $WorkBook.sheets.item($positions)
#ACCESS CELLS
#--- INVOICE NUMBER
$INVOICE_NUM = $Overview_sheet.Range("B6").Text
#INVOICE DATE
$INVOICE_DATE = $Overview_sheet.Range("B7").Text

YOU MAY ALSO LIKE

03 COMMENTS

Shuhein Chui, Nov 21, 2017

Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam.

LIKE REPLY
Shuhein Chui, Nov 21, 2017

Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam.

LIKE REPLY
Shuhein Chui, Nov 21, 2017

Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam.

LIKE REPLY

LEAVE A COMMENT