-2
$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 wanted to read the value 17/11/2017 from the image i.e the last row in column A

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?

Community
  • 1
  • 1
Powershell learner
  • 35
  • 1
  • 4

3 Answers3

3

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.

ShanayL
  • 1,217
  • 2
  • 14
  • 29
2

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
Matt
  • 45,022
  • 8
  • 78
  • 119
Xabier
  • 7,587
  • 1
  • 8
  • 20
  • 2
    While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Clijsters Nov 30 '17 at 12:11
  • 1
    My only concern is that this code snippet is VB (Macro, Visual Basic), but the original question is looking for a PowerShell example. – Aaron C May 14 '19 at 19:38
2

The method provided by Shanayl does works fine but bear in mind that its using the count method which doesn't count blanks. Below one does:

$filepath = "F:\Drives.xlsx"
$xl=New-Object -ComObject "Excel.Application"  
$wb=$xl.Workbooks.open($filepath)
$ws=$wb.ActiveSheet 
$cells=$ws.Cells

$Lastrowno =$Ws.UsedRange.SpecialCells(11).row
Adam Marshall
  • 3,010
  • 9
  • 42
  • 80
SAKA UK
  • 39
  • 4