Export Data from VB 6 to Microsoft Excel

– Create one form
– Place listView to form with lvwReport view property and add 5 columns ID, name, gender, phone and address
– Place two buttons btnExport and btnClose to form

On Form Load

Private Sub Form_Load()
    Me.lvwData.View = lvwReport
    Dim i As Integer
    For i = 1 To 10
        With Me.lvwData.ListItems.Add(, , i)
            .SubItems(1) = "Name " & i
            .SubItems(2) = "M"
            .SubItems(3) = "012"
            .SubItems(4) = "Phnom Penh"
        End With
End Sub

On btnExport Click

Private Sub cmdExport_Click()
    Dim ExcelApp As Object, ExcelBook As Object
    Dim ExcelSheet As Object
    Dim i As Integer
    Dim j As Integer
    Dim rowCount As Integer
    'create object of excel
    Set ExcelApp = CreateObject("Excel.Application")
    Set ExcelBook = ExcelApp.WorkBooks.Add
    Set ExcelSheet = ExcelBook.WorkSheets(1)
    With ExcelSheet
        For i = 1 To Me.lvwData.ListItems.Count
            rowCount = rowCount + 1
            .cells(rowCount, 1) = lvwData.ListItems(i).Text
            For j = 1 To lvwData.ColumnHeaders.Count - 1
                .cells(rowCount, j + 1) = lvwData.ListItems(i).SubItems(j)
    End With
    ExcelApp.Visible = True
    'set page margin
    ExcelSheet.PageSetup.PaperSize = 9
    ExcelSheet.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.3)
    ExcelSheet.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.3)
    ExcelSheet.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.3)
    ExcelSheet.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.3)
    ExcelSheet.PageSetup.CenterHorizontally = True
    Set ExcelSheet = Nothing
    Set ExcelBook = Nothing
    Set ExcelApp = Nothing
End Sub

Download Source Code

(Coded by: Iech Setha)

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: