loop through text files using VBA and extract information

[greenkevin86]

I work for a sheet metal manufacturer and would like to use VBA to loop through a directory of text files (that are created as setup sheets when machine programs are generated) and extract meaningful information to Excel for further analysis. All of the text files are generally structured the same and below is a sample. I would like to:
1) Extract the Part Number, Prog Number, Blank Size, Material, Parts/Sheet, and Total Time (under Time Calculations) and place in a table on one sheet.
2) Extract all the tooling information into a table on a separate sheet. There would be 3 columns: a) prog number (which would repeat for each row of data within the same text file but would link each tool to the program), b) the tool # (e.g. T311), and c) the description (e.g. Sqr 0.531)
setup sheet template.JPG

[PaulSP8]

Should be doable

Can you attach a proper example of the Text file, and any variations, I noticed you used the term "generally structured" - why? - What differences occour?

Also if you could attach a mock up of the related output you'd like to see in Excel that would be ideal.

(Follow the instructions on the Yellow Banner above to see how to attach things )

[greenkevin86]

I've attached a representative sample of all the txt files. Note that each one of these file sets are stored in a separate directory by machine # so if the code requires slight modification for each type, that would be ok as I could manually copy and paste the 5 outputs together since this is manageable. The file extensions are ".stp" but they can be opened in notepad; I changed the extensions to txt for the purposes of being able to attach them but I'm not sure if this will affect the programming.

Really the only structural difference is that the file named SETUP SHEETS112349-02-10G-1-1 does not have any tooling information (as it is a laser), so only the first set of information (Part Number, Prog Number, Blank Size, Material, Parts/Sheet, and Total Time) would require extraction. I also realize that some of these fields are blank in the example file I attached so it is ok if there is a blank space when nothing is specified.

Ideally, it would also convert the total time to a consistent base (e.g. minutes or seconds) but I believe I could write a string formula within Excel to do this separately.

It will not let me attach the excel output at this time so I will have to post a separate reply to post example excel file that corresponds to the txt file EN02_0_2.

[greenkevin86]

Here is the excel file, notice how there are two tabs/tables of data; one for the program information and one for the tooling information. This output corresponds to the previously attached file EN02_0_2.

[Marc L]

As a starter, you must first open your workbook attachment ('cause of already existing headers),
paste the below VBA demonstration in a module
then save this workbook as binary .xlsb in the same folder where are the .stp text files to import
(or you can easily amend the Path variable within the code) :

Sub Demo1()
   Const S = "STAT/TOOL DESCRIPTION "
     Dim F%, P$, H, R, N$, L(), V, C&, W, T$(), X, Y&
         F = FreeFile
         P = ThisWorkbook.Path & Application.PathSeparator
    With Sheet1.UsedRange
         H = .Parent.Evaluate(.Rows(1).Address & "&""*:*""")
        .Offset(1).Clear
    End With
          Sheet2.UsedRange.Offset(1).Clear
          Application.ScreenUpdating = False
          R = [{1,2}]
          N = Dir(P & "*.stp")
    While N > ""
          ReDim L(1 To UBound(H))
          Open P & N For Input As #F
          V = Split(Input(LOF(F), #F), vbCrLf)
          Close #F
        For C = 1 To UBound(H)
            W = Application.Match(H(C), V, 0)
            If IsError(W) Then W = Application.Match(Replace(H(C), " ", "*"), V, 0)
            If IsNumeric(W) Then L(C) = Split(V(W - 1), ": ")(1)
        Next
        If Application.CountA(L) Then
                W = Empty
                X = Split(L(6))
            For C = 1 To UBound(X) Step 2
                If X(C) Like "minute*" Then W = W + Val(X(C - 1)) / 1440 Else _
                If X(C) Like "second*" Then W = W + Val(X(C - 1)) / 86400
            Next
                L(6) = W
                R(1) = R(1) + 1
                Sheet1.Cells(R(1), 1).Resize(, UBound(L)).Value = L
        End If
            W = Application.Match("*" & S & "*", V, 0)
        If IsNumeric(W) Then
                ReDim T(1 To UBound(V) - W + 1, 2)
                C = InStr(V(W - 1), S)
                Y = 0
            For W = W To UBound(V)
                   X = Split(RTrim(Mid(V(W), C, 29)), "/")
                If UBound(X) = 1 Then
                   Y = Y + 1
                   T(Y, 0) = L(2)
                   T(Y, 1) = RTrim(X(0))
                   T(Y, 2) = X(1)
                End If
            Next
            If Y Then
                Sheet2.Cells(R(2), 1).Resize(Y, 3).Value2 = T
                R(2) = R(2) + Y
            End If
        End If
          N = Dir
    Wend
    With Sheet1.UsedRange.Rows
        .Item("2:" & .Count).Columns(6).NumberFormat = "[m]:ss_W"
        .Columns("A:D").AutoFit
    End With
         Sheet2.UsedRange.Range("A:A,C:C").Columns.AutoFit
         Application.ScreenUpdating = True
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[greenkevin86]

Worked great, thank you!

[greenkevin86]

One more (hopefully simple) request; three of the five file structures have a "material thickness" attribute I would also like to include as a column in the table on the first sheet (this could just be left blank for the other files that don't have this attribute). See the file above EN0114-04-T5_B_1.txt for an example.

So the columns in the table on the first sheet would now be: Part Number, Prog Number, Blank Size, Material, Material Thickness, Parts/Sheet, and Total Time. Is there an updated version of the code you could send that will add in Material thickness as a column? I've included an updated export file example of what the structure of the table on the first sheet will look like.

[Marc L]

According to the initial attachment no need any mod if you add the new header directly in column G,
'cause of the time calculation in column F …

[Marc L]

Or just swap headers between columns E & G …