# A simple vba macro to import and correct a tab delimited text file

By James Owen Feb 20 2021 • 1 min read

Recently a friend asked me to make a spreadsheet that imported a short tab delimited text file generated as a report from a commercial product and then made some calculations. We tried importing the page with Excel's `Import` menu item, but there were some corrections that had to be made (because of spurious extra tabs in the input text file). By writing a short vba macro, I:

Could make this a one button operation
Could correct the problems in the text globally before placing items in the cells

I decided to share the code in the hope that it might save someone some time and grief in trying other approaches

# Google search, then hack away

I tried to find an excel vba script that placed an item at a time in cells, but I could not find one easily, so I wrote the following

Sub RoundedRectangle1_Click()
    Dim Ret
    Ret = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    If Ret <> False Then
        readFile (Ret)
    End If
End Sub
Sub readFile(fname)
    Dim MyData As String, strData() As String, tabData() As String

    Open fname For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    MyData = Replace(MyData, vbTab + vbTab, vbTab)
    strData() = Split(MyData, vbLf)
    For Row = 1 To UBound(strData) + 1
        tabData() = Split(strData(Row - 1), vbTab)
        For Column = 1 To UBound(tabData) + 1
            cellStr = Trim(tabData(Column - 1))
            If (Not cellStr = vbNullString) Then
                Cells(Row, Column) = cellStr
            End If
            
        Next
    Next
End Sub

# Notes

This code will write over many existing cells in your spreadsheet, so beware
I replaced double tabs with single tabs, and that fixed my cell placement problems in rows. You may need more sophisticated replacements there
The double loop limits were derived empirically
I had to use vbLf for windows txt files and vbNl for mac txt files
This code places cells starting at 1,1, but you can change it to suit your needs

# Conclusion

I hope you can adapt this simple snippet to solve your problem. I have only used this on 4x20 cells, and on spreadsheets I had backed up. The useful trick here was to correct the text before placing items in cells. If you have any comments or questions, please contact me at james.owen@virtualtwigs.com