CODEWITHSUNDEEP
excelpowershell
Ephraim
Ephraim

Reputation: 38

Error with Excel Range.AutoFilter when using with array (Powershell)

I am using Powershell to manipulate a .csv file with thousands of computernames and IPs. I'm using powershell because it's part of a bigger script I'm building.

I am using the Range.AutoFilter() method through Excel to delete the IP's that I don't need. Of the example below, I want to delete pc1,pc2 and pc5.

Example sheet:

Name  1   2   3   4
pc1   10  10  10  10
pc2   10  50  100 1
pc3   10  38  20  55
pc4   10  210 38  100
pc5   10  66  41  23
etc...

If I create an array manually, I have zero issues.

$Array1 = "38","210"

However, I need to create the array by pulling the unique values from column 3, like so:

$Array2 = @()
$row = 2
Do {
  $value = $Sheet1.Cells.Item($row,3).Text
  $Array2 += "$value"
  $row++
} until (!$Sheet1.Cells.Item($row,1).Text) #column 1 is less likely to have blanks
$Array2 = $Array1 | Sort-Object -unique

But when I put $Array2 into my autofilter command, I get an error:

$Filter = $Array2
$Range = $Sheet1.UsedRange
[void] $Range.AutoFilter(3,$Filter,7)

Exception setting "AutoFilter": Cannot convert the "7" value of type "int" to type "Object".

According to documentation, the 7 is the value of xlFilterValues for XlAutoFilterOperator. Again, the filter command works fine if I switch $Filter to $Array1.

I'm not sure if there's something I'm missing, or if the array created from Excel values is causing the issue, or what. Any help would be greatly appreciated.

Upvotes: 1

Views: 671

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36332

Ok, a couple things here... don't iterate manually, let the ComObject take care of that with the UsedRange property of the WorkSheet. Then use the Columns property to get columns as objects, and use the Item() method to select the 4th column (Excel is not zero based like PowerShell is). Then you just want each cell in the column, and you want to expand the Text property of each cell (skipping the header row), so this would end up looking like:

$Array2 = $Sheet1.UsedRange.Columns.Item(4).Cells|% text|select -skip 1

But that fails. I tried manually creating $array1 and comparing it to $array2 and I see no differences what so ever. But we know that an array of strings works, so what if we type cast $array2 when it is created?

[string[]]$Array2 = $Sheet1.UsedRange.Columns.Item(4).Cells|% text|select -skip 1

That works fine, so it looks like this was a type issue of some sort. I couldn't find how, but casting the variable as a specific type fixed the issue. In the end you could probably use your original code and just specify the type when creating and modifying the array.

Remove-Variable Array2
$row = 2
Do {
  $value = $Sheet1.Cells.Item($row,3).Text
  [string[]]$Array2 += "$value"
  $row++
} until (!$Sheet1.Cells.Item($row,1).Text) #column 1 is less likely to have blanks

Upvotes: 2

Related Questions