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:
Other People Are Reading
Things you 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 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
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
Display each word through the Immediate window from the String array:
Xcntr = 0
Do While (tmpArray(Xcntr) <> "")
Debug.Print tmpArray(Xcntr) Xcntr = Xcntr + 1
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for