Help getting text data into Excel


New Member
Aug 6, 2010

I'm trying to get data into excel from several text files in multiple directories. My lab has a program which generates sample data like this:

Sequence Data
\___ Sample 1
| |
| \___ Report.txt
\___ Sample 2
| |
| \___ Report.txt

The program also has the ability to manually click every sample in the GUI and export the data to a CSV or DIV file. However, that takes forever and I'd rather just get the same data from the Report.txt files. We generate a lot of data, so it would be nice if we had a program that let the user choose a 'Sequence Data' directory and then automatically scan the Report.txt files for all the samples for values we are interested in and place them into the appropriate columns in an already existing worksheet.

This is my first time using VBA outside of basic "macro recorder" tutorials so can you give some tips as to what libraries/functions/modules to use?


Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.


New Member
Aug 6, 2010
I've spent some time going through the Excel help system as well as many stackoverflow articles and have arrived at this code which finds all of the samples and opens up the Report text files for reading line by line. The trouble is that I don't know how I can search the strings and extract the desired information. I found the inStr function but I cannot get it to find the second line text at all. i.e.

Sample Name: sample 4 24H DH

When I use the Debugger, all the characters coming out of readline have spaces between them. Is that related?

Anyway, here is my code:

' Demand Explicit Variable declaration
Option Explicit

' A subroutine for extracting data from a text file
Sub testme()

' Create space for local variables
    Dim fd As FileDialog    ' Create space for folder dialog
    Dim myFiles() As String ' Create space for dynamic array
    Dim mySamples() As String  ' ""
    Dim fCtr As Long        ' This is short for "File Counter"
    Dim sCtr As Long        ' This is short for "Sample Counter"
    Dim rowN As Long
    Dim mySample As String
    Dim myFile As String
    Dim myPath As String
    Dim wkbk As Workbook
    Dim wks As Worksheet

' ========== LOOK ====================
' The user is prompted for a directory from which all the files
' are in child directories.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        If .Show = -1 Then
            myPath = .SelectedItems(1)  ' TODO: error handling
        End If
    End With
    Set fd = Nothing

' This conditional checks to make sure myPath ends with a backslash.
' TODO: I might need to use this function later on in the next
' level - using directory paths for report.txt.
    If Right(myPath, 1) <> "\" Then
        myPath = myPath & "\"
    End If

' The way this line works isn't very intuite until after you read the
' excel documentation on the Dir function.  Dir can work differently
' each time it is called.  If you call Dir() without arguments again,
' it will return a string of the next (different) .txt file in the
' directory.  So, calling Dir once only stores the first matching
' .txt file in the directory.
    mySample = Dir(myPath & "*.D", vbDirectory)
' This conditional checks to see if there were any .D dirs found
' in the directory specified by "myPath".  It does not think to
' check subdirectories.
    If mySample = "" Then
        MsgBox "No Samples found"
        Exit Sub
    End If
' If there is atleast one directory, then that directory needs to
' have a \.  (I think)
    If Right(mySample, 1) <> "\" Then
        mySample = mySample & "\"
    End If

' Process the matching filenames into the dynamic array "myFiles()".
' The "ReDim Preserve" crap tells VBA to keep the previous values
' in the array intact while it extends the size of the array.
' "myFile" contains the path to the first matching *.D dir in the
' directory.  Calling Dir() without arguments, replaces the return
' value to the next wildcard match.
    sCtr = 0
    Do While mySample <> ""
        sCtr = sCtr + 1
        If Right(mySample, 1) <> "\" Then   ' This is hackish!
            mySample = mySample & "\"
        End If
        ReDim Preserve mySamples(1 To sCtr)
        mySamples(sCtr) = mySample
        mySample = Dir()    ' find the next *.D match

' Now I should have a dynamic array of .D directories containing
' Report.TXT files.  I need to go through each of them, looking
' for that file.

' HELP:  I have no idea what this does except that it creates a
' worksheet and then puts three headers in.
    Set wks = Workbooks.Add(1).Worksheets(1)
    wks.Range("a1").Value = "Found Sample Reports:"
' DEBUGGING: Make sure all the sample directories are being found
' Go through all the .D files in mySamples and print their names
' in the A column.
    Dim reportPath As String
    rowN = 2
    For sCtr = LBound(mySamples) To UBound(mySamples)
        reportPath = myPath & mySamples(sCtr) & "Report.TXT"
        ' ==== Should become new subroutine ====
        ' Needs: Microsoft Scripting Runtime as reference
        Dim reportObject As New Scripting.FileSystemObject
        Dim report
        Dim lineNum As Long
        lineNum = 0
        Set report = reportObject.OpenTextFile(reportPath, ForReading)
        Do Until report.AtEndOfStream
            Dim rLine As String
            lineNum = lineNum + 1
            rLine = report.ReadLine
            If lineNum = 2 Then
                Debug.Print rLine
                If InStr(rLine, ":") <> 0 Then
                    Debug.Print lineNum
                End If
            End If
            ' Look for Sample Name
        ' ==== Should become new subroutine ====
        wks.Cells(rowN, 1).Value = reportPath
        rowN = rowN + 1
    Next sCtr
' TODO this block needs to include the subroutine that adds the
' requested data to the spreadsheet in the requested spots.

' Autofit the used columns to their appropriate size.

End Sub

Thanks for your help.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back