Change delimiter in import function from comma to tab

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

Chr(9)

--
Regards,
Tom Ogilvy


"marksince1984" <marksince1984.29bn8n_1150170003.3631@excelforum-nospam.com>
wrote in message
news:marksince1984.29bn8n_1150170003.3631@excelforum-nospam.com...
>
> 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?
>
>
>
>
> Code:
> --------------------
> 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
>
> --------------------
>
>
> --
> marksince1984
> ------------------------------------------------------------------------
> marksince1984's Profile:
http://www.excelforum.com/member.php...o&userid=33484
> View this thread: http://www.excelforum.com/showthread...hreadid=551278
>