Reading delimited text files

[gjsala]

I'm using Excel VBA to read through text files and the problem I'm having is capuring numbers after tags are identified. Below is what a sample of the text file:
A 1550.9 330.0
B 44.0 45.0

The tag is the letter "A" in the text file then I want to capture the number 1550.9. The problem is that if I go to another text file the number 1550.9 may be 998.5 which is one less charater than the previous text file. How do I make sure that no matter what text file I'm looking in I capture the all of the charaters for the number?

Here is what I have for code now:

 If Left(TextLine, 1) = "A" Then
        If cnt = 52 Then
            If Right(Left(TextLine, 8), 6) < 500 Then
                Workbooks.OpenText FileName:=sFile, Origin:=437, StartRow:= _
                1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(100 _
                , 1), Array(113, 1)), TrailingMinusNumbers:=True
            End If
        End If
End If
cnt =cnt +1

Thanks for your help in advance!

 

[Andrzejek]

Are you using "Excel VBA to read through text files " or are you importing a text file into Excel?



Have fun.

---- Andy

 

[SkipVought]

hi,

Also you have 4 items in your FieldInfo array.

Even more relevant, you ought to use DataType:=xlDelimited with SPACE as the delimiter. Just import the entire text file and then process in Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gjsala]

Andrew,
I'm using Excel VBA to read through the text files. I will post more of my code in a bit.

Thanks!

 

[Andrzejek]

I would go with Skip's suggestion, but if you want to go with VBA...

Dim strTextLine As String
Dim strLine() as Sting

Open "C:\Folder\SomeTextFile.txt" For Input As #1
Do While Not EOF(1)             
   Line Input #1, strTextLine   
   
   strLine = Split(strTextLine, " ")

   If Trim(strLine(0)) = "A" Then
      MsgBox Trim(strLine(1))
   End If
Loop
Close #1

Code not tested.

Have fun.

---- Andy

 

[gjsala]

Here is what I have so far. What I'm trying to accomplish is:
1. I have more than 100 text files to look through
2. Once the "A" tag is found and the number is less than 500, as explained above, then I would like to copy that text file
into an excel worksheet labeled "1".
3. If there are more than one text file identifed then a new worksheet would be created on the same workbook and the tabs
labeled in seqential order.
4. Once all of the text files in the folder are anlyzed I can take it from there.
If you have any questions please let me know.

Thank you!

Function PickFolder(strStartDir As Variant) As String
    'Selecting a folder to gather the text files and put into one excel spreadsheet.
    Dim SA As Object, F As Object
    Set SA = CreateObject("Shell.application")
    Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
    If (Not F Is Nothing) Then
        PickFolder = F.Items.Item.Path
    End If
    Set F = Nothing
    Set SA = Nothing
End Function
Sub Test()
Dim fso As Object, F As Object, Path As String
Dim CurrentRow As Range
Dim numRows As Integer, cnt As Integer
Dim myRange As Range
Dim R As Long, d As Long
Dim numR As Integer
Dim systemDirectory As String
On Error Resume Next
Application.ScreenUpdating = False
    UserFile = PickFolder(strStartDir)
    If UserFile = "" Then
        MsgBox "Canceled"
        Exit Sub
    End If
    Set CurWkb = Workbooks.Add
Set fso = CreateObject("Scripting.FileSystemObject")

 For Each F In fso.GetFolder(UserFile).Files
  If F.Type = "Text Document" Then
     TheTextFile = UserFile & "\" & F.Name
    FF = FreeFile()
    Open TheTextFile For Input As FF
      While Not EOF(FF)
      Line Input #FF, TextLine
    
'gets the information from the log files and puts it into the excel spreadsheet.
    If Left(TextLine, 1) = "A" Then
        If cnt = 52 Then
            If Right(Left(TextLine, 8), 6) > 500 Then
                Workbooks.OpenText FileName:=sFile, Origin:=437, StartRow:= _
                1, DataType:=xlDelimited, FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(100 _
                , 1), Array(113, 1)), TrailingMinusNumbers:=True
            End If
        End If
    End If
        cnt = cnt + 1
      Wend
    Close #FF
    cnt = 0
  End If
Next
End Sub