Read the next line in a text file - microsoft.public.excel.programming

Hi I'm hoping you can help. I'm trying to search the next line of a text file for a word called PAYDETAILS. If the word isn't there then in the worksheet i need to place "N/A". I have placed a shortened version of the program. I appreciate any help. Regards Frederic sub readtext() Dim strTextLine As String Dim strFilename As String Dim vFileHandle As Integer strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" If Dir(strFilename) = "" Then MsgBox ("File Not Found") Exit Sub End If vFileHandle = FreeFile Open strFilename For Input As vFileHandle Do While Not EOF(vFileHandle) Line Input #vFileHandle, strTextLine If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then Sheets("ECI File Index").Select NextRow = Range("O65536").End(xlUp).Row + 1 Range("O" & NextRow).Select ActiveCell = Mid(strTextLine, 40, 77) ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then Sheets("ECI File Index").Select NextRow = Range("L65536").End(xlUp).Row + 1 Range("L" & NextRow).Select ActiveCell = Mid(strTextLine, 11, 5) NextRow = Range("M65536").End(xlUp).Row + 1 Range("M" & NextRow).Select ActiveCell = Mid(strTextLine, 16, 8) NextRow = Range("N65536").End(xlUp).Row + 1 Range("N" & NextRow).Select ActiveCell = Mid(strTextLine, 24, 12) end if loop Close vFileHandle end sub

Frederic, I have inserted some code below that simply alters the ElseIf portion of your code by using a nested If to search for "PAYDETAILS" and act accordingly if "PAYDETAILS" is/isn't present. I also created a worksheet object to remove the .Select from your code. (The .Select only slows things down and is unnecessary). You didn't specify where the "N/A" is supposed to go, so I simply commented that portion of the program for you to fill in accordingly. As a side note, using your hard-coded values for the Mid function may backfire on you if your text file format ever changes. You may want to consider a way that would find the starting character (and length of text) for you rather than using a hard-coded value. (For example, if your text file is delimited, then using the delimiter is one way to leverage finding the right spot and the right length). Best, Matthew Herbert Sub readtext() Dim strTextLine As String Dim strFilename As String Dim vFileHandle As Integer Dim Wks As Worksheet Dim lngNextRow As Long strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" If Dir(strFilename) = "" Then MsgBox "File Not Found" Exit Sub End If vFileHandle = FreeFile() Set Wks = Worksheets("ECI File Index") Open strFilename For Input As vFileHandle Do While Not EOF(vFileHandle) Line Input #vFileHandle, strTextLine If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then With Wks lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1 .Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77) End With Else If InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then With Wks lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 .Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5) lngNextRow = Range("M" & .Rows.Count).End(xlUp).Row + 1 .Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8) lngNextRow = Range("N" & .Rows.Count).End(xlUp).Row + 1 .Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12) End With Else 'put the "N/A" where ever it needs to go. I'm not sure ' if the "N/A" goes into columns L, M, and N, or simply ' in one of the columns. You can fill this in as needed. End If End If Loop Close vFileHandle End Sub "bluewatermist" wrote: > Hi > > I'm hoping you can help. I'm trying to search the next line of a text file > for a word called PAYDETAILS. If the word isn't there then in the worksheet > i need to place "N/A". > > I have placed a shortened version of the program. I appreciate any help. > > Regards > Frederic > > sub readtext() > > Dim strTextLine As String > Dim strFilename As String > Dim vFileHandle As Integer > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > If Dir(strFilename) = "" Then > MsgBox ("File Not Found") > Exit Sub > End If > > vFileHandle = FreeFile > > Open strFilename For Input As vFileHandle > > Do While Not EOF(vFileHandle) > Line Input #vFileHandle, strTextLine > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > Sheets("ECI File Index").Select > NextRow = Range("O65536").End(xlUp).Row + 1 > Range("O" & NextRow).Select > ActiveCell = Mid(strTextLine, 40, 77) > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > Sheets("ECI File Index").Select > NextRow = Range("L65536").End(xlUp).Row + 1 > Range("L" & NextRow).Select > ActiveCell = Mid(strTextLine, 11, 5) > > NextRow = Range("M65536").End(xlUp).Row + 1 > Range("M" & NextRow).Select > ActiveCell = Mid(strTextLine, 16, 8) > > NextRow = Range("N65536").End(xlUp).Row + 1 > Range("N" & NextRow).Select > ActiveCell = Mid(strTextLine, 24, 12) > > end if > loop > Close vFileHandle > > end sub

Frederic, I just noticed that two of the "lngNextRow = ..." lines are missing the "." (i.e. dot) prior to the "Range" object. (This will cause problems with where the data will be placed, and you'll have unexpected results). Simply added the dots to the following two lines: lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 Sorry for not reading my code over carefully prior to replying to the post. Obviously, because I don't have the text file, the code is not tested. Best, Matt "bluewatermist" wrote: > Hi > > I'm hoping you can help. I'm trying to search the next line of a text file > for a word called PAYDETAILS. If the word isn't there then in the worksheet > i need to place "N/A". > > I have placed a shortened version of the program. I appreciate any help. > > Regards > Frederic > > sub readtext() > > Dim strTextLine As String > Dim strFilename As String > Dim vFileHandle As Integer > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > If Dir(strFilename) = "" Then > MsgBox ("File Not Found") > Exit Sub > End If > > vFileHandle = FreeFile > > Open strFilename For Input As vFileHandle > > Do While Not EOF(vFileHandle) > Line Input #vFileHandle, strTextLine > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > Sheets("ECI File Index").Select > NextRow = Range("O65536").End(xlUp).Row + 1 > Range("O" & NextRow).Select > ActiveCell = Mid(strTextLine, 40, 77) > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > Sheets("ECI File Index").Select > NextRow = Range("L65536").End(xlUp).Row + 1 > Range("L" & NextRow).Select > ActiveCell = Mid(strTextLine, 11, 5) > > NextRow = Range("M65536").End(xlUp).Row + 1 > Range("M" & NextRow).Select > ActiveCell = Mid(strTextLine, 16, 8) > > NextRow = Range("N65536").End(xlUp).Row + 1 > Range("N" & NextRow).Select > ActiveCell = Mid(strTextLine, 24, 12) > > end if > loop > Close vFileHandle > > end sub

Hi Matthew Thank you for the quick reply. I have added those extra dots but I'm getting a runtime error '91' object variable or with block variable not set. Error is highlighting on the first lngNextRow = ..... many thanks Frederic "Matthew Herbert" wrote: > Frederic, > > I just noticed that two of the "lngNextRow = ..." lines are missing the "." > (i.e. dot) prior to the "Range" object. (This will cause problems with where > the data will be placed, and you'll have unexpected results). Simply added > the dots to the following two lines: > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > Sorry for not reading my code over carefully prior to replying to the post. > Obviously, because I don't have the text file, the code is not tested. > > Best, > > Matt > > "bluewatermist" wrote: > > > Hi > > > > I'm hoping you can help. I'm trying to search the next line of a text file > > for a word called PAYDETAILS. If the word isn't there then in the worksheet > > i need to place "N/A". > > > > I have placed a shortened version of the program. I appreciate any help. > > > > Regards > > Frederic > > > > sub readtext() > > > > Dim strTextLine As String > > Dim strFilename As String > > Dim vFileHandle As Integer > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > > > If Dir(strFilename) = "" Then > > MsgBox ("File Not Found") > > Exit Sub > > End If > > > > vFileHandle = FreeFile > > > > Open strFilename For Input As vFileHandle > > > > Do While Not EOF(vFileHandle) > > Line Input #vFileHandle, strTextLine > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > Sheets("ECI File Index").Select > > NextRow = Range("O65536").End(xlUp).Row + 1 > > Range("O" & NextRow).Select > > ActiveCell = Mid(strTextLine, 40, 77) > > > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > Sheets("ECI File Index").Select > > NextRow = Range("L65536").End(xlUp).Row + 1 > > Range("L" & NextRow).Select > > ActiveCell = Mid(strTextLine, 11, 5) > > > > NextRow = Range("M65536").End(xlUp).Row + 1 > > Range("M" & NextRow).Select > > ActiveCell = Mid(strTextLine, 16, 8) > > > > NextRow = Range("N65536").End(xlUp).Row + 1 > > Range("N" & NextRow).Select > > ActiveCell = Mid(strTextLine, 24, 12) > > > > end if > > loop > > Close vFileHandle > > > > end sub

After making the changes to the Range entries (to .Range), try changing this section of code a bit to make another check: Set Wks = Worksheets("ECI File Index") Open strFilename For Input As vFileHandle Change the Set statement and add a line of code to make it look like this Set Wks = ThisWorkbook.Worksheets("ECI File Index") Msgbox "Wks is now set to worksheet named [" & Wks.name & "]" Open strFilename For Input As vFileHandle Then try running it and seeing for sure that Wks got set to the worksheet. Although I'd have expected an error at that Set statement if the sheet doesn't exist. In looking at the code, I don't see any readily visible problem that would give the error you reported; the Do and If blocks seem correct and complete, which could have been a source of a false report of that type if they aren't. "bluewatermist" wrote: > Hi Matthew > > Thank you for the quick reply. I have added those extra dots but I'm > getting a runtime error '91' object variable or with block variable not set. > Error is highlighting on the first lngNextRow = ..... > > many thanks > Frederic > > "Matthew Herbert" wrote: > > > Frederic, > > > > I just noticed that two of the "lngNextRow = ..." lines are missing the "." > > (i.e. dot) prior to the "Range" object. (This will cause problems with where > > the data will be placed, and you'll have unexpected results). Simply added > > the dots to the following two lines: > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > > > Sorry for not reading my code over carefully prior to replying to the post. > > Obviously, because I don't have the text file, the code is not tested. > > > > Best, > > > > Matt > > > > "bluewatermist" wrote: > > > > > Hi > > > > > > I'm hoping you can help. I'm trying to search the next line of a text file > > > for a word called PAYDETAILS. If the word isn't there then in the worksheet > > > i need to place "N/A". > > > > > > I have placed a shortened version of the program. I appreciate any help. > > > > > > Regards > > > Frederic > > > > > > sub readtext() > > > > > > Dim strTextLine As String > > > Dim strFilename As String > > > Dim vFileHandle As Integer > > > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > > > > > If Dir(strFilename) = "" Then > > > MsgBox ("File Not Found") > > > Exit Sub > > > End If > > > > > > vFileHandle = FreeFile > > > > > > Open strFilename For Input As vFileHandle > > > > > > Do While Not EOF(vFileHandle) > > > Line Input #vFileHandle, strTextLine > > > > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > > Sheets("ECI File Index").Select > > > NextRow = Range("O65536").End(xlUp).Row + 1 > > > Range("O" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 40, 77) > > > > > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > > Sheets("ECI File Index").Select > > > NextRow = Range("L65536").End(xlUp).Row + 1 > > > Range("L" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 11, 5) > > > > > > NextRow = Range("M65536").End(xlUp).Row + 1 > > > Range("M" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 16, 8) > > > > > > NextRow = Range("N65536").End(xlUp).Row + 1 > > > Range("N" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 24, 12) > > > > > > end if > > > loop > > > Close vFileHandle > > > > > > end sub

