Exception from HRESULT: 0x800A03EC in powershell

Question

I am trying to delete entire row from excel based on search criteria, below is my code:

$file = "C:\Desktop\user.xlsx"
$excel = new-object -com Excel.Application -Property @{Visible = $false}
$workbook = $excel.Workbooks.Open($file)
$sheet = $workbook.Sheets.Item(1)
for($i = 1; $i -lt $($Workbook.Sheets.Count() + 1); $i++)
{
    $Range = $Workbook.Sheets.Item($i).Range("A:Z")
    $Target = $Range.Find($SearchString)
    $First = $Target
    Do
    {
        Write-Host "$($Target.AddressLocal())"
        $stg= "$($Target.AddressLocal())"
        $str= $stg.Substring(3)
        [void]$sheet.Cells.Item($str,$str).EntireRow.Delete()
        $Target = $Range.FindNext($Target)
    }
    While ($Target -ne $NULL -and $Target.AddressLocal() -ne First.AddressLocal())
}

$WorkBook.Save()
$WorkBook.Close($true)
$excel.quit()

i am able to search "Null" values and i could able to find the address of the string "null", but while
deleting i am facing below exception, please Help.

Note: when i pass address [void]$ws.Cells.Item(100,100).EntireRow.Delete(),i could able to delete the row.

Exception from HRESULT: 0x800A03EC
At C:\desktop\Automation\1delet-excel-row-null.ps1:18 char:9
[void]$sheet.Cells.Item("$str","$str").EntireRow.Delete()
CategoryInfo: OperationStopped: (:) [], COMException
FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Welcome to SO. Have you confirmed that `$str` can be converted to a numeric integer value? Also, have you checked [this](https://stackoverflow.com/a/43218992/11609403), [this1](https://stackoverflow.com/a/21711147/11609403), and [this2](https://stackoverflow.com/a/38077834/11609403)? Maybe try calling `.Delete(-4162)` with the xlShiftUp value, or `.Delete(-4159)` with the xlShiftToLeft value. Worth a check. — leeharvey1, Jun 25 '20 at 12:21
I've tried your code sample, but I think there's a couple of typos - you're not defining a value for ```$SearchString``` anywhere, and in your ```while``` expression, ```First.AddressLocal()``` should probably be ```$First.AddressLocal()```. Might not solve your error, but it'll make it easier for other people to run your sample locally if you fix these... — mclayton, Jun 25 '20 at 23:03

score 0 · Answer 1 · answered Jun 25 '20 at 23:36

I can reproduce your issue with a couple of tweaks to your sample code.

Two different ways to fix your immediate issue are:

Force conversion of the $str parameter to an int with [int] $str:

Do
{
    Write-Host "$($Target.AddressLocal())"
    $stg = "$($Target.AddressLocal())"
    $str = $stg.Substring(3)
    [void] $sheet.Cells.Item([int] $str, [int] $str).EntireRow.Delete()
    $Target = $Range.FindNext($Target)
}

Or,

Rather than munging the address string, just delete the row directly with [void] $Target.EntireRow.Delete():

Do
{
    Write-Host "$($Target.AddressLocal())"
    [void] $Target.EntireRow.Delete()
    $Target = $Range.FindNext($Target)
}

Both of these should resolve the error you're seeing, but you'll get another error straight afterwards:

Unable to get the FindNext property of the Range class
At line:20 char:9
+         $Target = $Range.FindNext($Target)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Exception from HRESULT: 0x800A01A8
At line:20 char:9
+         $Target = $Range.FindNext($Target)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

I think this is because you're changing the contents of $Range and $Target by deleting rows, so you might need to find another way to continue the search after a match. Maybe pre-cache the list of rows to delete first, and then iterate over them to delete...

Exception from HRESULT: 0x800A03EC in powershell

1 Answers1