VBA: Get External Data and Sort

[BIGTONE559]

I'm trying to figure out how to prompt a file for import as an external data source and then when it is imported into cell $A$1 then sort it.

Tried recording the macro but no luck in understanding it.


this is what i received when i recorded the macro. If someone wouldn't mind simplifying it or potentially showing me another way i'd appreciate it.

Thanks

 Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;\\Master01\C\EmpFile\Checktest.txt", Destination:=Range("$A$1"))
        .Name = "Checktest"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("YTDdb").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("YTDdb").Sort.SortFields.Add Key:=Range("F1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("YTDdb").Sort
        .SetRange Range("A1:G30144")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Trevor G]

What about using Application.Dialog to open a selected text file like this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> appDial()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> Ran <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> RetVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#007F00">' Set object variables for the active book and active cell.</SPAN><br><SPAN style="color:#00007F">Set</SPAN> ws = ActiveWorkbook<br><SPAN style="color:#00007F">Set</SPAN> Ran = Range("A1")<br><br><SPAN style="color:#007F00">' Show the Open dialog box</SPAN><br>RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")<br><SPAN style="color:#007F00">' If Retval is false (Open dialog cancelled), exit the procedure</SPAN><br><SPAN style="color:#00007F">If</SPAN> RetVal = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[BIGTONE559]

Thanks for the response Trevor. Until now i never heard of the Dialog Function.

I will try your code and i will get back to you with my results.

 

[Trevor G]

If you use the object browser you might pick up some other areas that will assist you as well.

To show the Object Browser in the VBA screen simply press F2 then select Application on the left and on the right it lists what you can use and Dialogs is there.

To close the Object Browser you simply use the close cross top right.

 

[BIGTONE559]

wow. . . i appreciate that Trevor G. . . I think it's a great feeling when your like a kid and you know absolutely nothing about something. . . i feel like that whenever i'm on this site. again thanks for the heads up.