Hi Setting Wks has worked but my other problem is if for example "PAYDETAILS" doesn't exist in the text file how can I place on the worksheet at column L, M or N "N/A". I have tried the below code but when it starts checking each line in the text file for PAYDETAILS and doesn't find it, the macro then adds "N/A" on the next available line until it finds the word PAYDETAILS. With Wks lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 ..Range("L" & lngNextRow).Value = "N/A" lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 ..Range("M" & lngNextRow).Value = "N/A" lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 ..Range("N" & lngNextRow).Value = "N/A" End With "JLatham" wrote: > After making the changes to the Range entries (to .Range), try changing this > section of code a bit to make another check: > > Set Wks = Worksheets("ECI File Index") > > Open strFilename For Input As vFileHandle > > > Change the Set statement and add a line of code to make it look like this > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > Msgbox "Wks is now set to worksheet named [" & Wks.name & "]" > > Open strFilename For Input As vFileHandle > > Then try running it and seeing for sure that Wks got set to the worksheet. > Although I'd have expected an error at that Set statement if the sheet > doesn't exist. In looking at the code, I don't see any readily visible > problem that would give the error you reported; the Do and If blocks seem > correct and complete, which could have been a source of a false report of > that type if they aren't. > > > > "bluewatermist" wrote: > > > Hi Matthew > > > > Thank you for the quick reply. I have added those extra dots but I'm > > getting a runtime error '91' object variable or with block variable not set. > > Error is highlighting on the first lngNextRow = ..... > > > > many thanks > > Frederic > > > > "Matthew Herbert" wrote: > > > > > Frederic, > > > > > > I just noticed that two of the "lngNextRow = ..." lines are missing the "." > > > (i.e. dot) prior to the "Range" object. (This will cause problems with where > > > the data will be placed, and you'll have unexpected results). Simply added > > > the dots to the following two lines: > > > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > > > > > Sorry for not reading my code over carefully prior to replying to the post. > > > Obviously, because I don't have the text file, the code is not tested. > > > > > > Best, > > > > > > Matt > > > > > > "bluewatermist" wrote: > > > > > > > Hi > > > > > > > > I'm hoping you can help. I'm trying to search the next line of a text file > > > > for a word called PAYDETAILS. If the word isn't there then in the worksheet > > > > i need to place "N/A". > > > > > > > > I have placed a shortened version of the program. I appreciate any help. > > > > > > > > Regards > > > > Frederic > > > > > > > > sub readtext() > > > > > > > > Dim strTextLine As String > > > > Dim strFilename As String > > > > Dim vFileHandle As Integer > > > > > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > > > > > > > If Dir(strFilename) = "" Then > > > > MsgBox ("File Not Found") > > > > Exit Sub > > > > End If > > > > > > > > vFileHandle = FreeFile > > > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > Do While Not EOF(vFileHandle) > > > > Line Input #vFileHandle, strTextLine > > > > > > > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > > > Sheets("ECI File Index").Select > > > > NextRow = Range("O65536").End(xlUp).Row + 1 > > > > Range("O" & NextRow).Select > > > > ActiveCell = Mid(strTextLine, 40, 77) > > > > > > > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > > > Sheets("ECI File Index").Select > > > > NextRow = Range("L65536").End(xlUp).Row + 1 > > > > Range("L" & NextRow).Select > > > > ActiveCell = Mid(strTextLine, 11, 5) > > > > > > > > NextRow = Range("M65536").End(xlUp).Row + 1 > > > > Range("M" & NextRow).Select > > > > ActiveCell = Mid(strTextLine, 16, 8) > > > > > > > > NextRow = Range("N65536").End(xlUp).Row + 1 > > > > Range("N" & NextRow).Select > > > > ActiveCell = Mid(strTextLine, 24, 12) > > > > > > > > end if > > > > loop > > > > Close vFileHandle > > > > > > > > end sub

Frederic, I'm implying that you still have a question with the code, but I don't know what that question may be. I think the question might be something like "how do I set up putting the "N/A" in column L, M, or N"? (If this is the case, then there has to be some other criteria to decide how L would get "N/A" over M or N, or M would get "N/A" over L or N, etc.). If you have a specific question, please post back with that detailed question (and include your code again to remove the guess work of what your code now looks like). Best, Matt "bluewatermist" wrote: > Hi > > Setting Wks has worked but my other problem is if for example "PAYDETAILS" > doesn't exist in the text file how can I place on the worksheet at column L, > M or N "N/A". > > I have tried the below code but when it starts checking each line in the > text file for PAYDETAILS and doesn't find it, the macro then adds "N/A" on > the next available line until it finds the word PAYDETAILS. > > With Wks > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > .Range("L" & lngNextRow).Value = "N/A" > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > .Range("M" & lngNextRow).Value = "N/A" > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > .Range("N" & lngNextRow).Value = "N/A" > End With > > > "JLatham" wrote: > > > After making the changes to the Range entries (to .Range), try changing this > > section of code a bit to make another check: > > > > Set Wks = Worksheets("ECI File Index") > > > > Open strFilename For Input As vFileHandle > > > > > > Change the Set statement and add a line of code to make it look like this > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > Msgbox "Wks is now set to worksheet named [" & Wks.name & "]" > > > > Open strFilename For Input As vFileHandle > > > > Then try running it and seeing for sure that Wks got set to the worksheet. > > Although I'd have expected an error at that Set statement if the sheet > > doesn't exist. In looking at the code, I don't see any readily visible > > problem that would give the error you reported; the Do and If blocks seem > > correct and complete, which could have been a source of a false report of > > that type if they aren't. > > > > > > > > "bluewatermist" wrote: > > > > > Hi Matthew > > > > > > Thank you for the quick reply. I have added those extra dots but I'm > > > getting a runtime error '91' object variable or with block variable not set. > > > Error is highlighting on the first lngNextRow = ..... > > > > > > many thanks > > > Frederic > > > > > > "Matthew Herbert" wrote: > > > > > > > Frederic, > > > > > > > > I just noticed that two of the "lngNextRow = ..." lines are missing the "." > > > > (i.e. dot) prior to the "Range" object. (This will cause problems with where > > > > the data will be placed, and you'll have unexpected results). Simply added > > > > the dots to the following two lines: > > > > > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > > > > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > > > > > > > Sorry for not reading my code over carefully prior to replying to the post. > > > > Obviously, because I don't have the text file, the code is not tested. > > > > > > > > Best, > > > > > > > > Matt > > > > > > > > "bluewatermist" wrote: > > > > > > > > > Hi > > > > > > > > > > I'm hoping you can help. I'm trying to search the next line of a text file > > > > > for a word called PAYDETAILS. If the word isn't there then in the worksheet > > > > > i need to place "N/A". > > > > > > > > > > I have placed a shortened version of the program. I appreciate any help. > > > > > > > > > > Regards > > > > > Frederic > > > > > > > > > > sub readtext() > > > > > > > > > > Dim strTextLine As String > > > > > Dim strFilename As String > > > > > Dim vFileHandle As Integer > > > > > > > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > > > > > > > > > If Dir(strFilename) = "" Then > > > > > MsgBox ("File Not Found") > > > > > Exit Sub > > > > > End If > > > > > > > > > > vFileHandle = FreeFile > > > > > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > > > Do While Not EOF(vFileHandle) > > > > > Line Input #vFileHandle, strTextLine > > > > > > > > > > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > > > > Sheets("ECI File Index").Select > > > > > NextRow = Range("O65536").End(xlUp).Row + 1 > > > > > Range("O" & NextRow).Select > > > > > ActiveCell = Mid(strTextLine, 40, 77) > > > > > > > > > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > > > > Sheets("ECI File Index").Select > > > > > NextRow = Range("L65536").End(xlUp).Row + 1 > > > > > Range("L" & NextRow).Select > > > > > ActiveCell = Mid(strTextLine, 11, 5) > > > > > > > > > > NextRow = Range("M65536").End(xlUp).Row + 1 > > > > > Range("M" & NextRow).Select > > > > > ActiveCell = Mid(strTextLine, 16, 8) > > > > > > > > > > NextRow = Range("N65536").End(xlUp).Row + 1 > > > > > Range("N" & NextRow).Select > > > > > ActiveCell = Mid(strTextLine, 24, 12) > > > > > > > > > > end if > > > > > loop > > > > > Close vFileHandle > > > > > > > > > > end sub

