VBA：需要关闭窗口，只能关闭工作簿

I've set up some VBA to filter a specific type of file and return the filtered data (with only 3 columns shown) in a new worksheet. I have two buttons in that worksheet - one to simply select the file path, and the other to run the filter on the file in that path (which is in cell F7).

我已经设置了一些VBA来过滤特定类型的文件,并在新工作表中返回过滤后的数据(只显示了3列)。我在该工作表中有两个按钮 - 一个用于简单地选择文件路径,另一个用于在该路径中的文件上运行过滤器(在单元格F7中)。

My issue is that I haven't been able to read the selected file without opening it. This is fine, since it's a very quick process, but my command to close that file only closes the workbook, and it leaves an empty Excel window open. The only workbook I want open it the one where I'm executing the commands, which is also the one where the filtered results paste to. Any suggestions? I'm fine with figuring out a way to get that window to close, or execute this process without that window actually needing to open in the first place (this would be ideal).

我的问题是我没有打开它就无法读取所选文件。这很好,因为它是一个非常快速的过程,但我关闭该文件的命令只关闭工作簿,它会打开一个空的Excel窗口。我想要的唯一工作簿是打开我正在执行命令的工作簿,也就是过滤结果粘贴到的工具簿。有什么建议么?我很好找出一种让窗口关闭的方法,或者在没有那个窗口实际上需要首先打开的情况下执行这个过程(这将是理想的)。

Public Sub CommandButton2_Click()
b = Application.GetOpenFilename()

If b = False Then
    MsgBox (" You have not selected a file!")
Else
    ThisWorkbook.Worksheets("SelectUpdateFile").Cells(7, 6).Value = b
    MsgBox ("Your file has been selected. Click the Filter button below to generate MOVED nodes.")


End If

End Sub

Sub AutoFilter()
    Dim s As String
    Dim oApp As Object
    Dim wb As Object
    Dim ws As Object


    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True


    On Error Resume Next

    Set wb = oApp.Workbooks.Open(Filename:=Cells(7, 6))

    On Error GoTo 0


    If Not wb Is Nothing Then
        'specify worksheet name
        Set ws = wb.Worksheets(1)
        s = "AB"
        With ws
            'disable all previous filters
            .AutoFilterMode = False
            'apply new filter
            .Range("$A$2:$K$100000").AutoFilter Field:=1, Criteria1:="=*MOVE*", _
            Operator:=xlAnd
            .Range("$A$2:$K$100000").AutoFilter Field:=7, Criteria1:="=*%3e*", _
        Operator:=xlAnd
        'Copy
            .Range("a2:a100000").Copy
            Sheets("MovedNodes").Range("a1").PasteSpecial xlPasteValues

            .Range("b2:b100000").Copy
            Sheets("MovedNodes").Range("b1").PasteSpecial xlPasteValues

            .Range("g2:g100000").Copy
            Sheets("MovedNodes").Range("c1").PasteSpecial xlPasteValues
            'Paste to Sheet2

            End With


End If
oApp.DisplayAlerts = False
oApp.Workbooks.Close
    MsgBox ("Done! Click OK to view the moved nodes.")

Application.ScreenUpdating = True
Sheets("MovedNodes").Select


End Sub

1 个解决方案

#1

You are only closing the workbook in your code.

您只是在代码中关闭工作簿。

oApp.Workbooks.Close

Will just close the workbook and leave the window open.

只需关闭工作簿并打开窗口即可。

Add oApp.quit after closing the workbook

关闭工作簿后添加oApp.quit

#1