Consulting

Results 1 to 2 of 2

Thread: Looping through a bunch of text files

  1. #1
    VBAX Mentor
    Joined
    Aug 2008
    Posts
    323
    Location

    Looping through a bunch of text files

    Hello,
    This question kinda a refers to this previous thread I started. My questions have been addressed there, however I am trying to add few more IF then statements to it and need help for the same.

    My current code:
    [VBA]Sub Import_Data()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim DirPath As String
    Dim Lrow As Long
    Dim Test As String
    Dim T As String
    Dim oFSO As New FileSystemObject
    Dim oFS
    Dim stext As String


    Lrow = 1
    DirPath = Application.GetOpenFilename()

    DirPath = CurDir$(DirPath) & "\"
    Set objFSO = CreateObject("Scripting.FileSystemObject")


    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(DirPath)
    Application.ScreenUpdating = False
    With ActiveSheet
    .Cells(Lrow, 1) = "Primary Key"
    .Cells(Lrow, 2) = "County"
    .Cells(Lrow, 3) = "Station"
    .Cells(Lrow, 4) = "Daily Volume"
    .Cells(Lrow, 5) = "Count Date"
    .Cells(Lrow, 6) = "File Path"
    End With

    'Loop through the Files collection
    For Each objFile In objFolder.Files
    Lrow = Lrow + 1
    Test = objFile.Path
    Set oFS = oFSO.OpenTextFile(Test)

    Do Until oFS.AtEndOfStream

    stext = oFS.ReadLine

    If (InStr(stext, "County:") = 1) Then
    stext = Trim(Mid(stext, 9, Len(stext) - 8))
    ' If ((stext = 70) Or (stext = 73) Or (stext = 11) Or (stext = 36) Or (stext = 75) Or (stext = 92) Or (stext = 77) Or (stext = 18) Or (stext = 79)) Then
    ActiveSheet.Cells(Lrow, 2).Value = CStr(stext) 'Updates County ID
    ' Else
    ' GoTo Line1
    ' End If
    ElseIf (InStr(stext, "Station:") = 1) Then
    stext = Trim(Mid(stext, 9, Len(stext) - 8))
    ActiveSheet.Cells(Lrow, 3).NumberFormat = "@"
    ActiveSheet.Cells(Lrow, 3).Value = CStr(stext) 'Updates StationID
    ElseIf (InStr(stext, "Start Date:") = 1) Then
    stext = Trim(Mid(stext, 12, Len(stext) - 11))
    ActiveSheet.Cells(Lrow, 5).Value = CDate(stext)
    ElseIf (InStr(stext, "24-Hour Totals:") = 1) Then
    stext = Trim(Right(stext, 8))
    ActiveSheet.Cells(Lrow, 4).Value = stext
    End If
    ActiveSheet.Cells(Lrow, 6) = Test
    ActiveSheet.Cells(Lrow, 1) = Cells(Lrow, 2) & Cells(Lrow, 3)
    'Line1:
    Loop
    Next

    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set oFSO = Nothing
    Set oFS = Nothing
    end sub[/VBA]

    What I am trying to achieve is this:
    I would like to read the first line that is "County ID:", if the value matches to a specific value then record it in excel and continue with the rest of code. else go to next file. I have tried to use if then statement in the above code and commented it as I am stuck with how I would let the program know if the first line meets the needs then read other lines too else go to next file.

    Can anyone tell me what is the code line to specify to read a specific line in a text file. or if there is any other better approach for this.
    I am a Newbie, soon to be a Guru

  2. #2
    VBAX Mentor
    Joined
    Aug 2008
    Posts
    323
    Location
    Nevermind I figured out.

    Line1: should be above Next but not above Loop

    Thanks
    I am a Newbie, soon to be a Guru

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •