Gday all,

I have been using this code from Pearsons http://www.cpearson.com/excel/imptext.htm#Import and changed it slightly (bold red).

However, i want to also use this file to import tab-delimited values. What is the delimiter character i need to stipulate for this?



Private Sub CommandButton1_Click()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
    (filefilter:="CSV Files(*.csv),*.txt,All Files (*.*),*.*")
If FName = False Then
    MsgBox "Error: You didn't select a CSV file to import, please run process again"
    Exit Sub
End If

' Set delimiter as comma
Sep = ","


ImportTextFile CStr(FName), Sep

End Sub

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

'Status bar indicating current process
Application.StatusBar = "Please wait: Data currently being imported"

' set A1 as active code, will eventually change coding to always place in cell A1
Range("A1").Select

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1