Types of Looping Construct in Visual Basic

A loop is a sequence of instructions that is continually repeated until a certain condition is reached. It is a fundamental programming idea that is commonly used in writing programs. Without looping in a programming language, hundreds to thousands of repeated computer instructions would be time consuming, if not impossible to perform.

Here are the four types of looping construct in Visual Basic.

For Loop example

Private Sub ForLoop()
  Dim intX        As Integer
    
  '-->INCREMENTING
  For intX = 0 To 10
    MsgBox "For Loop #" & intX, vbInformation, _
           "Visual Basic Looping"
  Next
    
  '-->DECREMENTING
  For intX = 10 To 0 Step -1
    MsgBox "For Loop Step -1 #" & intX, vbInformation, _
     "Visual Basic Looping"
  Next
End Sub

Do While Loop example

Private Sub DoWhileLoop()
  Dim intX        As Integer
    
  intX = 0
  Do While intX < 10
    MsgBox "Do While Loop #" & intX, vbInformation, _
           "Visual Basic Looping"
    intX = intX + 1
  Loop
End Sub

While Wend Loop example

Private Sub WhileWendLoop()
  Dim intX        As Integer
    
  intX = 0
  While intX < 10
    MsgBox "While Wend Loop #" & intX, vbInformation, _
        "Visual Basic Looping"
    intX = intX + 1
  Wend
End Sub

Do Loop Example

Private Sub DoLoop()
  Dim intX        As Integer

  intX = 0
  Do
    MsgBox "Do Loop #" & intX, vbInformation, _
         "Visual Basic Looping"
    intX = intX + 1
  Loop While intX < 10
End Sub

How to Get The RGB of a Color Value in Visual Basic

This functions will returns the red, blue and green value of a color value.

'-->RETURNS THE RED COLOR VALUE
Private Function Red(ByVal Color As Long) As Integer
    Red = Color Mod &H100
End Function

'-->RETURNS THE GREEN COLOR VALUE
Private Function Green(ByVal Color As Long) As Integer
    Green = (Color \ &H100) Mod &H100
End Function

'-->RETURNS THE BLUE COLOR VALUE
Private Function Blue(ByVal Color As Long) As Integer
    Blue = (Color \ &H10000) Mod &H100
End Function

Here's how to use this functions (see the image above for the result):

Private Sub Command1_Click()
    MsgBox "Red: " & Red(Me.BackColor) & "," & vbNewLine & _
            "Blue: " & Blue(Me.BackColor) & "," & vbNewLine & _
            "Green: " & Green(Me.BackColor), _
            vbInformation, "Form RGB Color"
End Sub

How to Full Format Date in Visual Basic

This function shows how to full format date in Visual Basic 6.

Here's how to call the function: MsgBox FullFormatDate("02/24/1978")
The result will be: Friday, 24th Mar 1978

Public Function FullFormatDate(ByVal strDate As String) As String
    Dim strDay As String

    strDay = Format(strDate, "DD")
    Select Case strDay
        Case 1, 21, 31
            strDay = Format(strDay, "#0") & "st"
        Case 2, 22
            strDay = Format(strDay, "#0") & "nd"
        Case 3, 23
            strDay = Format(strDay, "#0") & "rd"
        Case Else
            strDay = Format(strDay, "#0") & "th"
    End Select

    FullFormatDate = Format(strDate, "DDDD, ") & strDay & _
                     Format(strDate, " MMM YYYY")
End Function

Count the Forms Loaded in a Visual Basic Project

Sometimes you want to count the forms loaded in your Visual Basic project during runtime.


This simple and ready to use function will return the number of forms loaded in a project.

Public Function FormCount() As Long
    Dim frmForm As Form
    For Each frmForm In Forms
        FormCount = FormCount + 1
    Next
End Function

To use, just call the function like this.

MsgBox "# of forms loaded: " & FormCount

How to Flash a Minimized Form in the Taskbar Using API in Visual Basic

Here's a simple function to to flash a minimized form in the taskbar. It uses the FlashWindow API function and is written in Visual Basic

'-->API DECLARATION
Public Declare Function FlashWindow Lib "user32" (ByVal hWnd As Long, _
    ByVal bInvert As Long) As Long

