how to export the listview items to excel sheet with listview header.

Question

0.00/5 (No votes)

See more:

I am trying to export the data of listview in vb 6.0 to excel sheet with listview header.

My code is:-

VB

Private Sub cmdExport_Click()

'genaral
Dim objExcel As New Excel.Application

Dim objExcelSheet As Excel.Worksheet
'-----------------------------------

'check whether data is thre
If LstLog.ListItems.count > 0 Then
    objExcel.Workbooks.Add
    Set objExcelSheet = objExcel.Worksheets.Add


    For Col = 1 To LstLog.ColumnHeaders.count
        objExcelSheet.Cells(1, Col).Value = LstLog.ColumnHeaders(Col)
    Next

    For Row = 2 To LstLog.ListItems.count
        For Col = 1 To LstLog.ColumnHeaders.count
        If Col = 1 Then
                objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).Text
        Else
                objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).SubItems(Col - 1)
        End If
        Next
    Next

    objExcelSheet.Columns.AutoFit
    CommonDialog1.ShowOpen
    A = CommonDialog1.FileName

    objExcelSheet.SaveAs A & ".xls"
    MsgBox "Export Completed", vbInformation, Me.Caption

    objExcel.Workbooks.Open A & ".xls"
    objExcel.Visible = True
    'objExcel.Quit
Else
    MsgBox "No data to export", vbInformation, Me.Caption
End If

End Sub

But when I export the data to excel sheet with this code the first row of list view item is replace by list view header..

Please help.

Posted 11-Apr-13 20:08pm

Member 8737191

Updated 27-Aug-18 3:45am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Maciej Los 12-Apr-13 2:37am

Code look OK, try to debug program and check why this happens...

Aydin Homay 15-Apr-13 0:23am

Please trace your for block:

For Row = 2 To LstLog.ListItems.count
For Col = 1 To LstLog.ColumnHeaders.count
If Col = 1 Then
objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).Text
Else
objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).SubItems(Col - 1)
End If
Next
Next
I think you have a mistake at initializing second for.

Best Regards.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CoderzF1 · Answer 1 · 2013-09-07T04:59:00

Replace

For Row = 2 To LstLog.ListItems.count
    For Col = 1 To LstLog.ColumnHeaders.count
    If Col = 1 Then
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).Text
    Else
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row).SubItems(Col - 1)
    End If
    Next
Next

with

For Row = 2 To LstLog.ListItems.count
    For Col = 1 To LstLog.ColumnHeaders.count
    If Col = 1 Then
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row-1).Text
    Else
            objExcelSheet.Cells(Row, Col).Value = LstLog.ListItems(Row-1).SubItems(Col - 1)
    End If
    Next
Next

Headers weren't overriding the first data entry. The code was just skipping over the first data entry in the listview.

if last item does not show up in the exported data then change

For Row = 2 To LstLog.ListItems.count

to

For Row = 2 To LstLog.ListItems.count + 1

Member 10655931 · Answer 2 · 2018-08-27T03:46:00

Public Sub export_me_to_excel(ByVal list As ListView)
    Try
        Dim objExcel As New Excel.Application
        Dim bkWorkBook As Workbook
        Dim shWorkSheet As Worksheet
        Dim chartRange As Excel.Range


        Dim i As Integer
        Dim j As Integer

        objExcel = New Excel.Application
        bkWorkBook = objExcel.Workbooks.Add
        shWorkSheet = CType(bkWorkBook.ActiveSheet, Worksheet)
        shWorkSheet.DisplayRightToLeft = True

        chartRange = shWorkSheet.Range("a1", "e2")
        chartRange.Merge()
        chartRange.FormulaR1C1 = xlval

        chartRange.HorizontalAlignment = 2
        chartRange.VerticalAlignment = 2

        For i = 0 To list.Columns.Count - 1
            shWorkSheet.Cells(5, i + 1) = list.Columns(i).Text
            shWorkSheet.Columns.AutoFit()
            shWorkSheet.Columns.HorizontalAlignment = Excel.Constants.xlCenter

            'shWorkSheet.Range(newcell).BorderAround2(Excel.XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)
        Next
        For i = 0 To list.Items.Count - 1
            For j = 0 To list.Items(i).SubItems.Count - 1
                shWorkSheet.Cells(i + 6, j + 1) = list.Items(i).SubItems(j).Text
                shWorkSheet.Columns.AutoFit()
                shWorkSheet.Columns.HorizontalAlignment = Excel.Constants.xlCenter
                'shWorkSheet.Columns.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)

            Next
        Next

        objExcel.Visible = True
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

how to export the listview items to excel sheet with listview header.

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0