locked
Powershell Selection.Autofilter (x, does not contain) RRS feed

  • Question

  • Dear All,

    I am copying together a powershell script and am stuck with powershell, where I open an Excel sheet, set an Autofilter and try to filter a column where I would like to filter "does not contain".

    Can someone explain, how the command looks like?

    In one of the blogs it says Selection.Autofilter(5,<> 'text') which didn't work for me. I tried several things like -eq but that also does not work.

    Maybe someone has a clue?

    Thank you very much,

    Mike

    Friday, June 1, 2018 6:31 AM

All replies

  • Please post your script.  What you have posted is not PowerShell.  You cannot use VBA code directly in PowerShell.


    \_(ツ)_/

    Friday, June 1, 2018 7:51 AM
  • Dear Jrv,

    Thank you very much trying to help me. I was not aware, that I have copied VBA code.

    What I did copy - paste together, you will find at the end:

    The line, where I am stuck is the one below:

    $objExcel.Selection.AutoFilter(5,)# "tap.local/T-Systems/DYC-Service/DYC-Users/Quarantine-Users")

    Good news is, that the Filter is set in Column 5. What I was not able to figure out, how to filter for everything that 'does not contain' "tap.local/T-Systems/DYC-Service/DYC-Users/Quarantine-Users"

    Maybe you have a trick around? Sorry, I am hardcore beginner when it comes to Powershell.

    Thank you very much,

    Mike

    CLS
    # Check if the folder C:\Temp exists and if not, create it
    if (!(test-path C:\temp)) {new-item -Path c:\temp -type directory -Force}


    $setdate = Get-Date -UFormat %y-%b-%d

    $location = "C:\temp\Non-TAP-ALL-Members_$setdate.csv"
    #Get all Active Directory Users, which are not memeber of "TAP-ALL"
    get-aduser -filter { -not (memberof -ne "TAP-ALL") } -Properties * | select SamAccountName, GivenName, Surname, Description, CanonicalName, Enabled | export-csv $location -Delimiter ";" -NoTypeInformation -Encoding UTF8

    CLS
    Write-Host "oooooooooooooooooooooooooooooooooooooooooooooo" -ForegroundColor Green 
    Write-Host "-->  Excel is about to start...please wait <--" -ForegroundColor White 
    Write-Host "oooooooooooooooooooooooooooooooooooooooooooooo" -ForegroundColor Green

    $comments = @' 
    Script name: AutoFit-Columns.ps1 
    Created on: Friday, April 20, 2007 
    Author: Kent Finkle 
    Purpose: How can I use Windows Powershell to AutoFit Columns in a Microsoft Excel Worksheet? 
    '@ 
     
    # ----------------------------------------------------- 
    function Release-Ref ($ref) { 
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
    [System.__ComObject]$ref) -gt 0) 
    [System.GC]::Collect() 
    [System.GC]::WaitForPendingFinalizers() 

    # ----------------------------------------------------- 
     
    $objExcel = new-object -comobject excel.application  
    $objExcel.Visible = $True  
    $objWorkbook = $objExcel.Workbooks.Open($location)  
    $objWorksheet = $objWorkbook.Worksheets.Item(1)  
    $x = 1 
     
    $objRange = $objWorksheet.UsedRange 
    [void] $objRange.EntireColumn.Autofit()
    $xlFilterValues = "tap.local/T-Systems/DYC-Service/DYC-Users/Quarantine-Users"
    $objExcel.Selection.AutoFilter(5,)# "tap.local/T-Systems/DYC-Service/DYC-Users/Quarantine-Users")
     


     
    $a = Release-Ref($objWorksheet) 
    $a = Release-Ref($objWorkbook) 
    $a = Release-Ref($objExcel) 

    Friday, June 1, 2018 7:59 AM
    • Edited by jrv Friday, June 1, 2018 8:28 AM
    Friday, June 1, 2018 8:27 AM
  • Is there another way around, so I can filter? Something like does not begin with or similar?
    Friday, June 1, 2018 8:32 AM
  • Post in Excel developers forum to see if someone has an example of how to do this. Thre are a number of ways but I have no PowerShell examples.


    \_(ツ)_/

    Friday, June 1, 2018 8:35 AM
  • Thank you very much, appreciate your help and effort,

    Greetings from Switzerland,

    Mike

    Friday, June 1, 2018 8:37 AM