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

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 jowen@virtualtwigs.com