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

Question

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.

TheMadTechnician · Accepted Answer · 2018-06-15 20:13:06Z

2

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

answered Jun 15, 2018 at 20:13

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ephraim Over a year ago

Excellent! I will try this out at work tomorrow and report back.

Ephraim Over a year ago

This works! Thank you. I was on the right track, I just got stuck because I tried [string] instead of [string[]]. Guess I should break out the Learn Powershell book that I bought a couple months ago and haven't read yet....

Ephraim Over a year ago

Oh, I also had an issue with your suggested Array2 build. It wouldn't stop working. Either it takes a lot longer to process than my current method, or it didn't stop once it reached a blank. I'll fiddle with that later.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related