Upload Text file into Excel – 1 Click

This code will open up the explorer dialog window, select a text file and it will upload the file into excel. The code will will put the data into sheet 1 starting in cell A1 and then sort the data into columns, then correct the UPPER and LOWER case values in column E (5).

Sub Sample()
    With ActiveSheet.QueryTables.Add(Connection:= _
        “TEXT;” & GetFile, Destination:=Range(“$A$1“) _
        )
        .Name = “Sample”
        .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, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Application.ScreenUpdating = False
     With ActiveSheet.UsedRange.Columns(5)   ‘< change to which column 5 being E
           .Offset(0, 5).FormulaR1C1 = “=Proper(RC[-5])”
           .Value = .Offset(0, 5).Value
           .Offset(0, 5).ClearContents
           .Replace “‘S”, “‘s”
           End With
            With ActiveSheet.UsedRange.Columns(7)   ‘< change to which column 7 being G
           .Offset(0, 5).FormulaR1C1 = “=Proper(RC[-5])”
           .Value = .Offset(0, 5).Value
           .Offset(0, 5).ClearContents
           .Replace “‘S”, “‘s”
     End With
     Application.ScreenUpdating = True
End Sub
Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(FileFilter:=”Txt (*.Txt), *.Txt”, Title:=”Select File To Be Opened”)
If filename__path = False Then Exit Function
GetFile = filename__path
End Function
As always, parts to change are in bold.
Nice handy bit of code if you are always moving data from text files into excel!
Enjoy!