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()
    ExcelApp.WorkBooks.Close
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
   
    lvwList.ListItems.Clear
   
    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)
    Next

    objExcel.CloseFile
    Set objExcel = Nothing
End Sub

– Export data to Excel

Private Sub Command1_Click()
    Dim objExcel As New clsExcel
    Dim i As Integer
   
    objExcel.NewFile
    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)
    Next

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: