Import textfile and sort it

[SteveNash88]

Hi,

Started learning VBA a couple of days ago and I am trying to come up with practical examples where I can learn as much as possible. One thing I want to be able is to import a text file and also sort it, i.e.

"practice.txt" :

Peter - 20
David - 25
.
.
.

For this small example I have written

Option Explicit

Private Sub CommandButton1_Click()

Dim myFile As String, text As String, textline As String, posPeter As Integer, posAndreas As Integer

myFile = "C:\practice.txt"

Open myFile For Input As #1

Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop

Close #1

posPeter = InStr(text, "Peter")
posAndreas = InStr(text, "Andreas")

Range("A1").Value = Mid(text, posPeter, 5)
Range("A2").Value = Mid(text, posAndreas, 7)

Range("B1").Value = Mid(text, posPeter + 8, 2)
Range("B2").Value = Mid(text, posAndreas + 10, 2)

End Sub

______________________________________________

As you can see there are a few problems with this method, namely

a) I have to insert the names (Peter and Andreas) manually
b) I am forced to enter the number of letters in each name manually
c) I am forced to enter the position of each number (20 and 25) manually

The code above imports the names and ages and gives
...A ...B
1 Peter 20
2 Andreas 25

in excel which is exactly what I want. The method sucks if I have many names and ages in the text file because of the things I mentioned above.

I am thinking of a for loop but I do not know how to proceed. Think of a file with say 100 names and a corresponding number (age) to each name.

Any ideas?

 

[Ruddles]

You appear to be reading the entire file into a single string of text and then searching the string for specific names, when the names are already separated out in the input file, one name per line.

You should be reading the input file one line at a time and outputting the contents of the line to the worksheet as you go.

Try this:-

Option Explicit
 
Private Sub CommandButton1_Click()
 
Dim myFile As String, textline As String
Dim iRow as Long
Dim iHyphen as Long
 
myFile = "C:\practice.txt"
 
Close
Open myFile For Input As #1
 
iRow=0
Do Until EOF(1)
  Line Input #1, textline
  iHyphen=InStr(textline,"-")
  iRow=iRow+1
  Range("A" & iRow) = Trim(Left(textline, iHyphen-1))
  Range("B" & iRow) = Trim(Mid(textline, iHyphen+1))
Loop
 
Close #1
 
End Sub

Is that what you're trying to do?

We can add on the code for sorting it later...

 

[Ruddles]

Assuming that works okay, replace:-

myFile = "C:\practice.txt"

with:-

ChDrive "C:"
ChDir "\"
myFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")
If myFile = "False" Then Exit Sub

and try it again.

 

[SteveNash88]

Awesome, worked great, thanks!

 

[Ruddles]

And the sorting? Can you do that?