CODEWITHSUNDEEP
arraysexcelpowershell
XXInvidiaXX
XXInvidiaXX

Reputation: 323

Excel Reading to Powershell Array

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?

Upvotes: 0

Views: 20476

Answers (2)

DonT
DonT

Reputation: 11

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

Upvotes: 1

XXInvidiaXX
XXInvidiaXX

Reputation: 323

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!

Upvotes: 4

Related Questions