PDA

View Full Version : Looping through a bunch of text files



nepotist
04-18-2011, 12:21 PM
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:
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

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.

nepotist
04-18-2011, 12:32 PM
Nevermind I figured out.

Line1: should be above Next but not above Loop

Thanks