cheapkid1 cheapkid1 - 2 months ago 35
Vb.net Question

VB export listview to Excel

I keep getting 'Exception from HRESULT: 0x800A03EC' error when I run my code.
Some forums say that the '1' needs to be changed to a '0' or vice versa.
At this point I just don't know what is wrong nothing is sticking out. I believe I have all the references loaded that I need, so any suggestions/help would be much appreciated.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim objExcel As New Excel.Application
Dim bkWorkBook As Workbook
Dim shWorkSheet As Worksheet
Dim i As Integer
Dim j As Integer

objExcel = New Excel.Application
bkWorkBook = objExcel.Workbooks.Add
shWorkSheet = bkWorkBook.ActiveSheet
For i = 0 To Me.ListView1.Columns.Count
shWorkSheet.Cells(1, Chr(64 + i)) = Me.ListView1.Columns(i)
Next
For i = 1 To Me.ListView1.Items.Count
shWorkSheet.Cells(i + 2, "A") = Me.ListView1.Items(i).Text
For j = 2 To Me.ListView1.Items.Count
shWorkSheet.Cells(i + 2, Chr(64 + j)) = Me.ListView1.Items(i).SubItems(j - 1)
Next
Next

objExcel.Visible = True
End Sub


Thanks,

Answer

I retested and found I hadn't fully tested a listview more more than a couple of columns. Try this:

    Try
        Dim objExcel As New Excel.Application
        Dim bkWorkBook As Workbook
        Dim shWorkSheet As Worksheet
        Dim i As Integer
        Dim j As Integer

        objExcel = New Excel.Application
        bkWorkBook = objExcel.Workbooks.Add
        shWorkSheet = CType(bkWorkBook.ActiveSheet, Worksheet)
        For i = 0 To Me.ListView1.Columns.Count - 1
            shWorkSheet.Cells(1, i + 1) = Me.ListView1.Columns(i).Text
        Next
        For i = 0 To Me.ListView1.Items.Count - 1
            For j = 0 To Me.ListView1.Items(i).SubItems.Count - 1
                shWorkSheet.Cells(i + 2, j + 1) = Me.ListView1.Items(i).SubItems(j).Text
            Next
        Next

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