mdalala
11-03-2017, 08:15 AM
Hi everyone. Basically, I have a txt file with different words and each one has it's own row. I have to copy them using VBA from txt to Excel file and then separate the UPPERCASE and LOWERCASE words. Words that start with the UPPERCASE should be in column A, but words that start with LOWERCASE should be in column B.
I have this code so far which copies the words from txt file to excel:
Public Sub aaa()
Dim i As Long, j As Long
Dim myStr As String
Dim Words() As String
'CHECK THE FILE EXISTS, IF NOT QUIT
If Dir("C:\Users\Robert\Desktop\ekselis\forexcel.txt") = "" Then
MsgBox ("File not Found")
Exit Function 'ADDED QUIT MESSAGE
End If
'FILE TO OPEN
Open "C:\Users\Robert\Desktop\ekselis\forexcel.txt" For Input As #1
'LOOP COUNTER
i = 0
'LOOP THROUGH FILE
Do Until EOF(1)
'READ A LINE
Line Input #1, myStr
'SEPERATE THE LINE BY WORDS
Words = Split(myStr, " ")
'LOOP THROUGH WORDS ARRAY
For j = LBound(Words) To UBound(Words)
'IF THE WORD IS NOT A ""
If Words(j) <> "" Then
'THEN PLACE THE WORD IN THE CELL
ActiveCell.Offset(i, j) = Words(j)
End If
Next
i = i + 1
Loop
Close #1
End Function
I`m not really sure how to organise the IF statements in a loop to complete my task... Thanks!
I have this code so far which copies the words from txt file to excel:
Public Sub aaa()
Dim i As Long, j As Long
Dim myStr As String
Dim Words() As String
'CHECK THE FILE EXISTS, IF NOT QUIT
If Dir("C:\Users\Robert\Desktop\ekselis\forexcel.txt") = "" Then
MsgBox ("File not Found")
Exit Function 'ADDED QUIT MESSAGE
End If
'FILE TO OPEN
Open "C:\Users\Robert\Desktop\ekselis\forexcel.txt" For Input As #1
'LOOP COUNTER
i = 0
'LOOP THROUGH FILE
Do Until EOF(1)
'READ A LINE
Line Input #1, myStr
'SEPERATE THE LINE BY WORDS
Words = Split(myStr, " ")
'LOOP THROUGH WORDS ARRAY
For j = LBound(Words) To UBound(Words)
'IF THE WORD IS NOT A ""
If Words(j) <> "" Then
'THEN PLACE THE WORD IN THE CELL
ActiveCell.Offset(i, j) = Words(j)
End If
Next
i = i + 1
Loop
Close #1
End Function
I`m not really sure how to organise the IF statements in a loop to complete my task... Thanks!