To get the value of last cell used in Excel

Question

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$WorkBook = $objExcel.Workbooks.Open($filepath)
$WorkBook.Sheets | Select-Object -Property Name
$WorkSheet = $WorkBook.Sheets.Item($sheetname)
$worksheetrange = $WorkSheet.UsedRange
$last = $worksheetrange.Rows.Count

I have used 3 rows and getting an answer 3 which is true, I wanted to get the value present in 3rd row and as we keep on adding the row, how to append?

Answer 1

This would do it, just change the Sheet1 for your actual Sheet:

LastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row 
'This will find the last used row on column A of Sheet1

value = Sheet1.cells(LastRow,1).value 
'Place the value of that cell into a variable

Msgbox value 
'Display the variable to the user in a msgbox

Answer 2

If you want to stick with powershell. try this..

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$WorkBook = $objExcel.Workbooks.Open($filepath)
$WorkSheet = $objExcel.WorkSheets.item($sheetname)
$WorkSheet.activate()

[int]$lastRowvalue = ($WorkSheet.UsedRange.rows.count + 1) - 1
$lastrow = $WorkSheet.Cells.Item($lastRowvalue, 1).Value2
write-host $lastrow

$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | out-null

I'm assuming you already specify $filepath and $sheetname. Don't forget to close the excel after every run.