How To Import Text File To Excel in VB6

This visual basic source code shows how to import a text file into a Microsoft Excel file.

To test this source code, add a blank Excel file named Phones.xls and a text file named 'Phones.txt' with the following text:

"Ali Ezzahir","Winnipeg","Canada","Programmer","204-1234567"
"Adam Smith","Winnipeg","Canada","Carpenter","204-8970965"
"John Smith","Winnipeg","Canada","Engineer","204-6578765"

This text is what we will import to the Phones.xls. Now open a Visual Basic project. In the form, add a commandbutton control and paste the source codes below, then run the program and click the commandbutton.

'Procedure: N/A
'Language: Visual Basic
'Parameter: N/A
'Purpose: Import text file to excel file
'Usage: N/A

'-->API DECLARATION
Private Declare Function ShellExecute Lib "shell32.dll" Alias _
        "ShellExecuteA" (ByVal hwnd As Long, _
        ByVal lpOperation As String, ByVal lpFile As String, _
        ByVal lpParameters As String, ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As Long

Private Sub Command1_Click()
    Dim xlApp As Excel.Application
    Dim xlsWorkBook As Excel.Workbook
    Dim xlsWorkSheet As Excel.Worksheet
    Dim J As Integer
    Dim delim As Variant
    Dim LineText(8) As String
    Dim NewLine As String
    Dim CC As String
    Dim DD As String
    Dim AA
    Dim I As Integer
    Dim W As Integer
    Dim Z As Integer
    Dim ReturnCode As Integer

    Me.MousePointer = 11
    DD = """"
    On Local Error Resume Next
    Set xlApp = New Excel.Application
    Set xlsWorkBook = xlApp.Workbooks.Add
    Set xlsWorkSheet = xlsWorkBook.Worksheets.Add
    
    delim = vbTab
    
    xlsWorkSheet.Cells(1, 1).Value = "Name"
    xlsWorkSheet.Cells(1, 2).Value = "City"
    xlsWorkSheet.Cells(1, 3).Value = "Country"
    xlsWorkSheet.Cells(1, 4).Value = "Profession"
    xlsWorkSheet.Cells(1, 5).Value = "Phone"
    xlsWorkSheet.Cells(1, 1).Font.Bold = True
    xlsWorkSheet.Cells(1, 2).Font.Bold = True
    xlsWorkSheet.Cells(1, 3).Font.Bold = True
    xlsWorkSheet.Cells(1, 4).Font.Bold = True
    xlsWorkSheet.Cells(1, 5).Font.Bold = True
    I = 1
    Open App.Path & "\Phones.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, NewLine
        I = I + 1
        AA = Split(NewLine, delim)
        For Z = LBound(AA) To UBound(AA)
            xlsWorkSheet.Cells(I, Z + 1).Value = Replace(AA(Z), DD, "")
        Next Z
    Loop
    Close #1
    xlsWorkSheet.Columns.AutoFit
    xlsWorkSheet.Cells(1, 1).Interior.ColorIndex = 20
    xlsWorkSheet.Cells(1, 2).Interior.ColorIndex = 20
    xlsWorkSheet.Cells(1, 3).Interior.ColorIndex = 20
    xlsWorkSheet.Cells(1, 4).Interior.ColorIndex = 20
    xlsWorkSheet.Cells(1, 5).Interior.ColorIndex = 20
    xlsWorkSheet.Name = "Phones"
    xlsWorkSheet.SaveAs App.Path & "\Phones.xls"
    xlApp.Quit
    Set xlsWorkSheet = Nothing
    Set xlsWorkBook = Nothing
    Set xlApp = Nothing
    Me.MousePointer = 0
    ReturnCode = ShellExecute(hwnd, "Open", App.Path & _
                                    "\Phones.xls", "", App.Path, 1)
End Sub

Private Sub Form_Load()
    Command1.Caption = "Import To Excel"
End Sub