Try:
Code:
Public Sub DataImport()
Application.ScreenUpdating = False
Dim FName As Variant, Sep As String
FName = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If
Sep = InputBox("Enter a single delimiter character.", "Import Text File", ",")
If Sep = "" Then
Sep = Chr(9)
End If
ActiveSheet.Range(Replace(ActiveSheet.Range("A1").CurrentRegion.Address, "$A$1", "$A$2")).ClearContents
Call ImportTextFile(CStr(FName), Sep, 1, 2)
Application.ScreenUpdating = True
End Sub
Public Sub ImportTextFile(FName As String, Sep As String, iCol As Long, iRow As Long)
Dim RowNdx As Long, ColNdx As Long, SaveColNdx As Long, Pos As Long, NextPos As Long
Dim TempVal As Variant, WholeLine As String
On Error GoTo ErrExit:
SaveColNdx = iCol
RowNdx = iRow
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
ErrExit:
On Error GoTo 0
Close #1
End Sub
Note that the code allows you to specify the separator (in case the file doesn't use commas - if you leave the separator spec empty, tabs will be assumed) and the starting row/column for the data import. As coded row 2 column A is the assumed starting point.