Hi Matthew This problem was my first original question, but I have probably not stated it clearly. The text files have lots of other information which I don't require. What is happening with the macro below is that if for example the macro doesn't find on the next line the word "PAYDETAILS" it places "-----" on column L, or M, or N. Therefore if the information retreived made only 4 rows on the work sheet, columns L, or M or N used up 57 rows. I have place an example below and the code. Hope i have made it clearer Frederic $773.75 17092009 SUPERLIFE PTY ----- ----- ----- $1015.90 17092009 SUPERLIFE PTY ----- ----- ----- $315.00 11092009 SUPERLIFE PTY ----- ----- ----- $142.50 17092009 SUPERLIFE PTY ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- CHQ 12082009 $77.37 ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- CHQ 12082009 $101.59 ----- ----- ----- ----- ----- ----- Sub ReadText() Dim strTextLine As String Dim strFilename As String Dim vFileHandle As Integer Dim Wks As Worksheet strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" If Dir(strFilename) = "" Then MsgBox ("File Not Found") Exit Sub End If vFileHandle = FreeFile newfilepath = Right(strFilename, 31) Open strFilename For Input As vFileHandle Do While Not EOF(vFileHandle) Line Input #vFileHandle, strTextLine Set Wks = ThisWorkbook.Worksheets("ECI File Index") If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then With Wks NextRow = Range("O65536").End(xlUp).Row + 1 Range("O" & NextRow).Select ActiveCell = Mid(strTextLine, 40, 77) End With ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then With Wks NextRow = Range("C65536").End(xlUp).Row + 1 Range("C" & NextRow).Select ActiveCell = Mid(strTextLine, 11, 44) End With ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then With Wks NextRow = Range("D65536").End(xlUp).Row + 1 Range("D" & NextRow).Select ActiveCell = Mid(strTextLine, 148, 10) NextRow = Range("E65536").End(xlUp).Row + 1 Range("E" & NextRow).Select ActiveCell = Mid(strTextLine, 191, 8) End With ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then With Wks NextRow = Range("K65536").End(xlUp).Row + 1 Range("K" & NextRow).Select ActiveCell = Mid(strTextLine, 11, 76) End With ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then With Wks NextRow = Range("G65536").End(xlUp).Row + 1 Range("G" & NextRow).Select ActiveCell = Mid(strTextLine, 31, 11) NextRow = Range("H65536").End(xlUp).Row + 1 Range("H" & NextRow).Select ActiveCell = Mid(strTextLine, 51, 76) If ActiveCell.Offset(0, 7) = "" Then ActiveCell.Offset(0, 7).Select Selection.Value = "-----" ActiveCell.Offset(0, -13).Select Selection.Value = "--" ActiveCell.Offset(0, -1).Select Selection.Value = newfilepath ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> "" Then ActiveCell.Offset(0, -6).Select Selection.Value = "Y" ActiveCell.Offset(0, -1).Select Selection.Value = newfilepath End If End With ElseIf InStr(1, Left(strTextLine, 10), "SPRCONTBTN") > 0 Then With Wks NextRow = Range("I65536").End(xlUp).Row + 1 Range("I" & NextRow).Select ActiveCell = Mid(strTextLine, 11, 13) NextRow = Range("J65536").End(xlUp).Row + 1 Range("J" & NextRow).Select ActiveCell = Mid(strTextLine, 40, 8) End With ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then With Wks NextRow = Range("L65536").End(xlUp).Row + 1 Range("L" & NextRow).Select ActiveCell = Mid(strTextLine, 11, 5) NextRow = Range("M65536").End(xlUp).Row + 1 Range("M" & NextRow).Select ActiveCell = Mid(strTextLine, 16, 8) NextRow = Range("N65536").End(xlUp).Row + 1 Range("N" & NextRow).Select ActiveCell = Mid(strTextLine, 24, 12) End With else Else With Wks lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 ..Range("L" & lngNextRow).Value = "-----" lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 ..Range("M" & lngNextRow).Value = "-----" lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 ..Range("N" & lngNextRow).Value = "-----" End With End If Loop Close vFileHandle End Sub "Matthew Herbert" wrote: > Frederic, > > I'm implying that you still have a question with the code, but I don't know > what that question may be. I think the question might be something like "how > do I set up putting the "N/A" in column L, M, or N"? (If this is the case, > then there has to be some other criteria to decide how L would get "N/A" over > M or N, or M would get "N/A" over L or N, etc.). If you have a specific > question, please post back with that detailed question (and include your code > again to remove the guess work of what your code now looks like). > > Best, > > Matt > > "bluewatermist" wrote: > > > Hi > > > > Setting Wks has worked but my other problem is if for example "PAYDETAILS" > > doesn't exist in the text file how can I place on the worksheet at column L, > > M or N "N/A". > > > > I have tried the below code but when it starts checking each line in the > > text file for PAYDETAILS and doesn't find it, the macro then adds "N/A" on > > the next available line until it finds the word PAYDETAILS. > > > > With Wks > > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > > .Range("L" & lngNextRow).Value = "N/A" > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > .Range("M" & lngNextRow).Value = "N/A" > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > .Range("N" & lngNextRow).Value = "N/A" > > End With > > > > > > "JLatham" wrote: > > > > > After making the changes to the Range entries (to .Range), try changing this > > > section of code a bit to make another check: > > > > > > Set Wks = Worksheets("ECI File Index") > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > > Change the Set statement and add a line of code to make it look like this > > > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > > Msgbox "Wks is now set to worksheet named [" & Wks.name & "]" > > > > > > Open strFilename For Input As vFileHandle > > > > > > Then try running it and seeing for sure that Wks got set to the worksheet. > > > Although I'd have expected an error at that Set statement if the sheet > > > doesn't exist. In looking at the code, I don't see any readily visible > > > problem that would give the error you reported; the Do and If blocks seem > > > correct and complete, which could have been a source of a false report of > > > that type if they aren't. > > > > > > > > > > > > "bluewatermist" wrote: > > > > > > > Hi Matthew > > > > > > > > Thank you for the quick reply. I have added those extra dots but I'm > > > > getting a runtime error '91' object variable or with block variable not set. > > > > Error is highlighting on the first lngNextRow = ..... > > > > > > > > many thanks > > > > Frederic > > > > > > > > "Matthew Herbert" wrote: > > > > > > > > > Frederic, > > > > > > > > > > I just noticed that two of the "lngNextRow = ..." lines are missing the "." > > > > > (i.e. dot) prior to the "Range" object. (This will cause problems with where > > > > > the data will be placed, and you'll have unexpected results). Simply added > > > > > the dots to the following two lines: > > > > > > > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > > > > > > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > > > > > > > > > Sorry for not reading my code over carefully prior to replying to the post. > > > > > Obviously, because I don't have the text file, the code is not tested. > > > > > > > > > > Best, > > > > > > > > > > Matt > > > > > > > > > > "bluewatermist" wrote: > > > > > > > > > > > Hi > > > > > > > > > > > > I'm hoping you can help. I'm trying to search the next line of a text file > > > > > > for a word called PAYDETAILS. If the word isn't there then in the worksheet > > > > > > i need to place "N/A". > > > > > > > > > > > > I have placed a shortened version of the program. I appreciate any help. > > > > > > > > > > > > Regards > > > > > > Frederic > > > > > > > > > > > > sub readtext() > > > > > > > > > > > > Dim strTextLine As String > > > > > > Dim strFilename As String > > > > > > Dim vFileHandle As Integer > > > > > > > > > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > > > > > > > > > > > If Dir(strFilename) = "" Then > > > > > > MsgBox ("File Not Found") > > > > > > Exit Sub > > > > > > End If > > > > > > > > > > > > vFileHandle = FreeFile > > > > > > > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > > > > > Do While Not EOF(vFileHandle) > > > > > > Line Input #vFileHandle, strTextLine > > > > > > > > > > > > > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > > > > > Sheets("ECI File Index").Select > > > > > > NextRow = Range("O65536").End(xlUp).Row + 1 > > > > > > Range("O" & NextRow).Select > > > > > > ActiveCell = Mid(strTextLine, 40, 77) > > > > > > > > > > > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > > > > > Sheets("ECI File Index").Select > > > > > > NextRow = Range("L65536").End(xlUp).Row + 1 > > > > > > Range("L" & NextRow).Select > > > > > > ActiveCell = Mid(strTextLine, 11, 5) > > > > > > > > > > > > NextRow = Range("M65536").End(xlUp).Row + 1 > > > > > > Range("M" & NextRow).Select > > > > > > ActiveCell = Mid(strTextLine, 16, 8) > > > > > > > > > > > > NextRow = Range("N65536").End(xlUp).Row + 1 > > > > > > Range("N" & NextRow).Select > > > > > > ActiveCell = Mid(strTextLine, 24, 12) > > > > > > > > > > > > end if > > > > > > loop > > > > > > Close vFileHandle > > > > > > > > > > > > end sub

