CODEWITHSUNDEEP
excelpowershell
theshizy
theshizy

Reputation: 545

Powershell - method invocation failed because SystemComObject doesn't contain a method named cells

I've been trying to execute the following code to pull information out of an Excel spreadsheet but it keeps failing with the following error:

Method invocation failed because [System.__ComObject#{000208d8-0000-0000-c000-000000000046}] doesn't contain a method named 'cells'.
 At run.ps1:42 char:52
 +         for ($row = [int]$matches[1] + 1; $sh.cells <<<< ($row, 2) -ne ""; $row++)
     + CategoryInfo          : InvalidOperation: (cells:String) [], RuntimeException
     + FullyQualifiedErrorId : MethodNotFound

Any idea how I can resolve this? Here is my code:

$(for ($i=3; $i -le $wb.sheets.count; $i++){

    $sh=$wb.Sheets.Item($i)
    $startCell_appname = $sh.cells.item(1,2).EntireColumn.find("Application Name").Address()

    if ($startCell_appname -match '\$\w+\$(\d+)')
    {
        for ($row = [int]$matches[1] + 1; $sh.Cells($row, 2) -ne ""; $row++)
        {
            $apps = "" | Select appname,location,lob,os
            $apps.appname = $sh.Cells($row, 2).Value2 

            # I don't know if the location value is in column 3; this is just an example.
            $apps.location = $sh.Cells($row, 3).Value2

            $apps.lob = $sh.Name
            $apps.os = "Windows 7"
            $apps
        }
    }
}) | Export-csv "apps.csv" -NoTypeInformation

Upvotes: 0

Views: 2332

Answers (1)

Keith Hill
Keith Hill

Reputation: 201952

Use $sh.Cells.Item($row,2) like you do where you are assigning to $startCell_appname.

Upvotes: 2

Related Questions