After using the get file function. formula continues on original file.

[zohaib]

Hello,

I open a blank excel file and go through the below code. The code works just fine until it get to (ActiveSheet.Range("$A$1:$M$1048576").AutoFilter Field:=1, Criteria1:="="). When excel gets to this line it actually performs this code on the original blank file instead. I think it does because the line says activesheet. How do keep excel from going to blank excel file and perform the sequencing code on the file that was opened?

Thanks in advance for your help.

Zohaib

Sub test_fileopen()
Dim filename
filename = Application.GetOpenFilename
If filename <> False Then
Workbooks.Open (filename)
MsgBox "Continue"
' add your code here
ActiveSheet.Name = "Submission Review"

End If
End Sub

 

[Luke M]

Hi Zohaib,

You can assign workbooks/worksheets to variables, to help you keep track of them rather than using volatile references like ActiveSheet or ActiveWorkbook.

Sub test_fileopen()
Dim fileName As String
Dim wbOpen As Workbook
Dim wsFilter As Worksheet

fileName = Application.GetOpenFilename
If fileName = "" Then Exit Sub

Set wbOpen = Workbooks.Open(fileName)
MsgBox "Continue"
' add your code here
Set wsFilter = ActiveSheet

'Now we can use our variables to keep track of things, such as
wsFilter.Name = "Submission Review"
wsFilter.Range("$A$1:$M$1048576").AutoFilter Field:=1, Criteria1:="="
wbOpen.Close savechanges:=True

End Sub

 

[zohaib]

Thank you sooooooooooooooo much for your help

Zohaib

 

[zohaib]

Luke,

If I modify your code to below. Why does it not work?

Set wbOpen = Workbooks.OpenText(fileName), Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 2), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1)), _
        TrailingMinusNumbers:=True

 

[zohaib]

Luke,

I just realized that your formula is also change original file sheet name (1st file we began with) not the file we opened. I want the sheet name to update on just we just opened.

Thanks,
Zohaib

 

[Luke M]

Sorry, I wasn't sure which sheet your code was referring to. You could do something like:

'Sets the wsFilter variable to first sheet in opened workbook
Set wsFilter = wbOpen.Worksheets(1)

I'm not sure on the other issue. Are you attempting to open a CSV file?

 

[zohaib]

No. It is a .xls file.

 

[Luke M]

Then instead of doing an OpenText, you should be able to just do a regular open

Set wbOpen = Workbooks.Open(fileName)