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
source to share