How to Read a Line of Tab-Delimited Text in VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Read a Line of Tab-Delimited Text in VBA
Read a text file in Visual Basic for Applications. (Comstock/Comstock/Getty Images)

Being able to read a line of tab-delimited text using Visual Basic for Applications and parsing each of the words can make your VBA application more dynamic. Often programmers need to read tab-delimited files and display the contents of those files. In VBA, use the "OpenTextfile" method to open a text file that contains tab-delimited data. Search each line for tabs using the "vbTab" VBA constant. This process is useful when you need to quickly find tabs in a string instead of reading each character in the string.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Microsoft Excel

Show MoreHide

Instructions

  1. 1

    Click the Windows Start button and type "Notepad" in the "Search Programs and Files" text box. Press "Enter" to launch Notepad. Type "This," press "Tab," type "is," press "Tab," type "a," press "Tab," type "tab," press "Tab," type "delimited," press "Tab" and type "sentence." Press "Ctrl" and "S" to save the file in "C:\myTextFile.txt."

  2. 2

    Launch Microsoft Excel, click the "Developer" tab and click "Visual Basic" to launch the VB Editor window. Click the "Insert" menu and click "Module" to add a new code module. Click the "Tools" menu and click "References." Check the box next to "Microsoft Scripting Runtime" and click "OK."

  3. 3

    Type the following to create a new sub procedure:

    Private Sub readTabDelimited()

  4. 4

    Add the following code to declare your variables:

    Dim oFSO As New FileSystemObject

    Dim OFS

    Dim sText As String

    Dim tmpArray(10) As String

    Dim pos As Integer

    Dim Xcntr As Integer

  5. 5

    Open and read the text file created in Step 1:

    Set OFS = oFSO.OpenTextFile("C:\myTextFile.txt")

    Do Until oFS.AtEndOfStream

    sText = oFS.ReadLine
    

    Loop

  6. 6

    Find each tab in the tab delimited sentence and add each word to the String array:

    pos = InStr(1, sText, vbTab, vbTextCompare)

    Do While (pos <> 0)

    tmpArray(Xcntr) = Left(sText, pos - 1)
    
    sText = Right(sText, Len(sText) - pos)
    
    pos = InStr(1, sText, vbTab, vbTextCompare)
    
    Xcntr = Xcntr + 1
    
    If (pos = 0) Then
    
        tmpArray(Xcntr) = sText
    
    End If
    

    Loop

  7. 7

    Display each word through the Immediate window from the String array:

    Xcntr = 0

    Do While (tmpArray(Xcntr) <> "")

    Debug.Print tmpArray(Xcntr)
    
    Xcntr = Xcntr + 1
    

    Loop

  8. 8

    End the procedure by typing "End Sub" as the final line of VBA code. Press "Ctrl" and "G" to display the "Immediate" window and press "F5" to run the procedure.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.