VBA: you need to close the window, you can close the book

I created several VBAs to filter a specific file type and return the filtered data (with only three columns shown) in a new sheet. I have two buttons on this sheet - one for simply selecting a file path and the other for running a filter on a file at that path (which is in cell F7).

My problem is that I was unable to read the selected file without opening it. This is fine as it is a very fast process, but my command to close this file only closes the workbook and it leaves a blank Excel window open. The only workbook I want to open is the one where I execute the commands, and also the one where the filtered results are inserted. Any suggestions? I'm fine with figuring out a way to close this window or go through this process without the window that really needs to be opened in the first place (that 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

      

+3


source to share


1 answer


You only close the book in your code.

oApp.Workbooks.Close

      



Close the book and leave the window open.

Add oApp.quit

after book close

+1


source







All Articles