Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, November 8, 2016 4:23 AM
Hi,
I am able to search required string in stated column, but unable to get it's ROW and column number of that cell. it's returning relative cell-address.
$getName = $sheet1.Range("C1:C50").find("Test")
$cellAddress = $getName.Address($false,$false)
Write-Host $cellAddress
output: C11
I would like to get it in row number and column letter instead of relative cell-address so that, by using this row number and column letter, can use further to increase or decrease cell address and update/add data in to that cell.
Is there any specific command to get it row and column in which searched string is present.. I tried below but no luck:
$getcellrow = $getName.row Output: This just give me row number
$getcellcolumn = $getName.column Output: This just give me column number and not letter
All replies (14)
Tuesday, November 8, 2016 6:26 AM ✅Answered | 1 vote
Just ask for the row and column.
$row = $getName.Row
$column = $getName.Column
Excel Interop uses numbers and no letters to identify locations.
$sheet1.Cells.Item($row, $column + 1)
\(ツ)_/
Tuesday, November 8, 2016 6:59 AM
If you want the row letter, you can split the adress with Regular Expressions:
"C11" -split "(?<=[A-Z])(?=\d)"
Tuesday, November 8, 2016 7:08 AM
Thanks for quick reply.
by using above Row and Column, able to get correct data available in to that cell. but unable to set new value in the same cell. Am i missing something? I use below line:
$var = 12
$sheet1.cells.item($row,$column) = $var
Powershell output displays 12 but do not see same value updated in Excel.
Thanks,
Sudhir
Tuesday, November 8, 2016 7:18 AM
"Address" returns a relative address. A location is needed and it cannot be obtained by splitting anything.
$getName.AddressLocal($false,$false)
returns the absolute address but row/column are better to work with in code.
Cell addressing can obly done with row/column numerical format.
$sheets.Cells.Item(3,7)
\(ツ)_/
Tuesday, November 8, 2016 7:21 AM
$sheet1.cells.item($row,$column).Value2 = $var
\(ツ)_/
Tuesday, November 8, 2016 7:23 AM
Excellent, here my question come again; once we have relative address, how can we use it to update same cell or any other cell using this address?
As i mentioned earlier, using row and column, powershell displays correct value but same is not udpated in Excel. Please help.
Tuesday, November 8, 2016 7:31 AM
It's not working...powershell displays correct value but same is not updated in Excel.
Tuesday, November 8, 2016 7:32 AM
It is not a relative address. It is an exact address when you use the Row and Column properties of the cell.
\(ツ)_/
Tuesday, November 8, 2016 7:36 AM
It's not working...powershell displays correct value but same is not updated in Excel.
It works correctly for me and everybody else.
I recommend that you take some time to learn Excel and the Excel object model. Take it one step t a time. Learn what a Range object is, how "Find" works and other things. Learn about auto-calculate and protection and how they can make updates not work.
If you have no error then the cell you addressed was updated but the spreadsheet can change this. Since we don't have your sheets we cannot know what is happening.
\(ツ)_/
Tuesday, November 8, 2016 7:50 AM
Really weird....please see below code and let me know where am i going wrong...
$excel = New-Object -ComObject excel.application
$destinationPath = "C:\Copy value\Test.xlsx"
$workbook = $excel.Workbooks.Open($destinationPath)
$sheet1 = $workbook.WorkSheets.item("sheet1")
$sheet1.activate()
$getName = $sheet1.Range("C1").EntireColumn.find("Test")
$row = $getName.Row
$column = $getName.Column
$var1 = 67
$sheet1.cells.item($row,$column).Value2 = $var1
$getcellValue = $sheet1.cells.item($row,$column).text
write-host $getcellValue[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Stop-Process -Name EXCEL -Force
Output displays 67 but does not get updated in Excel
Thanks,
Sudhir
Tuesday, November 8, 2016 7:58 AM
You haven't' saved the workbook. No updates will work if you use Stop-Process.
Use
$workbook.Save()
$workbook.Close()
$excel.Quit()
Releasing only one object will not work.
You need to learn Excel. Your original question has been answered. To learn Excel post in the Excel developers forum.
\(ツ)_/
Tuesday, November 8, 2016 8:05 AM
OMG....I was trying this much of code in new script and hence forgot to add .save (), .close() methods, due to which i was reading/ looping/ thinking in that part only..didn't came from this box...really my BAD...and I really appreciate your time and prompt response on this question.
THANK YOU SO MUCH! KEEP THE SAME...
Thanks,
Sudhir
Tuesday, November 8, 2016 12:12 PM
Cell addressing can obly done with row/column numerical format.
$sheets.Cells.Item(3,7)
By the way, this is not really true. You can adress cells via
$worksheet.Range("A2")
which can be comfortable if you have non-changing positions.
Tuesday, November 8, 2016 3:22 PM
Cell addressing can obly done with row/column numerical format.
$sheets.Cells.Item(3,7)
By the way, this is not really true. You can adress cells via
$worksheet.Range("A2")which can be comfortable if you have non-changing positions.
It does not return a cell it returns a range object. Range objects can be addressed as a single cell but it still is not a way to design code that walks through a sequence of cell addresses. You cannot address a "Cell" in that way.
Excel is tricky. A great many posts and blogs poorly demonstrate how to use the Excel object model. VBS and PowerShell do not see the object model in the same way because one is using Interop.
\(ツ)_/