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

Save

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.

Things You'll Need

  • Microsoft Excel
  • 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."

  • 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."

  • Type the following to create a new sub procedure:

    Private Sub readTabDelimited()

  • 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

  • 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

  • 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

  • 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

  • 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.

References

  • Photo Credit Comstock/Comstock/Getty Images
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!