Change delimiter in import function from comma to tab

[marksince1984]

Gday all,

I have been using this code from Pearson
http://www.cpearson.com/excel/imptext.htm#Import and changed i
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

 

[Tom Ogilvy]

Chr(9)

--
Regards,
Tom Ogilvy


"marksince1984" <[email protected]>
wrote in message
news:[email protected]...