CODEWITHSUNDEEP
excelpowershellautofilter
ezcoding
ezcoding

Reputation: 2996

Name conflicts with _FilterDatabase

I'm getting a "Name conflicts" dialog box after I try to run my Powershell script the second time on the same file. I know that this error has something to do with me autofiltering the file. If I never use autofiter, then the dialog box won't show. What am I doing wrong? Here's the code:

$excel = new-object -comobject excel.application
$excel.DisplayAlerts = $false

$workbook = $excel.Workbooks.open("testtabelle1.xlsx")
Trap {"Worksheet problems..."} $workbook.Worksheets.Item(1) | Out-Null

$from = ">0"
$to = "<2"

$workbook.ActiveSheet.Range("D:D").AutoFilter(1, $from, 1, $to) > $null

#Reset Selection and close file
#$workbook.ActiveSheet.Range("A1").Select() | Out-Null
$workbook.Save()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Upvotes: 2

Views: 30917

Answers (3)

Mike
Mike

Reputation: 1

Sub IdentifyNames()

Dim defName As Name

With ThisWorkbook 'Entire workbook
  For Each defName In .Names
  If Right(defName.Name, 14) = "FilterDatabase" Then defName.delete
  'Debug.Print defName.Name
  Next
End With

End Sub

comment part lists defined names

Upvotes: 0

Andrei Nevedomskii
Andrei Nevedomskii

Reputation: 321

Also it might be useful to do something like this after $workbook.Names.Item("_FilterDatabase").Delete():

$Items_FD = $workbook.Names | ?{ $_.Name -match "_FilterDatabase" }
$Items_FD | %{
  try{
    $_.Delete() | Out-Null
  }
  catch{}
}

Upvotes: 0

Daniel
Daniel

Reputation: 9839

You have to delete the Name "_FilterDatabase", because this name is only allowed once.

The Names object is a hastable. Therefore you Need to delete the key like this:

$workbook.Names.Item("_FilterDatabase").Delete()

Upvotes: 4

Related Questions