How to Get Data from Excel Worksheet Using Visual Basic 6

You can read and retrieve data from Microsoft Excel Worksheet easily by using your application that you made by Visual Basic 6 (VB6) programming. Simply just using its Microsoft Excel Object Library reference provided by VB6. The following code will show you how we can do that step by step.

Started from how we can prepare the Excel object, get or create the Excel object, opening the Excel file, opening the Excel worksheet, accessing (reading and displaying) the data from Excel file, closing the Excel worksheet, closing the Excel file, until cleaning-up the memory used by the Excel object. This is very very important for you, since almost our data stored in Microsoft Excel file, and not always in the database.

'Description: Read and get data from an Excel worksheet and 
'             display it from the first row until the fifth row
'             by using a MsgBox in Visual Basic 6.
'             Including the technique how you should access Excel 
'             step by step properly, from opening the Excel file until
'             close and clear the memory that used by Excel object.
'Author     : Masino Sinaga
'Date       : Sunday, August 2, 2009
'Preparation: 1. Create a new Standard exe project with 1 form
'             2. Add 1 CommandButton control on the Form1
'             3. Add an item "Microsoft Excel X.X Object Library"
'                from menu Project -> References... . X.X depends on
'                version Excel or Microsoft Office you are using.
'                When I tried this code, it had been installed 
'                Microsoft Office 2000 and Microsoft Office 2007,
'                thus, X.X above is equal with 12.0 which VB6
'                displaying only the last version object library Excel. 
'                In this case, Microsoft Excel 12.0 Object Library 
'             4. Ensure your Dataku.xls file already exists in the same
'                directory where your application located, and in
'                column B started from the first row until the fifth row
'                you already have value in it.
'             5. Copy the following code to the Form1 editor.

Dim Excel As Excel.Application 'Excel Application
Dim ExcelWBk As Excel.Workbook 'Excel Workbook
Dim ExcelWS As Excel.Worksheet 'Excel Worksheet

Private Sub Command1_Click()
  On Error GoTo Err
  'Inisialitation Excel object
  'Open Dataku.xls file that located in the same directory
  'where your application located.
  Set ExcelWBk = Excel.Workbooks.Open(App.Path & "\Dataku.xls")
  'Displaying its status on the form
  Print "Successfully open file ..."
  'Access the first Worksheet (1)
  'If you want to switch to the second Worksheet, then
  'simply replace (1) with (2), and so forth...
  Set ExcelWS = ExcelWBk.Worksheets(1)
  'Displaying its status on the form
  Print "Successfully read Worksheet Sheet1 ..."
  'Processing the ExcelWS variable
  With ExcelWS
    Dim i As Integer
    Dim strData As String
    'Read from the first row until the fifth row
    For i = 1 To 5
      'Assign to a variabel
      strData = strData & .Cells(i, 2) & vbCrLf
    Next i
  End With
  'Displaying the data to MsgBox
  MsgBox strData
  'After finished, don't forget to close the worksheet
  'Displaying its status on the form
  Print "Successfully close worksheet and Excel file ..."
  'Don' forget neither, always clean-up the memory that
  'has just been used by Excel object
  'Displaying its status on the form
  Print "Successfully clean-up the memory used by Excel ..."
  'Displaying the message
  MsgBox "Finish, that's all folks ...!", vbInformation, "Good"
  Exit Sub
  'When error occured, don't forget to clean-up the memory
  MsgBox Err.Description, vbCritical, "Error Occured"
End Sub

Private Sub StartExcel()
  On Error GoTo Err:
  'First of all, get the Excel object, and if error occured
  'jumpt to the Err label on the bottom of this Sub,
  'then create the Excel object. Typically, error occured here
  'if the Excel object has not been created, yet
  Set Excel = GetObject(, "Excel.Application")
  Exit Sub
  'Create an Excel object if it does not exist.
  Set Excel = CreateObject("Excel.Application")
End Sub

Private Sub CloseWorkSheet()
  On Error Resume Next
  'Close the Excel workbook
  'Quit from Excel application
End Sub

Private Sub ClearExcelMemory()
  'Clean-up the memory, check first, whether
  'the Excel object exists or not in the memory ...
  If Not ExcelWS Is Nothing Then Set ExcelWS = Nothing
  If Not ExcelWBk Is Nothing Then Set ExcelWBk = Nothing
  If Not Excel Is Nothing Then Set Excel = Nothing
End Sub

Do not forget to always cleaning-up the memory used by your Excel object. If you do not handle this, then you can not open your Excel file, until you have to first clear the memory used by your Excel object by pressing End Task button in the Task Manager dialogue box.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>