Fredric, I've altered your code a little bit. I added a Case statement instead of your If, ElseIf statements. (I think that the Case statement is a bit easier to read. If you haven't used a Case statement before, the VBE Help documentation has some good information on how it works. It acts very similar to the If...Then statement. Also, take note of the comments listed in the code below). I'm still not 100% sure what you are trying to achieve. What your code is set up to do is the following: 1. Test for "CEG_HEADER". If it exists, do something and move to the next line of the text file. 2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME" exists, do something and move to the next line of the text file. 3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc. ..... If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR", "SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----". Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"] isn't there then in the worksheet I need to place 'N/A'," as noted in your first post. I say this because, as noted above, if "PAYDETAILS" does NOT exist (meaning that none of the other words exist either and the code has reached the "Case Else" section), then insert the "----". Are you familiar with the Debugging tools inside VBE? If not, then at least use the following to test and evaluate your code: In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow you to evaluate your code one line at a time. As you evaluate your code (i.e. press the F8 key) you will see a yellow line highlight syntax. As you press F8, you will see the yellow line advance one line at a time. I think that doing this simple exercise will help you see how your logic is behaving and will hopefully help you see where/why the logic is not behaving as you anticipated. Otherwise, you'll have to really spell out what you are looking for. For example, I don't know what you mean or intend when you say "...the information retreived made only 4 rows on the worksheet, [but] columns L, or M, or N used up 57 rows." Do you mean that columns L, or M, or N should have used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2 rows only instead of spread out and separated by "----"?). How is the data supposed to look? (For example, assuming from your post that the dollar column is "A" and the subsequent columns move in alphabetical order [i.e. A1 = $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 = ----, etc. ] there should be the four rows of data (A1:C4) as shown in the post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 = $101.59; F3:G4 = ----). Best, Matt Sub ReadText() Dim strTextLine As String Dim strFilename As String Dim strNewFilepath As String Dim intFileHandle As Integer Dim Wks As Worksheet Dim lngNextRow As Long 'should this file have a ".txt" extension? If so, then ' you'll have to adjust the suggestion for strNewFilepath ' listed below strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" If Dir(strFilename) = "" Then MsgBox "File Not Found" Exit Sub End If intFileHandle = FreeFile 'consider the following instead, which finds the first space, ' working right to left, in order to get the "new" filename: 'strNewFilepath = Right(strFilename, Len(strFilename) - InStrRev(strFilename, " ")) strNewFilepath = Right(strFilename, 31) Open strFilename For Input As intFileHandle Set Wks = ThisWorkbook.Worksheets("ECI File Index") With Wks Do While Not EOF(intFileHandle) Line Input #intFileHandle, strTextLine Select Case Left(strTextLine, 10) Case "CEG_HEADER" lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1 .Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77) Case "FILENAME" lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1 .Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44) Case "INTRCHGHDR" lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1 .Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10) lngNextRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1 .Range("E" & lngNextRow).Value = Mid(strTextLine, 191, 8) Case "RECIPNTDTL" lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1 .Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76) Case "SPRPRODHDR" lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1 .Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11) lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 .Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76) With .Range("H" & lngNextRow) If .Offset(0, 7) = "" Then .Offset(0, 7).Value = "-----" 'double check this Offset .Offset(0, -6).Value = "--" 'double check this Offset .Offset(0, -7).Value = strNewFilepath ElseIf .Offset(0, 7) <> "-----" And .Offset(0, 7) <> "" Then .Offset(0, -6).Value = "Y" 'double check this Offset .Offset(0, -7).Value = strNewFilepath End If End With Case "SPRCONTBTN" lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1 .Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13) lngNextRow = .Range("J" & .Rows.Count).End(xlUp).Row + 1 .Range("J" & lngNextRow).Value = Mid(strTextLine, 40, 8) Case "PAYDETAILS" lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 .Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5) lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 .Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8) lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 .Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12) Case Else lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 .Range("L" & lngNextRow).Value = "-----" lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 .Range("M" & lngNextRow).Value = "-----" lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 .Range("N" & lngNextRow).Value = "-----" End Select Loop End With Close intFileHandle End Sub "bluewatermist" wrote: > Hi Matthew > > This problem was my first original question, but I have probably not stated > it clearly. > The text files have lots of other information which I don't require. What > is happening with the macro below is that if for example the macro doesn't > find on the next line the word "PAYDETAILS" it places "-----" on column L, or > M, or N. Therefore if the information retreived made only 4 rows on the work > sheet, columns L, or M or N used up 57 rows. I have place an example below > and the code. > > Hope i have made it clearer > Frederic > > $773.75 17092009 SUPERLIFE PTY ----- ----- ----- > $1015.90 17092009 SUPERLIFE PTY ----- ----- ----- > $315.00 11092009 SUPERLIFE PTY ----- ----- ----- > $142.50 17092009 SUPERLIFE PTY ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > CHQ 12082009 $77.37 > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > ----- ----- ----- > CHQ 12082009 $101.59 > ----- ----- ----- > ----- ----- ----- > > > Sub ReadText() > > Dim strTextLine As String > Dim strFilename As String > Dim vFileHandle As Integer > Dim Wks As Worksheet > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > If Dir(strFilename) = "" Then > MsgBox ("File Not Found") > Exit Sub > End If > > vFileHandle = FreeFile > > newfilepath = Right(strFilename, 31) > > Open strFilename For Input As vFileHandle > > Do While Not EOF(vFileHandle) > Line Input #vFileHandle, strTextLine > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > With Wks > NextRow = Range("O65536").End(xlUp).Row + 1 > Range("O" & NextRow).Select > ActiveCell = Mid(strTextLine, 40, 77) > End With > ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then > With Wks > NextRow = Range("C65536").End(xlUp).Row + 1 > Range("C" & NextRow).Select > ActiveCell = Mid(strTextLine, 11, 44) > End With > > ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then > With Wks > NextRow = Range("D65536").End(xlUp).Row + 1 > Range("D" & NextRow).Select > ActiveCell = Mid(strTextLine, 148, 10) > > NextRow = Range("E65536").End(xlUp).Row + 1 > Range("E" & NextRow).Select > ActiveCell = Mid(strTextLine, 191, 8) > End With > ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then > With Wks > NextRow = Range("K65536").End(xlUp).Row + 1 > Range("K" & NextRow).Select > ActiveCell = Mid(strTextLine, 11, 76) > End With > ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then > With Wks > NextRow = Range("G65536").End(xlUp).Row + 1 > Range("G" & NextRow).Select > ActiveCell = Mid(strTextLine, 31, 11) > > NextRow = Range("H65536").End(xlUp).Row + 1 > Range("H" & NextRow).Select > ActiveCell = Mid(strTextLine, 51, 76) > > If ActiveCell.Offset(0, 7) = "" Then > ActiveCell.Offset(0, 7).Select > Selection.Value = "-----" > ActiveCell.Offset(0, -13).Select > Selection.Value = "--" > ActiveCell.Offset(0, -1).Select > Selection.Value = newfilepath > ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> "" > Then > ActiveCell.Offset(0, -6).Select > Selection.Value = "Y" > ActiveCell.Offset(0, -1).Select > Selection.Value = newfilepath > End If > End With > ElseIf InStr(1, Left(strTextLine, 10), "SPRCONTBTN") > 0 Then > With Wks > NextRow = Range("I65536").End(xlUp).Row + 1 > Range("I" & NextRow).Select > ActiveCell = Mid(strTextLine, 11, 13) > > NextRow = Range("J65536").End(xlUp).Row + 1 > Range("J" & NextRow).Select > ActiveCell = Mid(strTextLine, 40, 8) > End With > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > With Wks > NextRow = Range("L65536").End(xlUp).Row + 1 > Range("L" & NextRow).Select > ActiveCell = Mid(strTextLine, 11, 5) > > NextRow = Range("M65536").End(xlUp).Row + 1 > Range("M" & NextRow).Select > ActiveCell = Mid(strTextLine, 16, 8) > > NextRow = Range("N65536").End(xlUp).Row + 1 > Range("N" & NextRow).Select > ActiveCell = Mid(strTextLine, 24, 12) > End With > else > Else > With Wks > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > .Range("L" & lngNextRow).Value = "-----" > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > .Range("M" & lngNextRow).Value = "-----" > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > .Range("N" & lngNextRow).Value = "-----" > End With > End If > > Loop > > Close vFileHandle > > End Sub > > > "Matthew Herbert" wrote: > > > Frederic, > > > > I'm implying that you still have a question with the code, but I don't know > > what that question may be. I think the question might be something like "how > > do I set up putting the "N/A" in column L, M, or N"? (If this is the case, > > then there has to be some other criteria to decide how L would get "N/A" over > > M or N, or M would get "N/A" over L or N, etc.). If you have a specific > > question, please post back with that detailed question (and include your code > > again to remove the guess work of what your code now looks like). > > > > Best, > > > > Matt > > > > "bluewatermist" wrote: > > > > > Hi > > > > > > Setting Wks has worked but my other problem is if for example "PAYDETAILS" > > > doesn't exist in the text file how can I place on the worksheet at column L, > > > M or N "N/A". > > > > > > I have tried the below code but when it starts checking each line in the > > > text file for PAYDETAILS and doesn't find it, the macro then adds "N/A" on > > > the next available line until it finds the word PAYDETAILS. > > > > > > With Wks > > > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > > > .Range("L" & lngNextRow).Value = "N/A" > > > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > > .Range("M" & lngNextRow).Value = "N/A" > > > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > > .Range("N" & lngNextRow).Value = "N/A" > > > End With > > > > > > > > > "JLatham" wrote: > > > > > > > After making the changes to the Range entries (to .Range), try changing this > > > > section of code a bit to make another check: > > > > > > > > Set Wks = Worksheets("ECI File Index") > > > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > > > > > Change the Set statement and add a line of code to make it look like this > > > > > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > > > Msgbox "Wks is now set to worksheet named [" & Wks.name & "]" > > > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > Then try running it and seeing for sure that Wks got set to the worksheet. > > > > Although I'd have expected an error at that Set statement if the sheet > > > > doesn't exist. In looking at the code, I don't see any readily visible > > > > problem that would give the error you reported; the Do and If blocks seem > > > > correct and complete, which could have been a source of a false report of > > > > that type if they aren't. > > > > > > > > > > > > > > > > "bluewatermist" wrote: > > > > > > > > > Hi Matthew > > > > > > > > > > Thank you for the quick reply. I have added those extra dots but I'm > > > > > getting a runtime error '91' object variable or with block variable not set. > > > > > Error is highlighting on the first lngNextRow = ..... > > > > > > > > > > many thanks > > > > > Frederic > > > > > > > > > > "Matthew Herbert" wrote: > > > > > > > > > > > Frederic, > > > > > > > > > > > > I just noticed that two of the "lngNextRow = ..." lines are missing the "." > > > > > > (i.e. dot) prior to the "Range" object. (This will cause problems with where > > > > > > the data will be placed, and you'll have unexpected results). Simply added > > > > > > the dots to the following two lines: > > > > > > > > > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > > > > > > > > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > > > > > > > > > > > Sorry for not reading my code over carefully prior to replying to the post. > > > > > > Obviously, because I don't have the text file, the code is not tested. > > > > > > > > > > > > Best, > > > > > > > > > > > > Matt > > > > > > > > > > > > "bluewatermist" wrote: > > > > > > > > > > > > > Hi > > > > > > > > > > > > > > I'm hoping you can help. I'm trying to search the next line of a text file > > > > > > > for a word called PAYDETAILS. If the word isn't there then in the worksheet > > > > > > > i need to place "N/A". > > > > > > > > > > > > > > I have placed a shortened version of the program. I appreciate any help. > > > > > > > > > > > > > > Regards > > > > > > > Frederic > > > > > > > > > > > > > > sub readtext() > > > > > > > > > > > > > > Dim strTextLine As String > > > > > > > Dim strFilename As String > > > > > > > Dim vFileHandle As Integer > > > > > > > > > > > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570.txt" > > > > > > > > > > > > > > If Dir(strFilename) = "" Then > > > > > > > MsgBox ("File Not Found") > > > > > > > Exit Sub > > > > > > > End If > > > > > > > > > > > > > > vFileHandle = FreeFile > > > > > > > > > > > > > > Open strFilename For Input As vFileHandle > > > > > > > > > > > > > > Do While Not EOF(vFileHandle) > > > > > > > Line Input #vFileHandle, strTextLine > > > > > > > > > > > > > > > > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > > > > > > Sheets("ECI File Index").Select > > > > > > > NextRow = Range("O65536").End(xlUp).Row + 1 > > > > > > > Range("O" & NextRow).Select > > > > > > > ActiveCell = Mid(strTextLine, 40, 77) > > > > > > > > > > > > > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > > > > > > Sheets("ECI File Index").Select > > > > > > > NextRow = Range("L65536").End(xlUp).Row + 1 > > > > > > > Range("L" & NextRow).Select

