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?


Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.


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.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...