excel - 对 Excel 文件应用过滤器 - VBA

Question

无论文件是否打开，我都想对名为“SearchData.xlsx”的文件应用过滤器。

我尝试了以下代码，但它给出了异常。

Sub ApplyFilterInDataFile()
    IsOpen = False

    For Each wb In Workbooks
        If LCase(wb.Name) = "searchdata.xlsx" Then
            IsOpen = True
        End If
    Next

    If IsOpen Then
        Workbooks("SearchData").ActiveSheet.UsedRange.AutoFilter Field:=42, Criteria1:=Range("SearchName")
    Else
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\SearchData.xlsx")
        Workbooks("SearchData").Activate
        Workbooks("SearchData").ActiveSheet.UsedRange.AutoFilter Field:=42, Criteria1:=Range("SearchName")
        wb.Close SaveChanges:=True
        Set wb = Nothing
    End If
End Sub

我需要专家帮助。

Answer 1

如果文件已保存，您必须使用全名(包括扩展名)引用它:

Workbooks("SearchData.Xlsx").Activate