Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I need to read cells values from a worksheet and i am referencing the following piece of code, however i don't get .Items() method.

Code Referred:

$objWorksheet.Cells.Item(2,1).Text

Also is there any other way we can read the cell values? Any suggestions/help would be highly appreciated.

Regards, Ketan

share|improve this question

closed as off-topic by Robert Lindgren Sep 8 at 17:48

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Programming questions not specific to SharePoint are off-topic here, but can be asked on Stack Overflow." – Robert Lindgren
If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers 3

I did a bit of searching, and found the below. If you're just looking to the read the values from the cell, any of the below should work. I was able to return values on them all.

$objWorksheet.Range("B1").Text

$objWorksheet.Range("B1:B1").Text

$objWorksheet.Range("B1","B1").Text

$objWorksheet.cells.Item(2, 1).text

$objWorksheet.cells.Item(2, 1).value2

$objWorksheet.Columns.Item(2).Rows.Item(1).Text

$objWorksheet.Rows.Item(1).Columns.Item(2).Text

$objWorksheet.UsedRange.Range("B1").Text

This site definately pointed me in the right direction: http://www.lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html

share|improve this answer

I believe it would be:

$VaribleObject = $objWorksheet.Cells.Item(2, 1).Value()

I hope this helps ;)

Reference:
http://blogs.msdn.com/b/sriram_reddy1/archive/2012/07/16/excel-with-powershell.aspx

share|improve this answer
    
Done using same way..still giving error $excelObj = New-Object -ComObject "Excel.Application" $wrkBook = $excelObj.Workbooks.Open("FILE_PATH") $wrkShtGroup = $wrkBook.Worksheets.Item(4) $wrkShtGroup.Cells.Items(2,2).Value() –  Ketan03 Aug 17 '12 at 11:43
    
Hi that really strange! I dont really use poweshell meself but I fond this that should help! goto page 378. It goes into detail far more than I could as I dont know what code you might have thats effecting the result! hope it helps –  ali Sharepoint Aug 17 '12 at 12:59
    
the code iv done is correct, make sure your worksheet is correct? so you have 4 sheets within the workbook and you want to access the fourth? also your accessing row 2, 1 would be the header. the second val is 2 which would be column 2! blogs.msdn.com/b/sriram_reddy1/archive/2012/07/16/… –  ali Sharepoint May 12 at 7:31

Try this

$objWorksheet.Cells.Item(1,2).Value2 = "New Value"
$objWorksheet.SaveAs("C:\Users\Public\test2.xlsx")
share|improve this answer

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Not the answer you're looking for? Browse other questions tagged or ask your own question.