How to get specific number of rows from Excel in powershell

Question

I need to get first 35 rows (including empty rows) from column A to a variable. I looked in the internet but I cannot find the answer anywhere. $data = $worksheet.Range("A1:A35").text returns only cell A1. I tried with Cell.item etc. but with no success. Does anyone know how to extract cell range A1:A35 from excel into variable and save it to the text file? Thanks in advance.

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.open("*PATH_TO_THE_FILE*.xlsx")
$worksheet = $workbook.sheets.item("MatrixFill") 
$data = $worksheet.Range("A1:A35").text
$excel.Quit()

Answer 1

the text property you are trying to access is actually an object, so you have to treat it as such. Also for ranges you will need to use a , instead of :. Below will give you what you need. It worked for me when I tested it.

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.open("*PATH_TO_THE_FILE*.xlsx")
$worksheet = $workbook.sheets.item("MatrixFill") 
$worksheet.Range("A1","A35") | select -expand text |out-file "textfilename.txt"
$excel.Quit()

or

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.open("*PATH_TO_THE_FILE*.xlsx")
$worksheet = $workbook.sheets.item("MatrixFill") 
$data = $worksheet.Range("A1","A35") | select -expand text 
$data | out-file "textfilename.txt"
$excel.Quit()