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!