'-->THIS SUB WILL FLASH THE MINIMIZED FORM IN THE TASKBAR
Public Sub FlashForm(lngHandle As Long, _
                     Optional intCount As Integer = 1)
    Dim intX            As Integer

    For intX = 0 To intCount
        Call FlashWindow(lngHandle, True)
    Next
End Sub

Drag PictureBox at Runtime in Visual Basic

The source codes below shows how you can drag a picturebox at runtime. To test this, copy and paste code to the declaration section of a form with a picturebox on it.

Private dblX As Double, dblY As Double
Private bolMove As Boolean

Private Sub cmdClose_Click()
   Unload Me
End Sub

Private Sub Picture1_MouseDown(Button As Integer, _
                              Shift As Integer, _
                              X As Single, Y As Single)
   If Button = 1 And Not bolMove Then
       bolMove = True
       dblX = X
       dblY = Y
   End If
End Sub

Private Sub Picture1_MouseMove(Button As Integer, _
                              Shift As Integer, _
                              X As Single, Y As Single)
   Dim tmpy As Integer
   Dim tmpx As Integer

   If bolMove Then
       If dblY > Y Then 'scroll up
           tmpy = (dblY - Y) '* 100
           Me.Picture1.Top = Me.Picture1.Top - tmpy
       Else 'scroll down
           tmpy = (Y - dblY) '* 100
           Me.Picture1.Top = Me.Picture1.Top + tmpy
       End If
       If dblX > X Then 'scroll right
           tmpx = (dblX - X) '* 100
           Me.Picture1.Left = Me.Picture1.Left - tmpx
       Else 'scroll left
           tmpx = (X - dblX) '* 100
           Me.Picture1.Left = Me.Picture1.Left + tmpx
       End If
   End If
End Sub

Private Sub Picture1_MouseUp(Button As Integer, _
                            Shift As Integer, _
                            X As Single, Y As Single)
   bolMove = False
End Sub

How to Get the File Information Using API in Visual Basic

This function takes a passed filename as an argument and returns the description of that file. For example, if you pass the filename "c:\windows\sys.com" to the function, it will return the string "MS-DOS Application". If the file doesn'texist, it will return a blank type information.

NOTE: To see the result, copy and paste the code below in the
declaration section of a form.

'Constants declaration
Const SHGFI_DISPLAYNAME = &H200
Const SHGFI_TYPENAME = &H400
Const MAX_PATH = 260
Private Type SHFILEINFO
    hIcon As Long                       '  out: icon
    iIcon As Long                       '  out: icon index
    dwAttributes As Long                '  out: SFGAO_ flags
    szDisplayName As String * MAX_PATH  '  out: display name (or path)
    szTypeName As String * 80           '  out: type name
End Type

'API declaration
Private Declare Function SHGetFileInfo Lib "shell32.dll" Alias _
                        "SHGetFileInfoA" (ByVal pszPath As String, _
                        ByVal dwFileAttributes As Long, _
                        psfi As SHFILEINFO, ByVal cbFileInfo As Long, _
                        ByVal uFlags As Long) As Long

'Gets the information of the file passed to it
Private Function GetFileInfo(strFileName) As SHFILEINFO
    Dim lngRetVal   As Long
    Dim fileInfo    As SHFILEINFO

    lngRetVal = SHGetFileInfo(strFileName, 0, fileInfo, Len(fileInfo), _
                                SHGFI_DISPLAYNAME Or SHGFI_TYPENAME)
    GetFileInfo = fileInfo
End Function

'This fucntion is used to strip al the unnecessary chr$(0)'s
Private Function StripTerminator(sInput As String) As String
    Dim ZeroPos As Integer
    'Search the position of the first chr$(0)
    ZeroPos = InStr(1, sInput, vbNullChar)
    If ZeroPos > 0 Then
        StripTerminator = Left$(sInput, ZeroPos - 1)
    Else
        StripTerminator = sInput
    End If
End Function

Private Sub Form_Load()
    Dim fileInfo As SHFILEINFO

    fileInfo = GetFileInfo("c:\autoexec.bat")
    MsgBox "Displayname: " & StripTerminator(fileInfo.szDisplayName) & _
            vbNewLine & _
           "Typename: " & StripTerminator(fileInfo.szTypeName)
