CODEWITHSUNDEEP
vbaexcel
Lind
Lind

Reputation: 33

VBA: need to close window, only can close workbook

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).

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). 

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

Upvotes: 3

Views: 2706

Answers (1)

Sam
Sam

Reputation: 948

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

Upvotes: 1

Related Questions