Practical Web Programming

Tuesday, November 13, 2007

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

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
Close #1
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"
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


Recent Post