End Sub

Cut, Copy, Paste and Delete Using the Clipboard Object Source Codes in Visual Basic

Are you having difficulty in doing Cut, Copy, Paste and Delete of texts routines in Visual Basic? Well, settle down now.

This source codes shows the CUT, COPY, PASTE and DELETE routines using the Clipboard object. This source codes assumes that you have four commandbutton controls (EditCut, EditCopy, EditPaste and EditDelete) in your Visual Basic project

Private Sub EditCut_Click()
    'Clear the contents of the Clipboard.
    Clipboard.Clear
    'Copy selected text to Clipboard.
    Clipboard.SetText Screen.ActiveControl.SelText
    'Delete selected text.
    Screen.ActiveControl.SelText = ""
End Sub

Private Sub EditCopy_Click()
    'Clear the contents of the Clipboard.
    Clipboard.Clear
    'Copy selected text to Clipboard.
    Clipboard.SetText Screen.ActiveControl.SelText
End Sub

Private Sub EditPaste_Click()
    'Place text from Clipboard into active control.
    Screen.ActiveControl.SelText = Clipboard.GetText()
End Sub

Private Sub EditDelete_Click()
    'Delete selected text.
    Screen.ActiveControl.SelText = ""
End Sub

How to Handle Control Arrays with Index Holes in Between in Visual Basic

Here's simple tutorial on how you can handle control arrays with index holes in between.

Control array is one of the best feature of Visual Basic. However, this can cause runtime errors if you are not careful, especially if they have missing elements or index in between.

To handle control arrays with sequence index, you can use this method.

Dim intX as integer
For intX = Text1.LBound to Text1.UBound
    MsgBox Text1(intX).Text
Next

However, if they have holes in between them (Ex: Text1(0), Text1(1), Text1(3), Text1(4)), the above method spits an error. To avoid that situation, treat the array like a collection as below.

Dim txt as TextBox
For Each txt In Text1
    MsgBox txt.Text
Next txt

That's it. Tamed control arrays. (^_^)

How to Easily Clear the Texts in Textbox or Combobox in Visual Basic

Ever done a program wherein you have to clear all the texts in textboxes and/comboboxes every time you need to input something? If you reference each control by it's name and then clearing the text one by one, that will result to more lines of codes. And in programming, more lines of codes means more complications.

Good thing is, you don't have to write several lines of source codes, the function below will do just that.

This visual basic function will clear texts any control with text property or a list-index property in the form.

Public Sub ClearAllControls(frmForm As Form)
    Dim ctlControl As Object
    On Error Resume Next
    For Each ctlControl In frmForm.Controls
        ctlControl.Text = ""
        ctlControl.LISTINDEX = -1
        DoEvents
    Next ctlControl
End Sub

Automatically Search ListBox Using API in Visual Basic

Using API, this codes automatically searches the listbox for whatever you type in the textbox.

To use this codes, add a ListBox, named List1 and a TextBox, named Text1 into your project and copy and paste the codes to your project.

Private Declare Function SendMessage Lib "user32" _
         Alias "SendMessageA" (ByVal hwnd As Long, _
         ByVal wMsg As Long, ByVal wParam As Integer, _
         ByVal lParam As Any) As Long

Const LB_FINDSTRING = &H18F

Private Sub Form_Load()
    With List1
        .AddItem "Computer"
        .AddItem "Screen"
        .AddItem "Modem"
        .AddItem "Printer"
        .AddItem "Scanner"
        .AddItem "Sound Blaster"
        .AddItem "Keyboard"
        .AddItem "CD-Rom"
        .AddItem "Mouse"
    End With
End Sub

Private Sub Text1_Change()
    Index = SendMessage(List1.hwnd, LB_FINDSTRING, -1, _
                        ByVal CStr(Text1.Text))
    If Index < 0 Then Exit Sub
    List1.ListIndex = Index
    Text1.Text = List1.List(Index)
End Sub

How to Make Context Menu (Pop up) Using API in Visual Basic

This is a very good example on how to make context menu or pop-up menu using API.

Context menu (pop up menu) is useful for your program, specially if it's heavy in graphic. A program with context menu far more user-friendly than one which has not.

