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)
