extracting a single line of data from a folder of .txt files to excel

Question

I really hope i can get your help. Ive been searching high and low for what is probably a simple solution.

We have hundreds of txt files that all relate to cnc programs. Unfortunately there has been a historical lack of control in keeping a strict numbering system for parts and operations.

I have to extract the 3rd and 4th line of txt from each file into an excel doc so we can remunerate some and catalogue all for referencing.

So far the closest thing i've found to what i'm after is in the thread

Extract a single line of data from numerous text files and import into Excel

however i cannot make it work - my excel knowledge is good but not with macros. the start of every txt file is

#1 <blank line>
#2 %
#3 O00000 (part description)
#4 (part descriptio)
#5 rest of program.

. . . as requested ive included the code i'm trying to modify.

Private Sub CommandButton1_Click()
    Dim filename As String, nextrow As Long, MyFolder As String
    Dim MyFile As String, text As String, textline As String, prog As String

    MyFolder = "M:\CNC Programs\Haas lathe programs\Haas ST30 programs\Programs\Programs in .txt format"
    MyFile = Dir(MyFolder & "*.txt")

    Do While MyFile <> ""
        Open (MyFolder & MyFile) For Input As #1
        Do Until EOF(1)
            Line Input #3, textline
            text = text & textline 
        Loop
        Close #1
        MyFile = Dir()
        Debug.Print text
        nextrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row + 1
        Sheet1.Cells(nextrow, "A").Value = Mid(text, prog)
        text = "" 'reset text
    Loop
End Sub

Welcome to StackOverflow. Please note, that this is not a free code-writing service. Yet, we are eager to help fellow programmers (and aspirants) writing their own code. Please read the help topics on [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask). You might also want to [take the tour](http://stackoverflow.com/tour) and earn a badge while doing so. Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. Also, please elaborate why the referenced Q&A does not solve your problem. — Ralph, Jul 29 '16 at 10:12
Could you please elaborate, on what exactly is not working. Have you tried to debug your code one step at a time? — Mister 832, Jul 29 '16 at 10:23

score 0 · Accepted Answer · answered Jul 29 '16 at 10:20

0

Since you don't have much experience with vba, here are some points you might want to google and put the results together

Open a Text File in Excel VBA (you should start with that, and try to read one file)
Loop through all files in a certain folder with Excel VBA

Your code needs to do the following.

get a list of all the files you want to load
open each file of that list
read the 3rd and 4th line from that file
copy the lines to excel.

There are lots of examples on the internet, once you learn how to search

answered Jul 29 '16 at 10:20

Mister 832

1,177
1
15
34

Thanks for the advice. You are right, I don't know much about this at all. Ill start looking at your suggested line of learning!! – Sam McClintock Jul 29 '16 at 10:23
Maybe check out some videos or tutorials on debugging excel vba. This will help you, to narrow down the line where things go wrong. For example: Is the value read correctly from the file? Is it written in the right cell? aso – Mister 832 Jul 29 '16 at 10:28

score 0 · Answer 2 · answered Aug 03 '16 at 05:54

after a few hours and some help from a friend we came up with this, does more or less what i needed.

I know how I want to improve it, I just need to figure that out now. Again, I'm sure its a simple trick. Perseverance!!!

What I want to do with this now, if you can, is take my directory
'M:\CNC Programs\Haas Mills programs\All Mill Programs .txt format\' and scan all subsequent folders for said .txt files and extract the same info into a workbook.

If I figure it out I'll update the post again. Thanks for setting me on the right path Mister 832.

Private Sub CommandButton1_Click()
  Dim MyMask As String, nextrow As Long, MyFolder As String
  Dim MyFile As String, text As String, textline As String
  Dim posCommentStart As String, posCommentFinish
  Dim iLine As Integer

MyFolder = "M:\CNC Programs\Haas Mills programs\All Mill Programs .txt format\HAAS MINI-MILL BALLPADS & BALLPINS\"
MyMask = MyFolder & "*.txt"
MyFile = Dir(MyMask)

Do While MyFile <> ""
    iLine = 0
    Open (MyFolder & MyFile) For Input As #1
    Do Until EOF(1) Or iLine >= 4
        iLine = iLine + 1
        Line Input #1, textline
        If iLine >= 3 Then
           text = text & textline 'second loop text is already stored -> see reset text
        End If

    Loop
    Close #1
    MyFile = Dir()
    Debug.Print text

    posCommentStart = InStr(text, "(")
    posCommentFinish = InStr(text, ")")

    If posCommentStart > 0 Then

        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

        ActiveSheet.Cells(nextrow, "A").Value = Left(text, posCommentStart - 2)
        ActiveSheet.Cells(nextrow, "B").Value = Mid(text, posCommentStart +        1, posCommentFinish - posCommentStart - 1)

    End If

    text = "" 'reset text

Loop
End Sub

Here is one link that might help you. http://stackoverflow.com/questions/21583678/vba-trying-to-open-all-workbooks-in-a-folder you can also do a * search with the fso object. — Mister 832, Aug 03 '16 at 09:38

extracting a single line of data from a folder of .txt files to excel

2 Answers2