Hi Matthew Thank you for persevering with me and i really appreciate how you have shown me a simpler and better way to write the code. My main problem still exists though. As it is reading line by line I need the following to occur. Usely in the text file the word SPRCONTBTN is before PAYDETAILS, but sometimes there won't be the word PAYDETAILS in the next line. So then I am trying to make that if the next line doesn't have PAYDETAILS, then in the worksheet there will be either "N/A" or "-----". Currently it is reading every line and if the next line doesn't contain PAYDETAILS it is adding "-----" in the worksheet. If the text file has say 50 lines it will start placing "-----" on the worksheets in the relevant columns until it finds the word PAYDETAILS and then it will add payment type, date and dollar amount. I'm hoping this is clearer. Regards Frederic "Matthew Herbert" wrote: > Fredric, > > I've altered your code a little bit. I added a Case statement instead of > your If, ElseIf statements. (I think that the Case statement is a bit easier > to read. If you haven't used a Case statement before, the VBE Help > documentation has some good information on how it works. It acts very > similar to the If...Then statement. Also, take note of the comments listed > in the code below). > > I'm still not 100% sure what you are trying to achieve. What your code is > set up to do is the following: > 1. Test for "CEG_HEADER". If it exists, do something and move to the next > line of the text file. > 2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME" > exists, do something and move to the next line of the text file. > 3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc. > .... > If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR", > "SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----". > > Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"] > isn't there then in the worksheet I need to place 'N/A'," as noted in your > first post. I say this because, as noted above, if "PAYDETAILS" does NOT > exist (meaning that none of the other words exist either and the code has > reached the "Case Else" section), then insert the "----". > > Are you familiar with the Debugging tools inside VBE? If not, then at least > use the following to test and evaluate your code: > In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow > you to evaluate your code one line at a time. As you evaluate your code > (i.e. press the F8 key) you will see a yellow line highlight syntax. As you > press F8, you will see the yellow line advance one line at a time. I think > that doing this simple exercise will help you see how your logic is behaving > and will hopefully help you see where/why the logic is not behaving as you > anticipated. Otherwise, you'll have to really spell out what you are looking > for. > > For example, I don't know what you mean or intend when you say "...the > information retreived made only 4 rows on the worksheet, [but] columns L, or > M, or N used up 57 rows." Do you mean that columns L, or M, or N should have > used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2 > rows only instead of spread out and separated by "----"?). How is the data > supposed to look? (For example, assuming from your post that the dollar > column is "A" and the subsequent columns move in alphabetical order [i.e. A1 > = $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 = > ----, etc. ] there should be the four rows of data (A1:C4) as shown in the > post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 = > $101.59; F3:G4 = ----). > > Best, > > Matt > > Sub ReadText() > > Dim strTextLine As String > Dim strFilename As String > Dim strNewFilepath As String > Dim intFileHandle As Integer > Dim Wks As Worksheet > Dim lngNextRow As Long > > 'should this file have a ".txt" extension? If so, then > ' you'll have to adjust the suggestion for strNewFilepath > ' listed below > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > If Dir(strFilename) = "" Then > MsgBox "File Not Found" > Exit Sub > End If > > intFileHandle = FreeFile > > 'consider the following instead, which finds the first space, > ' working right to left, in order to get the "new" filename: > 'strNewFilepath = Right(strFilename, Len(strFilename) - > InStrRev(strFilename, " ")) > strNewFilepath = Right(strFilename, 31) > > Open strFilename For Input As intFileHandle > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > With Wks > Do While Not EOF(intFileHandle) > Line Input #intFileHandle, strTextLine > > Select Case Left(strTextLine, 10) > Case "CEG_HEADER" > lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1 > .Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77) > > Case "FILENAME" > lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1 > .Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44) > > Case "INTRCHGHDR" > lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1 > .Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10) > > lngNextRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1 > .Range("E" & lngNextRow).Value = Mid(strTextLine, 191, 8) > > Case "RECIPNTDTL" > lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1 > .Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76) > > Case "SPRPRODHDR" > lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1 > .Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11) > > lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 > .Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76) > > With .Range("H" & lngNextRow) > If .Offset(0, 7) = "" Then > .Offset(0, 7).Value = "-----" > 'double check this Offset > .Offset(0, -6).Value = "--" > 'double check this Offset > .Offset(0, -7).Value = strNewFilepath > > ElseIf .Offset(0, 7) <> "-----" And .Offset(0, 7) <> "" > Then > .Offset(0, -6).Value = "Y" > 'double check this Offset > .Offset(0, -7).Value = strNewFilepath > End If > End With > > Case "SPRCONTBTN" > lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1 > .Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13) > > lngNextRow = .Range("J" & .Rows.Count).End(xlUp).Row + 1 > .Range("J" & lngNextRow).Value = Mid(strTextLine, 40, 8) > > Case "PAYDETAILS" > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > .Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5) > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > .Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8) > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > .Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12) > > Case Else > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > .Range("L" & lngNextRow).Value = "-----" > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > .Range("M" & lngNextRow).Value = "-----" > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > .Range("N" & lngNextRow).Value = "-----" > End Select > Loop > End With > > Close intFileHandle > > End Sub > > > "bluewatermist" wrote: > > > Hi Matthew > > > > This problem was my first original question, but I have probably not stated > > it clearly. > > The text files have lots of other information which I don't require. What > > is happening with the macro below is that if for example the macro doesn't > > find on the next line the word "PAYDETAILS" it places "-----" on column L, or > > M, or N. Therefore if the information retreived made only 4 rows on the work > > sheet, columns L, or M or N used up 57 rows. I have place an example below > > and the code. > > > > Hope i have made it clearer > > Frederic > > > > $773.75 17092009 SUPERLIFE PTY ----- ----- ----- > > $1015.90 17092009 SUPERLIFE PTY ----- ----- ----- > > $315.00 11092009 SUPERLIFE PTY ----- ----- ----- > > $142.50 17092009 SUPERLIFE PTY ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > CHQ 12082009 $77.37 > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > ----- ----- ----- > > CHQ 12082009 $101.59 > > ----- ----- ----- > > ----- ----- ----- > > > > > > Sub ReadText() > > > > Dim strTextLine As String > > Dim strFilename As String > > Dim vFileHandle As Integer > > Dim Wks As Worksheet > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > > > If Dir(strFilename) = "" Then > > MsgBox ("File Not Found") > > Exit Sub > > End If > > > > vFileHandle = FreeFile > > > > newfilepath = Right(strFilename, 31) > > > > Open strFilename For Input As vFileHandle > > > > Do While Not EOF(vFileHandle) > > Line Input #vFileHandle, strTextLine > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > With Wks > > NextRow = Range("O65536").End(xlUp).Row + 1 > > Range("O" & NextRow).Select > > ActiveCell = Mid(strTextLine, 40, 77) > > End With > > ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then > > With Wks > > NextRow = Range("C65536").End(xlUp).Row + 1 > > Range("C" & NextRow).Select > > ActiveCell = Mid(strTextLine, 11, 44) > > End With > > > > ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then > > With Wks > > NextRow = Range("D65536").End(xlUp).Row + 1 > > Range("D" & NextRow).Select > > ActiveCell = Mid(strTextLine, 148, 10) > > > > NextRow = Range("E65536").End(xlUp).Row + 1 > > Range("E" & NextRow).Select > > ActiveCell = Mid(strTextLine, 191, 8) > > End With > > ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then > > With Wks > > NextRow = Range("K65536").End(xlUp).Row + 1 > > Range("K" & NextRow).Select > > ActiveCell = Mid(strTextLine, 11, 76) > > End With > > ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then > > With Wks > > NextRow = Range("G65536").End(xlUp).Row + 1 > > Range("G" & NextRow).Select > > ActiveCell = Mid(strTextLine, 31, 11) > > > > NextRow = Range("H65536").End(xlUp).Row + 1 > > Range("H" & NextRow).Select > > ActiveCell = Mid(strTextLine, 51, 76) > > > > If ActiveCell.Offset(0, 7) = "" Then > > ActiveCell.Offset(0, 7).Select > > Selection.Value = "-----" > > ActiveCell.Offset(0, -13).Select > > Selection.Value = "--" > > ActiveCell.Offset(0, -1).Select > > Selection.Value = newfilepath > > ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> "" > > Then > > ActiveCell.Offset(0, -6).Select > > Selection.Value = "Y" > > ActiveCell.Offset(0, -1).Select > > Selection.Value = newfilepath > > End If > > End With > > ElseIf InStr(1, Left(strTextLine, 10), "SPRCONTBTN") > 0 Then > > With Wks > > NextRow = Range("I65536").End(xlUp).Row + 1 > > Range("I" & NextRow).Select > > ActiveCell = Mid(strTextLine, 11, 13) > > > > NextRow = Range("J65536").End(xlUp).Row + 1 > > Range("J" & NextRow).Select > > ActiveCell = Mid(strTextLine, 40, 8) > > End With > > ElseIf InStr(1, Left(strTextLine, 10), "PAYDETAILS") > 0 Then > > With Wks > > NextRow = Range("L65536").End(xlUp).Row + 1 > > Range("L" & NextRow).Select > > ActiveCell = Mid(strTextLine, 11, 5) > > > > NextRow = Range("M65536").End(xlUp).Row + 1 > > Range("M" & NextRow).Select

Frederic, This last post is getting closer to being much more clear (or at least I now have an idea of what you are trying to do). Because I don't have the actual text file, the code below is written on the fly. As a result, it's not tested, and it isn't necessarily the "most efficient" way to do things (but it is a good way to illustrate some additional programming concepts that you may have not seen, namely arrays). I commented the code, so be sure to read those comments as you look at the code below. You'll have to check the logic inside the "Case "SPRCONTBTN"" syntax because I have no way of knowing what you are truly looking for (i.e. check the If...Then block of code). I simply added the "new" code in this section based on your recent post. Your statement of "...usually in the text file the word SPRCONTBTN is before PAYDETAILS..." is a bit bothersome to me. Do you need to test for "PAYDETAILS" as the next line for each of the Case conditions? If this is what you need, then let me know and we'll create an additional procedure/function to systematically make this check very easy for each of the Case conditions. However, you'll at least have one example within "SPRCONTBTN" to work with (and test) for now. In addition to the change within "Case "SPRCONTBTN"", please ensure that the "Case "PAYDETAILS"" is doing what you anticipate. Also, you'll have to determine if you now need code in the "Case Else" statement. (It's okay if you don't have anything in the "Case Else" statement). You'll notice that I left a simple comment in the Case Else section. (Again, use F8 repeatedly to see how the logic of the program is behaving). Let me know if this gets you closer to where you need to be. Best, Matt Sub ReadText() Dim strTextLine As String Dim strFilename As String Dim strNewFilepath As String Dim intFileHandle As Integer Dim Wks As Worksheet Dim lngNextRow As Long Dim strArr() As String Dim strNext As String Dim lngCnt As Long 'should this file have a ".txt" extension? If so, then ' you'll have to adjust the suggestion for strNewFilepath ' listed below strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" If Dir(strFilename) = "" Then MsgBox "File Not Found" Exit Sub End If intFileHandle = FreeFile 'consider the following instead, which finds the first space, ' working right to left, in order to get the "new" filename: 'strNewFilepath = Right(strFilename, Len(strFilename) - _ ' InStrRev(strFilename, " ")) strNewFilepath = Right(strFilename, 31) Open strFilename For Input As intFileHandle 'load the text file data into an array. Each line ' will be an item in the array. This will allow ' you to access any line of the text file at any ' time in order to determine if "PAYDETAILS" ' exists. (See the For...Next loop below). lngCnt = 0 Do While Not EOF(intFileHandle) Line Input #intFileHandle, strTextLine ReDim Preserve strArr(lngCnt) strArr(lngCnt) = strTextLine lngCnt = lngCnt + 1 Loop Close intFileHandle Set Wks = ThisWorkbook.Worksheets("ECI File Index") With Wks 'loop through each of the items in the array (i.e. ' loop through each of the text file lines) For lngCnt = LBound(strArr) To UBound(strArr) 'evaluate the first 10 characters of each item Select Case Left(strArr(lngCnt), 10) Case "CEG_HEADER" lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1 .Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77) Case "FILENAME" lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1 .Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44) Case "INTRCHGHDR" lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1 .Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10) lngNextRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1 .Range("E" & lngNextRow).Value = Mid(strTextLine, 191, 8) Case "RECIPNTDTL" lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1 .Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76) Case "SPRPRODHDR" lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1 .Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11) lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 .Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76) With .Range("H" & lngNextRow) If .Offset(0, 7) = "" Then .Offset(0, 7).Value = "-----" 'double check this Offset .Offset(0, -6).Value = "--" 'double check this Offset .Offset(0, -7).Value = strNewFilepath ElseIf .Offset(0, 7) <> "-----" And _ .Offset(0, 7) <> "" Then .Offset(0, -6).Value = "Y" 'double check this Offset .Offset(0, -7).Value = strNewFilepath End If End With Case "SPRCONTBTN" lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1 .Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13) lngNextRow = .Range("J" & .Rows.Count).End(xlUp).Row + 1 .Range("J" & lngNextRow).Value = Mid(strTextLine, 40, 8) '---------------------------------------------------- 'NEW CODE BLOCK FOR TESTING THE "NEXT" LINE FOR ' PAYDETAILS 'if you are in the last position of the array, then ' trying to get the (last position + 1) will throw ' an error because (last position + 1) doesn't exist. ' (last position + 1) pertains to the ' strNext = strArr(lngCnt + 1) below. If lngCnt <> UBound(strArr) Then strNext = strArr(lngCnt + 1) 'if the next line does NOT have "PAYDETAILS", insert ' "----" into the desired cells If Left(strNext, 10) <> "PAYDETAILS" Then lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 .Range("L" & lngNextRow).Value = "-----" lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 .Range("M" & lngNextRow).Value = "-----" lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 .Range("N" & lngNextRow).Value = "-----" End If End If '---------------------------------------------------- Case "PAYDETAILS" lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 .Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5) lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 .Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8) lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 .Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12) Case Else 'you need to decide if you need to add code in here End Select Next lngCnt End With End Sub "bluewatermist" wrote: > Hi Matthew > > Thank you for persevering with me and i really appreciate how you have > shown me a simpler and better way to write the code. > > My main problem still exists though. As it is reading line by line I need > the following to occur. Usely in the text file the word SPRCONTBTN is before > PAYDETAILS, but sometimes there won't be the word PAYDETAILS in the next > line. So then I am trying to make that if the next line doesn't have > PAYDETAILS, then in the worksheet there will be either "N/A" or "-----". > Currently it is reading every line and if the next line doesn't contain > PAYDETAILS it is adding "-----" in the worksheet. If the text file has say > 50 lines it will start placing "-----" on the worksheets in the relevant > columns until it finds the word PAYDETAILS and then it will add payment type, > date and dollar amount. > > I'm hoping this is clearer. > Regards > Frederic > > "Matthew Herbert" wrote: > > > Fredric, > > > > I've altered your code a little bit. I added a Case statement instead of > > your If, ElseIf statements. (I think that the Case statement is a bit easier > > to read. If you haven't used a Case statement before, the VBE Help > > documentation has some good information on how it works. It acts very > > similar to the If...Then statement. Also, take note of the comments listed > > in the code below). > > > > I'm still not 100% sure what you are trying to achieve. What your code is > > set up to do is the following: > > 1. Test for "CEG_HEADER". If it exists, do something and move to the next > > line of the text file. > > 2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME" > > exists, do something and move to the next line of the text file. > > 3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc. > > .... > > If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR", > > "SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----". > > > > Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"] > > isn't there then in the worksheet I need to place 'N/A'," as noted in your > > first post. I say this because, as noted above, if "PAYDETAILS" does NOT > > exist (meaning that none of the other words exist either and the code has > > reached the "Case Else" section), then insert the "----". > > > > Are you familiar with the Debugging tools inside VBE? If not, then at least > > use the following to test and evaluate your code: > > In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow > > you to evaluate your code one line at a time. As you evaluate your code > > (i.e. press the F8 key) you will see a yellow line highlight syntax. As you > > press F8, you will see the yellow line advance one line at a time. I think > > that doing this simple exercise will help you see how your logic is behaving > > and will hopefully help you see where/why the logic is not behaving as you > > anticipated. Otherwise, you'll have to really spell out what you are looking > > for. > > > > For example, I don't know what you mean or intend when you say "...the > > information retreived made only 4 rows on the worksheet, [but] columns L, or > > M, or N used up 57 rows." Do you mean that columns L, or M, or N should have > > used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2 > > rows only instead of spread out and separated by "----"?). How is the data > > supposed to look? (For example, assuming from your post that the dollar > > column is "A" and the subsequent columns move in alphabetical order [i.e. A1 > > = $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 = > > ----, etc. ] there should be the four rows of data (A1:C4) as shown in the > > post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 = > > $101.59; F3:G4 = ----). > > > > Best, > > > > Matt > > > > Sub ReadText() > > > > Dim strTextLine As String > > Dim strFilename As String > > Dim strNewFilepath As String > > Dim intFileHandle As Integer > > Dim Wks As Worksheet > > Dim lngNextRow As Long > > > > 'should this file have a ".txt" extension? If so, then > > ' you'll have to adjust the suggestion for strNewFilepath > > ' listed below > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > > > If Dir(strFilename) = "" Then > > MsgBox "File Not Found" > > Exit Sub > > End If > > > > intFileHandle = FreeFile > > > > 'consider the following instead, which finds the first space, > > ' working right to left, in order to get the "new" filename: > > 'strNewFilepath = Right(strFilename, Len(strFilename) - > > InStrRev(strFilename, " ")) > > strNewFilepath = Right(strFilename, 31) > > > > Open strFilename For Input As intFileHandle > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > > > With Wks > > Do While Not EOF(intFileHandle) > > Line Input #intFileHandle, strTextLine > > > > Select Case Left(strTextLine, 10) > > Case "CEG_HEADER" > > lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1 > > .Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77) > > > > Case "FILENAME" > > lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1 > > .Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44) > > > > Case "INTRCHGHDR" > > lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1 > > .Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10) > > > > lngNextRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1 > > .Range("E" & lngNextRow).Value = Mid(strTextLine, 191, 8) > > > > Case "RECIPNTDTL" > > lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1 > > .Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76) > > > > Case "SPRPRODHDR" > > lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1 > > .Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11) > > > > lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 > > .Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76) > > > > With .Range("H" & lngNextRow) > > If .Offset(0, 7) = "" Then > > .Offset(0, 7).Value = "-----" > > 'double check this Offset > > .Offset(0, -6).Value = "--" > > 'double check this Offset > > .Offset(0, -7).Value = strNewFilepath > > > > ElseIf .Offset(0, 7) <> "-----" And .Offset(0, 7) <> "" > > Then > > .Offset(0, -6).Value = "Y" > > 'double check this Offset > > .Offset(0, -7).Value = strNewFilepath > > End If > > End With > > > > Case "SPRCONTBTN" > > lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1 > > .Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13) > > > > lngNextRow = .Range("J" & .Rows.Count).End(xlUp).Row + 1 > > .Range("J" & lngNextRow).Value = Mid(strTextLine, 40, 8) > > > > Case "PAYDETAILS" > > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > > .Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5) > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > .Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8) > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > .Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12) > > > > Case Else > > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > > .Range("L" & lngNextRow).Value = "-----" > > > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > > .Range("M" & lngNextRow).Value = "-----" > > > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > > .Range("N" & lngNextRow).Value = "-----" > > End Select > > Loop > > End With > > > > Close intFileHandle > > > > End Sub > > > > > > "bluewatermist" wrote: > > > > > Hi Matthew > > > > > > This problem was my first original question, but I have probably not stated > > > it clearly. > > > The text files have lots of other information which I don't require. What > > > is happening with the macro below is that if for example the macro doesn't > > > find on the next line the word "PAYDETAILS" it places "-----" on column L, or > > > M, or N. Therefore if the information retreived made only 4 rows on the work > > > sheet, columns L, or M or N used up 57 rows. I have place an example below > > > and the code. > > > > > > Hope i have made it clearer > > > Frederic > > > > > > $773.75 17092009 SUPERLIFE PTY ----- ----- ----- > > > $1015.90 17092009 SUPERLIFE PTY ----- ----- ----- > > > $315.00 11092009 SUPERLIFE PTY ----- ----- ----- > > > $142.50 17092009 SUPERLIFE PTY ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > CHQ 12082009 $77.37 > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > ----- ----- ----- > > > CHQ 12082009 $101.59 > > > ----- ----- ----- > > > ----- ----- ----- > > > > > > > > > Sub ReadText() > > > > > > Dim strTextLine As String > > > Dim strFilename As String > > > Dim vFileHandle As Integer > > > Dim Wks As Worksheet > > > > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > > > > > If Dir(strFilename) = "" Then > > > MsgBox ("File Not Found") > > > Exit Sub > > > End If > > > > > > vFileHandle = FreeFile > > > > > > newfilepath = Right(strFilename, 31) > > > > > > Open strFilename For Input As vFileHandle > > > > > > Do While Not EOF(vFileHandle) > > > Line Input #vFileHandle, strTextLine > > > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > > > > > If InStr(1, Left(strTextLine, 10), "CEG_HEADER") > 0 Then > > > With Wks > > > NextRow = Range("O65536").End(xlUp).Row + 1 > > > Range("O" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 40, 77) > > > End With > > > ElseIf InStr(1, Left(strTextLine, 10), "FILENAME") > 0 Then > > > With Wks > > > NextRow = Range("C65536").End(xlUp).Row + 1 > > > Range("C" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 11, 44) > > > End With > > > > > > ElseIf InStr(1, Left(strTextLine, 10), "INTRCHGHDR") > 0 Then > > > With Wks > > > NextRow = Range("D65536").End(xlUp).Row + 1 > > > Range("D" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 148, 10) > > > > > > NextRow = Range("E65536").End(xlUp).Row + 1 > > > Range("E" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 191, 8) > > > End With > > > ElseIf InStr(1, Left(strTextLine, 10), "RECIPNTDTL") > 0 Then > > > With Wks > > > NextRow = Range("K65536").End(xlUp).Row + 1 > > > Range("K" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 11, 76) > > > End With > > > ElseIf InStr(1, Left(strTextLine, 10), "SPRPRODHDR") > 0 Then > > > With Wks > > > NextRow = Range("G65536").End(xlUp).Row + 1 > > > Range("G" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 31, 11) > > > > > > NextRow = Range("H65536").End(xlUp).Row + 1 > > > Range("H" & NextRow).Select > > > ActiveCell = Mid(strTextLine, 51, 76) > > > > > > If ActiveCell.Offset(0, 7) = "" Then > > > ActiveCell.Offset(0, 7).Select > > > Selection.Value = "-----" > > > ActiveCell.Offset(0, -13).Select > > > Selection.Value = "--" > > > ActiveCell.Offset(0, -1).Select > > > Selection.Value = newfilepath > > > ElseIf ActiveCell.Offset(0, 7) <> "-----" And ActiveCell.Offset(0, 7) <> "" > > > Then > > > ActiveCell.Offset(0, -6).Select > > > Selection.Value = "Y"

Hi Matthew Thank you so much for your patience. This is exactly what I need. The only change I made was to the below code from strTextLine to strArr(IngCnt) ..Range("O" & lngNextRow).Value = Mid(strArr(lngCnt), 40, 77) Other Case conditions besides PAYDETAILS sometimes do not occur. Is the new code block that you gave me the best way for all the other Case conditions? I really just need to check if the Case conditions exist in the text file and if they don't then add "-----" in the worksheet under the corresponding column. Thanks for your help :-) Frederic "Matthew Herbert" wrote: > Frederic, > > This last post is getting closer to being much more clear (or at least I now > have an idea of what you are trying to do). Because I don't have the actual > text file, the code below is written on the fly. As a result, it's not > tested, and it isn't necessarily the "most efficient" way to do things (but > it is a good way to illustrate some additional programming concepts that you > may have not seen, namely arrays). I commented the code, so be sure to read > those comments as you look at the code below. > > You'll have to check the logic inside the "Case "SPRCONTBTN"" syntax because > I have no way of knowing what you are truly looking for (i.e. check the > If...Then block of code). I simply added the "new" code in this section > based on your recent post. Your statement of "...usually in the text file > the word SPRCONTBTN is before PAYDETAILS..." is a bit bothersome to me. Do > you need to test for "PAYDETAILS" as the next line for each of the Case > conditions? If this is what you need, then let me know and we'll create an > additional procedure/function to systematically make this check very easy for > each of the Case conditions. However, you'll at least have one example > within "SPRCONTBTN" to work with (and test) for now. > > In addition to the change within "Case "SPRCONTBTN"", please ensure that the > "Case "PAYDETAILS"" is doing what you anticipate. Also, you'll have to > determine if you now need code in the "Case Else" statement. (It's okay if > you don't have anything in the "Case Else" statement). You'll notice that I > left a simple comment in the Case Else section. (Again, use F8 repeatedly to > see how the logic of the program is behaving). > > Let me know if this gets you closer to where you need to be. > > Best, > > Matt > > Sub ReadText() > > Dim strTextLine As String > Dim strFilename As String > Dim strNewFilepath As String > Dim intFileHandle As Integer > Dim Wks As Worksheet > Dim lngNextRow As Long > Dim strArr() As String > Dim strNext As String > Dim lngCnt As Long > > 'should this file have a ".txt" extension? If so, then > ' you'll have to adjust the suggestion for strNewFilepath > ' listed below > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > If Dir(strFilename) = "" Then > MsgBox "File Not Found" > Exit Sub > End If > > intFileHandle = FreeFile > > 'consider the following instead, which finds the first space, > ' working right to left, in order to get the "new" filename: > 'strNewFilepath = Right(strFilename, Len(strFilename) - _ > ' InStrRev(strFilename, " ")) > strNewFilepath = Right(strFilename, 31) > > Open strFilename For Input As intFileHandle > > 'load the text file data into an array. Each line > ' will be an item in the array. This will allow > ' you to access any line of the text file at any > ' time in order to determine if "PAYDETAILS" > ' exists. (See the For...Next loop below). > lngCnt = 0 > Do While Not EOF(intFileHandle) > Line Input #intFileHandle, strTextLine > ReDim Preserve strArr(lngCnt) > strArr(lngCnt) = strTextLine > lngCnt = lngCnt + 1 > Loop > Close intFileHandle > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > With Wks > > 'loop through each of the items in the array (i.e. > ' loop through each of the text file lines) > For lngCnt = LBound(strArr) To UBound(strArr) > > 'evaluate the first 10 characters of each item > Select Case Left(strArr(lngCnt), 10) > > Case "CEG_HEADER" > lngNextRow = .Range("O" & .Rows.Count).End(xlUp).Row + 1 > .Range("O" & lngNextRow).Value = Mid(strTextLine, 40, 77) > > Case "FILENAME" > lngNextRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1 > .Range("C" & lngNextRow).Value = Mid(strTextLine, 11, 44) > > Case "INTRCHGHDR" > lngNextRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1 > .Range("D" & lngNextRow).Value = Mid(strTextLine, 148, 10) > > lngNextRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1 > .Range("E" & lngNextRow).Value = Mid(strTextLine, 191, 8) > > Case "RECIPNTDTL" > lngNextRow = .Range("K" & .Rows.Count).End(xlUp).Row + 1 > .Range("K" & lngNextRow).Value = Mid(strTextLine, 11, 76) > > Case "SPRPRODHDR" > lngNextRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1 > .Range("G" & lngNextRow).Value = Mid(strTextLine, 31, 11) > > lngNextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 > .Range("H" & lngNextRow).Value = Mid(strTextLine, 51, 76) > > With .Range("H" & lngNextRow) > If .Offset(0, 7) = "" Then > .Offset(0, 7).Value = "-----" > 'double check this Offset > .Offset(0, -6).Value = "--" > 'double check this Offset > .Offset(0, -7).Value = strNewFilepath > > ElseIf .Offset(0, 7) <> "-----" And _ > .Offset(0, 7) <> "" Then > .Offset(0, -6).Value = "Y" > 'double check this Offset > .Offset(0, -7).Value = strNewFilepath > End If > End With > > Case "SPRCONTBTN" > lngNextRow = .Range("I" & .Rows.Count).End(xlUp).Row + 1 > .Range("I" & lngNextRow).Value = Mid(strTextLine, 11, 13) > > lngNextRow = .Range("J" & .Rows.Count).End(xlUp).Row + 1 > .Range("J" & lngNextRow).Value = Mid(strTextLine, 40, 8) > > '---------------------------------------------------- > 'NEW CODE BLOCK FOR TESTING THE "NEXT" LINE FOR > ' PAYDETAILS > > 'if you are in the last position of the array, then > ' trying to get the (last position + 1) will throw > ' an error because (last position + 1) doesn't exist. > ' (last position + 1) pertains to the > ' strNext = strArr(lngCnt + 1) below. > If lngCnt <> UBound(strArr) Then > > strNext = strArr(lngCnt + 1) > > 'if the next line does NOT have "PAYDETAILS", insert > ' "----" into the desired cells > If Left(strNext, 10) <> "PAYDETAILS" Then > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row > + 1 > .Range("L" & lngNextRow).Value = "-----" > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row > + 1 > .Range("M" & lngNextRow).Value = "-----" > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row > + 1 > .Range("N" & lngNextRow).Value = "-----" > End If > End If > '---------------------------------------------------- > > Case "PAYDETAILS" > lngNextRow = .Range("L" & .Rows.Count).End(xlUp).Row + 1 > .Range("L" & lngNextRow).Value = Mid(strTextLine, 11, 5) > > lngNextRow = .Range("M" & .Rows.Count).End(xlUp).Row + 1 > .Range("M" & lngNextRow).Value = Mid(strTextLine, 16, 8) > > lngNextRow = .Range("N" & .Rows.Count).End(xlUp).Row + 1 > .Range("N" & lngNextRow).Value = Mid(strTextLine, 24, 12) > > Case Else > 'you need to decide if you need to add code in here > End Select > Next lngCnt > End With > > End Sub > > > "bluewatermist" wrote: > > > Hi Matthew > > > > Thank you for persevering with me and i really appreciate how you have > > shown me a simpler and better way to write the code. > > > > My main problem still exists though. As it is reading line by line I need > > the following to occur. Usely in the text file the word SPRCONTBTN is before > > PAYDETAILS, but sometimes there won't be the word PAYDETAILS in the next > > line. So then I am trying to make that if the next line doesn't have > > PAYDETAILS, then in the worksheet there will be either "N/A" or "-----". > > Currently it is reading every line and if the next line doesn't contain > > PAYDETAILS it is adding "-----" in the worksheet. If the text file has say > > 50 lines it will start placing "-----" on the worksheets in the relevant > > columns until it finds the word PAYDETAILS and then it will add payment type, > > date and dollar amount. > > > > I'm hoping this is clearer. > > Regards > > Frederic > > > > "Matthew Herbert" wrote: > > > > > Fredric, > > > > > > I've altered your code a little bit. I added a Case statement instead of > > > your If, ElseIf statements. (I think that the Case statement is a bit easier > > > to read. If you haven't used a Case statement before, the VBE Help > > > documentation has some good information on how it works. It acts very > > > similar to the If...Then statement. Also, take note of the comments listed > > > in the code below). > > > > > > I'm still not 100% sure what you are trying to achieve. What your code is > > > set up to do is the following: > > > 1. Test for "CEG_HEADER". If it exists, do something and move to the next > > > line of the text file. > > > 2. If "CEG_HEADER" does NOT exist, test for "FILENAME". If "FILENAME" > > > exists, do something and move to the next line of the text file. > > > 3. If "FILENAME" does NOT exist, test for "INTRCHGHDR". Etc. > > > .... > > > If "CEG_HEADER", "FILENAME", "INTRCHGHDR", "RECIPNTDTL", "SPRPRODHDR", > > > "SPRCONTBTN", AND "PAYDETAILS" do NOT exist, insert the "----". > > > > > > Thus, I don't exactly know what you mean by "If the word ["PAYDETAILS"] > > > isn't there then in the worksheet I need to place 'N/A'," as noted in your > > > first post. I say this because, as noted above, if "PAYDETAILS" does NOT > > > exist (meaning that none of the other words exist either and the code has > > > reached the "Case Else" section), then insert the "----". > > > > > > Are you familiar with the Debugging tools inside VBE? If not, then at least > > > use the following to test and evaluate your code: > > > In the "Debug" menu, there is a "Step Into F8" option. Step Into will allow > > > you to evaluate your code one line at a time. As you evaluate your code > > > (i.e. press the F8 key) you will see a yellow line highlight syntax. As you > > > press F8, you will see the yellow line advance one line at a time. I think > > > that doing this simple exercise will help you see how your logic is behaving > > > and will hopefully help you see where/why the logic is not behaving as you > > > anticipated. Otherwise, you'll have to really spell out what you are looking > > > for. > > > > > > For example, I don't know what you mean or intend when you say "...the > > > information retreived made only 4 rows on the worksheet, [but] columns L, or > > > M, or N used up 57 rows." Do you mean that columns L, or M, or N should have > > > used up only 2 rows (i.e. the two "CHQ" lines from your post should be on 2 > > > rows only instead of spread out and separated by "----"?). How is the data > > > supposed to look? (For example, assuming from your post that the dollar > > > column is "A" and the subsequent columns move in alphabetical order [i.e. A1 > > > = $773.75, B1 = 17092009, C1 = SUPERLIFE PTY, D1 = ----, E1 = ----, F1 = > > > ----, etc. ] there should be the four rows of data (A1:C4) as shown in the > > > post, but E1 = CHQ, F1 = 12082009, G1 = $77.37; E2 = CHQ, F2 = 12082009, G2 = > > > $101.59; F3:G4 = ----). > > > > > > Best, > > > > > > Matt > > > > > > Sub ReadText() > > > > > > Dim strTextLine As String > > > Dim strFilename As String > > > Dim strNewFilepath As String > > > Dim intFileHandle As Integer > > > Dim Wks As Worksheet > > > Dim lngNextRow As Long > > > > > > 'should this file have a ".txt" extension? If so, then > > > ' you'll have to adjust the suggestion for strNewFilepath > > > ' listed below > > > strFilename = "C:\Russell PSGCD.XFR.GRPN.D130809.F.S00570" > > > > > > If Dir(strFilename) = "" Then > > > MsgBox "File Not Found" > > > Exit Sub > > > End If > > > > > > intFileHandle = FreeFile > > > > > > 'consider the following instead, which finds the first space, > > > ' working right to left, in order to get the "new" filename: > > > 'strNewFilepath = Right(strFilename, Len(strFilename) - > > > InStrRev(strFilename, " ")) > > > strNewFilepath = Right(strFilename, 31) > > > > > > Open strFilename For Input As intFileHandle > > > > > > Set Wks = ThisWorkbook.Worksheets("ECI File Index") > > > > > > With Wks > > > Do While Not EOF(intFileHandle)

Similiar Articles:

Read the next line in a text file - microsoft.public.excel ...
Hi I'm hoping you can help. I'm trying to search the next line of a text file for a word called PAYDETAILS. If the word isn't there then in the...

Last line in txt file - microsoft.public.windows.powershell ...
Read the next line in a text file - microsoft.public.excel ... I'm trying to search the next line of a text file for a word called PAYDETAILS. ... Frederic, This last post ...

vbs to read 3rd column field data & check if exist in other file ...
If the data can be found in the second text file, then read next line in first text file. If the data cannot be found in the second text file, then this field ...

reading text out of word docs - microsoft.public.word.vba.general ...
Read the next line in a text file - microsoft.public.excel ... reading text out of word docs - microsoft.public.word.vba.general ... How do >>> I move to the next line and ...

Simple GUI text input into Excel - microsoft.public.windows ...
... end of line - microsoft.public.vb ..... solution which turned out to be simple ... Help!! importing a text file into access with ... Read the next line in a text file ...

importing multiple text files into Excel with corresponding ...
Import-Module command failing - microsoft ... It can batch convert multiple files, and supports ... ... Read the next line in a text file - microsoft.public.excel ...

Write a line in a text file with quotes in it - microsoft.public ...
I need to write a line in a text file that contains ... starts with a " and ends with the next ... public.vc.mfc An article on how to read/write a line from/to a text file ...

Integration Manager Script Excel File - microsoft.public ...
Integration Manager - Script to validate company id? - microsoft ... microsoft.public ... Read the next line in a text file - microsoft.public.excel ...

Reading Text Files w/commas in the data - microsoft.public.dotnet ...
Parsing CSV files - microsoft.public.vc.mfc > CStdioFile::ReadString will read a text file a line at a time. The ... Tom Archer illustrates how to parse comma-delimited ...

Compare two text files and output third - microsoft.public ...
... OpenTextFile("File2.txt", ForReading) ' Next part should read from text csv file ... What I am trying to do is output to a file that contains the following lines: 1 ...

reading text out of word docs - microsoft.public.dotnet.languages ...
How do >>> I move to the next line and read the entire line? 'move to ... other criteria to decide how L would get "N/A" over ... Reading text of a Word doc or rtf file ...

Different outcome of adding ribbon via xml instead of in a table ...
Read the next line in a text file - microsoft.public.excel ... I added a Case statement instead of your If, ElseIf ... As a result, it's not tested, and it isn't ...

Parsing CSV files - microsoft.public.vc.mfc
>> > > CStdioFile::ReadString will read a text file a line at a time. > > The CString class ... terminate the parse and set up so ... the next parse starts the next line ...

How can I read CSV file using VBA? - microsoft.public.access ...
>[quoted text clipped - 3 lines] >> "c:\csv_files\12 ... Read line into variable. <MORE CODE GOES HERE> Next I Wher more code appears you would parse the text as ...

Automatically rename files based on text in a file - microsoft ...
... e. something like read file "listing.txt" for each line in ... sort_item+$searchfile next The ... rename files based on text in a file - microsoft ..... line in (Get ...

Reading large csv-file and removing duplicates - microsoft.public ...
Reading a 4GB text file into memory is likely to require 8 GB of memory ... at the first letter of the index entry on each line. Pur A - B in one file, C - D in the next ...

Highlighting text with F8 key - microsoft.public.mac.office.word ...
Read the next line in a text file - microsoft.public.excel ... I'm trying to search the next line of a text file for a word ... As you evaluate your code (i.e. press the ...

Notepad or .txt file to excel - microsoft.public.excel.worksheet ...
It copies very well each line into ... way after each space in the notepad file to put that information in the next ... Of .txt File Is there other way to read a text file ...

Reading Unicode text files. - microsoft.public.vc.mfc
An article on how to read/write a line from/to a text file encoded by ANSI/Unicode type.; Author: cokkiy; Updated: 18 Jan 2006; Section: Files and Folders; Chapter ...

Find next record according to specific criteria - microsoft.public ...
Hi, I am using a simple basic criteria to search for a certain ... Read the next line in a text file ... Click: > > MsgBox ... if the record exists already. ... the ...

READ: Read the Next Line of an ASCII Text File
READ will read the next line of the named opened file (the argument of READ is the logical name of a file previously opened with OPEN). This line then becomes the ...

Microsoft Windows 2000 Scripting Guide - Reading Text Files
... the first five characters in a line, using Skip(5). Read the sixth character, using Read(1). Skip to the next line of the file. Listing 4.41 Reading a Fixed-Width Text File

how do I get perl to skip lines reading a text file?
Hi all, I've matched a string in a text file, but I what to skip and read the next dozen or so lines. Don't quite know how to do this simple task.

Read a certain line in a text file - CodeProject
This Article will show you how to read a line in a text file. We will write a small class that ... Next

Reading Second Line From Text File - Java | Dream.In.Code
Each number is read serperatly. I have managed to read from the first line, but how do i read the next line. The text file looks like this: 2 7 2 7 7 5 9 6 2 5

Visual C# .NET and Text Files: Read a file line by line
Quite often, you'll want to read the contents of a text file line by line, as part of some processing operation. You can then move on to the next ...

Reading a text file line by line - Microsoft Corporation: Software ...
Hi, I have been trying to show the first line from a text file in a textbox, and then be able move to the next and previous lines by means of 2 button ...

Read text file, write each line to different text box
I have a simple text file that I want to read line by line, then put each line into a different text box. ... read the code back into the text boxes the next ...

How to read the LAST NON-BLANK line from a text file? - Vista Forums
The next line in a text file: PowerShell: How do I read a text file and sort text by fixed positions? PowerShell: blank line in txt file problem: PowerShell

C# Read Text File Line-by-Line : C# 411
Lets say line one from the file is birthday i want it to string birthday as birthdate then go to the next line and ... /i want to read a text file line by line which is ...

7/21/2012 6:56:38 PM