In my case at work, are program usually deals with map (autoCAD maps), so context menu is very important. Fortunately, Visual Basic 6 has APIs to do just that.

Copy and paste the source codes below into your VB project and you're ready to go.

Private Enum BTN_STYLE
    MF_CHECKED = &H8&
    MF_APPEND = &H100&
    TPM_LEFTALIGN = &H0&
    MF_DISABLED = &H2&
    MF_GRAYED = &H1&
    MF_SEPARATOR = &H800&
    MF_STRING = &H0&
    TPM_RETURNCMD = &H100&
    TPM_RIGHTBUTTON = &H2&
End Enum

Private Type POINTAPI
    X As Long
    Y As Long
End Type

Private Declare Function CreatePopupMenu Lib "user32" () As Long
Private Declare Function TrackPopupMenuEx Lib "user32" (ByVal hMenu As Long, _
                ByVal wFlags As Long, ByVal X As Long, ByVal Y As Long, _
                ByVal HWnd As Long, ByVal lptpm As Any) As Long
Private Declare Function AppendMenu Lib "user32" Alias "AppendMenuA" _
                (ByVal hMenu As Long, _
                ByVal wFlags As BTN_STYLE, ByVal wIDNewItem As Long, _
                ByVal lpNewItem As Any) As Long
Private Declare Function DestroyMenu Lib "user32" (ByVal hMenu As Long) As Long
Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Dim hMenu As Long

Private Sub Form_MouseDown(Button As Integer, Shift As Integer, _
                           X As Single, Y As Single)
    Dim Pt As POINTAPI
    Dim ret As BTN_STYLE

    If Button = vbRightButton Then
        hMenu = CreatePopupMenu()
        AppendMenu hMenu, MF_STRING, 1, "New"
        AppendMenu hMenu, MF_STRING, 2, "Open"
        AppendMenu hMenu, MF_SEPARATOR, -1, "Add"
        AppendMenu hMenu, MF_STRING, 3, "Exit"
        GetCursorPos Pt
        ret = TrackPopupMenuEx(hMenu, TPM_LEFTALIGN Or TPM_RETURNCMD, _
                                Pt.X, Pt.Y, Me.HWnd, ByVal 0&)
        DestroyMenu hMenu

        If ret = 1 Then
            MsgBox "New"
        ElseIf ret = 2 Then
            MsgBox "Open"
        ElseIf ret = 3 Then
            MsgBox "Exit"
        End If
    End If
End Sub

How To Pair a Single Quote, Useful for SQL Queries and Statements

This function will ensure that a single qoute(') is paired. This is useful when executing SQL queries because an unpaired qoute(') will spit an SQL error in all database engine.

This source codes is written in Visual Basic 6.

Public Function BalanceQoute(sText As String) As String
    Dim sSavar As String, i As Integer
    sSavar = ""
    For i = 1 To Len(Trim(IfNull(sText)))
        If Mid(sText, i, 1) <> "'" Then
            sSavar = sSavar + Mid(sText, i, 1)
        Else
            sSavar = sSavar + "''"
        End If
    Next i
    ChkValue = sSavar
End Function

Visual Basic: How To Automatically Search Text in a Listbox

This codes searches the list for whatever you type in the textbox that matches the text in it.

To use this codes, add a ListBox, named List1, and a TextBox, named Text1 into your project and copy and paste the codes bleow to your project.

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
        (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, _
        ByVal lParam As Any) As Long
Const LB_FINDSTRING = &H18F

Private Sub Form_Load()
    With List1
        .AddItem "Computer"
        .AddItem "Screen"
        .AddItem "Modem"
        .AddItem "Printer"
        .AddItem "Scanner"
        .AddItem "Sound Blaster"
        .AddItem "Keyboard"
        .AddItem "CD-Rom"
        .AddItem "Mouse"
    End With
End Sub

Private Sub Text1_Change()
    Index = SendMessage(List1.hwnd, LB_FINDSTRING, -1, ByVal CStr(Text1.Text))
    If Index < 0 Then Exit Sub
    List1.ListIndex = Index
    Text1.Text = List1.List(Index)
End Sub

Visual Basic: How To Automatically Drop Down Combo Box

