Working with VB and Excel (Visual Basic 6 and Excel)

The purpose of this script is to get data from MS-Excel into list view in Microsoft Visual Basic 6.0 and to export data from list view of Miscrofot Visual Basic 6.0 into MS-Excel.
– Create file Excel 2003 named Book3.xls and save to C:\
– Enter some data to the left 4 columns of the top 3 or 4 rows as the example
– In VB6 Create Class named clsExcel
– Create one Form and add ListView control to form with 4 columns and view property as lvwReport
– Add one button to form under the listview control and captioned as Export Data

– Create Class named clsExcel

Option Explicit

Private ExcelApp As Object
Private ExcelBook As Object
Private ExcelSheet As Object

Public Sub NewFile()
    Set ExcelBook = ExcelApp.WorkBooks.Add
    ExcelApp.Visible = True
End Sub

Public Sub OpenFile(ByVal pathName As String)
    Set ExcelBook = ExcelApp.WorkBooks.Open(pathName)
End Sub

Private Sub Class_Initialize()
    Set ExcelApp = CreateObject("Excel.Application")
End Sub

Public Sub SetSheet(ByVal sheetNumber As Integer)
    Set ExcelSheet = ExcelBook.WorkSheets(sheetNumber)
End Sub

Public Property Let Cells(ByVal row As Integer, ByVal col As Integer, ByVal value As String)
    ExcelSheet.Cells(row, col) = value
End Property

Public Property Get Cells(ByVal row As Integer, ByVal col As Integer) As String
    Cells = ExcelSheet.Cells(row, col)
End Property

Public Sub CloseFile()
End Sub

Private Sub Class_Terminate()
    Set ExcelSheet = Nothing
    Set ExcelBook = Nothing
    Set ExcelApp = Nothing
End Sub

– Using the above class in Form
– Get data from Excel

Private Sub Form_Load()
    Dim objExcel As New clsExcel
    Dim i As Integer
    Dim l As ListItem
    objExcel.OpenFile "C:\Book3.xls"
    objExcel.SetSheet 1
    For i = 1 To 30
        Set l = lvwList.ListItems.Add(, , objExcel.Cells(i, 1))
        l.SubItems(1) = objExcel.Cells(i, 2)
        l.SubItems(2) = objExcel.Cells(i, 3)
        l.SubItems(3) = objExcel.Cells(i, 4)

    Set objExcel = Nothing
End Sub

– Export data to Excel

Private Sub Command1_Click()
    Dim objExcel As New clsExcel
    Dim i As Integer
    objExcel.SetSheet 1
    For i = 1 To lvwList.ListItems.Count
        objExcel.Cells(i, 1) = lvwList.ListItems(i).Text
        objExcel.Cells(i, 2) = lvwList.ListItems(i).SubItems(1)
        objExcel.Cells(i, 3) = lvwList.ListItems(i).SubItems(2)
        objExcel.Cells(i, 4) = lvwList.ListItems(i).SubItems(3)

    Set objExcel = Nothing
End Sub


One Response to “Working with VB and Excel (Visual Basic 6 and Excel)”

  1. vongrith Says:

    good news

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: