Extracting data & filenames from multiple text files

[mike_engr]

I have a folder with 40 text files. The files contain test data from running the same test repeatedly on 8 test units, 5 times in a row. I expect to repeat this testing many times in the future, and so I am trying to speed up the data extraction time versus cutting and pasting. Once the data is properly loaded in a spreadsheet, I can calculate some statistics from the data.

I have been able to extract the device serial number and also some data values I need from these files using the attached VBA code. But this code returns the data in successive rows in a single column. I would like to extract the data from each file in a way that the data from each new file is started in the next column over from the last file. I have been unable to determine how to break the data up into columns. Also, I would like to extract the filename and display it at the beginning of each data set. Can anyone provide guidance? I am new to VBA.

Thanks,
Mech Eng VBA Noob

Sub ExtractAllLines()
    Dim fileName As String, nextrow As Long, MyFolder As String
    Dim MyFile As String, text As String, textline As String
    Dim RowCount As Long, LastCol As Long
    ActiveSheet.Select



    MyFolder = "D:\Data"
    MyFile = Dir(MyFolder & "*.txt")

    Do While MyFile <> ""
        Open (MyFolder & MyFile) For Input As #1 
        Do Until EOF(1)
            'Add each line of the text file to variable "text"
            Line Input #1 , textline
            text = text & textline
        Loop
        'close the text file
        Close #1 
        MyFile = Dir()
        
        'Find "device serial number" text
        SerNo = InStr(text, "device serial number")
        'Find "inclinometer" text
        S1Max = InStr(text, "sensor1 statistics")
        'Find "gyro" text
       S2Max = InStr(text, "sensor2 statistics")
        
        'Move to next empty row
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find device serial number
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, SerNo + 30, 10)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer x average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 120, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer y average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 132, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer z average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 145, 8)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer x std deviation
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 160, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer y std deviation
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 172, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer z std deviation
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 185, 8)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find gyro x average 72
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S2Max + 112, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find gyro y average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S2Max + 124, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find gyro z average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S2Max + 137, 8)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find gyro x standard deviation
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S2Max + 152, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find gyro y standard deviation
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S2Max + 165, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find gyro z standard deviation
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S2Max + 176, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        text = "" 'reset text
        
          
    Loop
    

    
End Sub

 

[Macropod]

Instead of:

        'Move to next empty row
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find device serial number
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, SerNo + 30, 10)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer x average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 120, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer y average
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, S1Max + 132, 9)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

etc., use:

        'Move to next empty row
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find device serial number
        ActiveSheet.Cells(nextrow, "A").Value = Mid(text, SerNo + 30, 10)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer x average
        ActiveSheet.Cells(nextrow, "B").Value = Mid(text, S1Max + 120, 9)
        
        'Find inclinometer y average
        ActiveSheet.Cells(nextrow, "C").Value = Mid(text, S1Max + 132, 9)

Extending this to insert the filename into Column A, the code would become:

        'Move to next empty row
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Write the File Name
        ActiveSheet.Cells(nextrow, "A").Value = MyFile
        
        'Find device serial number
        ActiveSheet.Cells(nextrow, "B").Value = Mid(text, SerNo + 30, 10)
        nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        'Find inclinometer x average
        ActiveSheet.Cells(nextrow, "C").Value = Mid(text, S1Max + 120, 9)
        
        'Find inclinometer y average
        ActiveSheet.Cells(nextrow, "D").Value = Mid(text, S1Max + 132, 9)