Here's a code snippet on how to automatically drow down the combo box control in Visual Basic 6 without using an API

'-->HOW TO AUTO DROPDOWN A COMBO WITHOUT USING API FUNCTION
Private Sub Combo1_GotFocus()
    SendKeys "%+{DOWN}"
End Sub

How To Export To OpenOffice Spreadsheet (Calc) in VB6

Exporting data to MS Excel is relatively easy. But if you are an advocate of Opensource software, you maybe using OpenOffice instead of Microsort Office.

This visual basic source codes show how to export the contents of the MSFlexGrid into OpenOffice Calc spreadsheet.

To test this source codes, open a Visual Basic 6 project add the ff. controls to a form: an MSFlexGrid, a CommandButton. Then, copy and paste the source codes to your project and you're good to go.

Public Sub ExportGridToCalc(grdTemp As MSFlexGrid, _
                strTitle As String, _
                Optional lngStartRow As Long = -1, _
                Optional lngEndRow As Long = -1, _
                Optional lngStartCol As Long = -1, _
                Optional lngEndCol As Long = -1)

  Dim oSM         As Object 'Object for accessing OpenOffice
  Dim oDesk       As Object 'Objects from the API
  Dim oDoc        As Object 'Objects from the API
  Dim oSheet      As Object 'Objects from the API
  Dim arg()                 'Ignore it for the moment !
  Dim lngRow      As Long
  Dim lngCol      As Long

  '-->SET FIRST THE ROWS AND COLS
  If lngStartRow = -1 Then lngStartRow = 1
  If lngEndRow = -1 Then lngEndRow = grdTemp.Rows - 1
  If lngStartCol = -1 Then lngStartCol = 0
  If lngEndCol = -1 Then lngEndCol = grdTemp.Cols - 1

  'Instanciate OOo : this line is mandatory with VB for OOo API
  Set oSM = CreateObject("com.sun.star.ServiceManager")
  'Create the first and most important service
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
  'Create a new doc
  Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", _
                       0, arg())
  'Get the first sheet in the doc
  Set oSheet = oDoc.getSheets().getByIndex(0)

  Call oSheet.getCellByPosition(0, 0).setString(strTitle)
  For lngRow = lngStartRow To lngEndRow
    For lngCol = lngStartCol To lngEndCol
      Call oSheet.getCellByPosition(lngCol, lngRow + _
      IIf(lngStartRow = 0, 2, 1)).setString(grdTemp.TextMatrix(lngRow, _
                          lngCol))
    Next
  Next
End Sub

Private Sub LoadDataToGrid()
  Dim lngRow        As Long
  Dim lngCol        As Long

  With MSFlexGrid1
    .Rows = 11
    .Cols = 5
    .FixedCols = 0

    For lngRow = 0 To 5 - 1
      .TextArray(lngRow) = "Header " & lngRow + 1
    Next

    For lngCol = 0 To 5 - 1
      For lngRow = 1 To 10
        .TextMatrix(lngRow, lngCol) = "R " & lngRow & " - C " & lngCol + 1
      Next
    Next
  End With
End Sub

Private Sub Command1_Click()
  Call ExportGridToOOCalc(MSFlexGrid1, "Exporting to OpenOffice")
End Sub

Private Sub Form_Load()
  Call LoadDataToGrid
End Sub

How To Make Custom Messages in VB6

If you are a Visual Basic (VB6) programmer who finds displaying messages using the MsgBox function so tedious. The functions below will make your life easier.

I wrote this functions to answer my demands in displaying messages to the users of my programs. This function is very handy. In fact, almost all of my Visual Basic projects uses this functions.

This functions re-uses the Visual Basic's MsgBox function to create custom message functions that are more flexible and easy to use.

'-->enum for the return of the confirmation from the user
Public Enum enum_prompt_return_value
  pr0mpt_yes = vbYes
  pr0mpt_no = vbNo
End Enum

'-->system name variable desclaration
'-->ex: gsystem_title = "payroll system"
Public gsystem_title As String

'-->this sub will display the critical error
'-->using the error object passed to it
'-->usage : call criticalerrormsg(err, ["error"])
Public Sub criticalerrormsg(err As ErrObject, _
              Optional strtitle As String = "critical error")
  MsgBox err.Number & ": " & err.Description, vbCritical, _
               gsystem_title & " - [" & strtitle & "]"
