0

Dear Stackoverflow(ers),

I have a small problem with powershell and Excel. I have an excel file with several rows and text in them. I would like to read every column and put the content to an array. My script works, but when I show the content of my array there is always one 0 in the beginning. Is there something wrong with my Excel-File or with my Script?

    $a = New-Object -comobject Excel.Application
    $a.Visible = $true
    $a.DisplayAlerts = $False

    $Workbook = $a.workbooks.open("C:\test.xls")
    $Sheet = $Workbook.Worksheets.Item("Auto")

       $row            = [int]2
       $KN             = @() # beginnt bei 2,1... 3,1... 4,1
       Do {$KN        += $Sheet.Cells.Item($row,1).Text ; $row = $row + [int]1} until (!$Sheet.Cells.Item($row,1).Text)

   $Workbook.Close()
   $a.Quit()
   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($a)

   Write-Output $KN

Output:

0
7
7
7
7
7

Excel File looks like that:

A    B    C    D
7
7
7
7
7
7

I guess it's some simple error with my array, but I can't find it! Thanks!

Edit1: The problem lays in the:

  $KN = @()

Because if I stop filling the array with my Exceldata, it continues to show me the 0.

Edit2: Ok it's getting weird. If I enter $KN, after the Script to look into the content again, it shows the right content without the 0. Where does it come from, and why is it there? And why is it gone, when I look into the array manually?

XXInvidiaXX
  • 323
  • 2
  • 7
  • 19

2 Answers2

4

Found it! Finally! Never thought about that, sorry for the post!

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($a)

wrote the 0. And in my script it looked like, it came from the write-output!

XXInvidiaXX
  • 323
  • 2
  • 7
  • 19
  • Indeed, any output - be it from a PowerShell command, an operator-based expression, or a .NET method call - that is neither captured in a variable nor redirected (sent to a file or via the pipeline to another command) is _implicitly output_ from a script or function. To simply _discard_ such output, use `$null = ...`. If you don't discard such output, it becomes part of a script or function's "return value" (stream of output objects). – mklement0 Aug 22 '23 at 14:22
1

If ReleaseComObject($a) was successful it writes out a '0' like most other functions so to make the fix just add pipe and Out-Null to the statement like this System.Runtime.Interopservices.Marshal::ReleaseComObject($a) | Out-Null

DonT
  • 11
  • 1