CODEWITHSUNDEEP
excelpowershell
Worker1234
Worker1234

Reputation: 21

I am looking to change data in Excel from Powershell.

I need to find what row the data is in and then Copy it and all other rows below it, and move it to the top of the sheet. A cut and paste would work, but I cannot seem to find a function that allows me to select all the rows I need.

I have tried this so far.

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('C:\Users\xx\Test2\TestFile2.xlsx')
$Worksheet = $Workbook.Sheets.Item(1)
$worksheet.Activate()
$Found = $Worksheet.Cells.Find('Last Name')
[char] $col = $found.column + 64
[String] $value = $col + $found.row
$range = $worsheet.Range("$value :A4")

But then I get this

You cannot call a method on a null-valued expression.
At line:1 char:1
+ $range = $worsheet.Range("$value :A4")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
     + FullyQualifiedErrorId : InvokeMethodOnNull

I am fairly new to Powershell so any help is appreciated

Thank you!

Upvotes: 2

Views: 293

Answers (1)

Robert Dyjas
Robert Dyjas

Reputation: 5227

I'd strongly suggest to use ImportExcel module for this operation so you can manipulate objects directly in PowerShell. Let's say that we have a table like this:

+----------+---------+
| Column1  | Column2 |
+----------+---------+
| Value1   | a       |
| Value2   | b       |
| LastName | c       |
| Value3   | d       |
| Value4   | e       |
+----------+---------+

What we can do is to import it to PowerShell object and then find in which row there's value we're looking for (in my case it's Column1 -eq 'LastName'):

$data = Import-Excel .\Book1.xlsx

$i = 0
$data | % {if ($_.Column1 -eq "LastName"){
    $i
    break
  }
  else {
    $i++
  }
}

Then we have to save all the row from the top to one variable and the ones starting from the row we just found to another one: 

$above = @()
$thisAndBelow = @()
for ($j = 0; $j -lt $data.length; $j++) {
  if ($j -lt $i) {$above += $data[$j]}
  else {$thisAndBelow += $data[$j]
  }
}

Then just get it together and export to the output file:

$object = $thisAndBelow + $above
$object | Export-Excel .\Book2.xlsx

As expected the output is:

+----------+---------+
| Column1  | Column2 |
+----------+---------+
| LastName | c       |
| Value3   | d       |
| Value4   | e       |
| Value1   | a       |
| Value2   | b       |
+----------+---------+

Upvotes: 1

Related Questions