End Sub

'-->this sub will display the error using the
'-->error object passed to it
'-->usage : call errormsg(err, ["error"])
Public Sub errormsg(err As ErrObject, _
          Optional strtitle As String = "error")
  MsgBox err.Number & ": " & err.Description, vbExclamation, _
               gsystem_title & " - [" & strtitle & "]"
End Sub

'-->this sub will display an information message
'-->using the text passed to it
'-->usage : call infomsg(err, ["info"])
Public Sub infomsg(strmessage As String, _
           Optional strtitle As String = "info")
  MsgBox strmessage, vbInformation, _
          gsystem_title & " - [" & strtitle & "]"
End Sub

'-->this sub will display an information
'-->message using the text passed to it
'-->usage : call infomsg(err, ["info"])
Public Sub alertmsg(strmessage As String, _
          Optional strtitle As String = "alert")
  MsgBox strmessage, vbExclamation, _
             gsystem_title & " - [" & strtitle & "]"
End Sub

'-->this sub will ask a confirmation from
'-->the user using the text passed to it
'-->usage : retval = confirmmsg("delete record?", ["confirm"])
Public Function confirmmsg(strmessage As String, _
          Optional strtitle As String = "confirm") As _
          enum_prompt_return_value
  confirmmsg = MsgBox(strmessage, vbQuestion + vbYesNo, _
          gsystem_title & " - [" & strtitle & "]")
End Function

How To Limit The Cursor Movement in VB6

Limiting the cursor movement in a certain area (Ex: a form) can be restrictive to your users, but sometime certain application requires it. This codes show how to limit the cursor movement within a form or any control in Visual Basic 6.

To test it, just open a Visual Basic project with a form in it. Copy the sourcecodes and paste to the declaration section of the form. Then put two command buttons in the form.

You can limit the cursor movement within any control in Visual Basic. Just change the 'Me.hwnd' with the control's hwnd property. Ex: Command1.hWnd

'-->API Declaration
Private Declare Sub ClientToScreen Lib "user32" _
    (ByVal hwnd As Long, lpPoint As POINT)
Private Declare Sub ClipCursor Lib "user32" (lpRect As Any)
Private Declare Sub OffsetRect Lib "user32" _
        (lpRect As RECT, ByVal X As Long, ByVal Y As Long)
Private Declare Sub GetClientRect Lib "user32" _
        (ByVal hwnd As Long, lpRect As RECT)

Private Type RECT
    Left As Integer
    Top As Integer
    Right As Integer
    Bottom As Integer
End Type

Private Type POINT
    X As Long
    Y As Long
End Type

Private Sub Command1_Click()
    Dim Client As RECT
    Dim Up As POINT

    ClientToScreen Me.hwnd, Up
    GetClientRect Me.hwnd, Client
    OffsetRect Client, Up.X, Up.Y
    Up.X = Client.Left
    Up.Y = Client.Top
    ClipCursor Client
End Sub

Private Sub Command2_Click()
    ClipCursor ByVal 0&
End Sub

Private Sub Form_Load()
    Command1.Caption = "&Limit Cursor"
    Command2.Caption = "&Remove Cursor Limit"
End Sub

How To Handle Null Values in Database in VB6

Null value is just so frustrating. If you don't anticipate and handle it, it will create unpredictable results in your program. With null values unhandled, runtime error are very likely to occur.

If you are a business application programmer, you need to take null values seriously, especially when accessing and referencing data from a database. In my experience as a programmer, this is one of most causes of runtime error in programs written by newbie (even experienced) programmers.

This function help you in handling null values from a database. It will checks a value for null and returns a non-null value or a default value.

'Language: Visual Basic 6 (VB6)
'Parameter: varValue = the value that needs to be handle
'           varNullValue = the optional value to return in 
'                          case the varValue is Null
'Usage: MsgBox IfNull(rst!name, "kabalweg")


Public Function IfNull(varValue As Variant, Optional varNullValue As Variant = "") As Variant
  If IsNull(varValue) Or varValue = "" Then
    IfNull = varNullValue
  Else
    IfNull = varValue
  End If
End Function

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