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?

Please Login or Register  to view this content.

Chr(9)

--
Regards,
Tom Ogilvy


"marksince1984" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>