need to import a text file into a EXCEL using VBA.

I need to import a text file into excel using VBA.
The Excel file has the first row as the Column names. The text file values are separated by tab delimiter.
I would prefer a icon on the excel tab to run this macro. Thanks.
karthik80cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Chip Pearson has a nice sample:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
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:

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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

Since ImportTextFile takes input parameters, it should be called from other VBA code.
If you want to prompt the user for a file name and separator character, use code like the following:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheImport
' This prompts the user for a FileName as separator character
' and then calls ImportTextFile.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheImport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = vbTab 'Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ImportTextFile FName:=CStr(FileName), Sep:=CStr(Sep)
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheImport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karthik80cAuthor Commented:
Thank you Macro for a quick solution. The code is working. But the code is writing in the first row. I would like the first row to be left as I have to write a header information.
The first row should have Title, Color and Year.
Only from the second row the text file records should be imported.
Is there a possibility to remove the duplicates when importing the text file into excel file. Thanks.
0
MacroShadowCommented:
Change row 19 to
RowNdx = ActiveCell.row + 1

Open in new window

0
MacroShadowCommented:
Removing duplicates is a separate issue and should be asked in a separate question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.