export Listview to Excel

[GoodJuJu]

Hi there,

I have a problem that I hope somebody can help with.

I have a button on my form which creates an instance of Excel and exports a ListView to an Excel Workbook - This all works perfectly.

Occasionally when I exit my application I get the following error 'The instruction at "0x0adbc609c" referenced memory at "0x00000014". The memory could not be "read".' (This may happen whether the Excel instance my application opened is open or has been closed).

I think that this error may be memory related. Is it possible that my code to export to excel is still 'hanging onto' the exported data?

My questions are:

• 
  Is there a way for me to clear the memory that has been used during the export to excel - do I even need to do this?

• 
  Is there a link that remains between my application and Excel after the workbook has been created? Can I break this link (if one exists) without killing Excel?

• 
  When I close my application, can I kill only the Excel instances that were started inside my app and leave externally opened Excel sessions open?

• 
  I see a lot of talk about the 'Garbage Collector' mentioned. I also see a lot of people mentioning to be extremely careful when using it. Is this somehting I need to think about using when exporting data to Excel?

Any help anyone can offer is much appreciated...

 

[ggunter]

Did you try releasing your Com object with "Marshal.ReleaseComOject"?

 

[GoodJuJu]

Hi ggunter ,

No, I hadn't released the COM object, thanks for pointing me in the right direction. I have now added this to my code.

I still get the error, but I have noticed it does not matter whether Excel has been open or not, so I shall keep digging as I'm guessing it is related to something different to what I posted.

Thanks very much for your help. I'm sure it will help prevent errors further down the line and is a good idea to implement.

 

[KnobblesSteed]

Export to Excel from Listview

Hi GoodJuJu,

I'm a very .NET beginner and i'm trying to get this done.. you were saying "exports a ListView to an Excel Workbook - This all works perfectly."

Can you please help me on this and share your code ?

Thx a lot..

KSteed

 

[GoodJuJu]

Hi KSteed,

You need to add reference to Microsoft Excel at the top of your code. For example:.....

Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports System.Text.RegularExpressions

I originally downloaded the code myself from this website....

how to export listview data into excel sheet

I checked the link this morning (19th Oct. 2010) and it is working.

I call the code from a button on my form. My button code is:

    Private Sub btnExportEvents_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles btnExportEvents.Click

        On Error GoTo Err_Handler

        Dim intEventEntriesCount As Integer
        intEventEntriesCount = lstViewMain.Items.Count

        If intEventEntriesCount <= 0 Then
            Exit Sub
        End If
        Dim intResponse As Integer
        intResponse = MsgBox("There are " & CStr(intEventEntriesCount) & _
                            " entries in the events log" & vbLf & "" & _
                            "Are you sure you wish to proceed?" & vbLf & "" & _
                            "Depending on the amount of entries, this may take" & vbLf & "" & _
                            " several minutes to complete.  Please be patient", vbOKCancel, "Export events")
        If intResponse = 1 Then
            Call ExportListViewToExcel(lstViewMain)
        Else
            Exit Sub
        End If

        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub

My ListView I am exporting is called 'lstViewMain' and contains 3 columns.

Good luck.....

 

[KnobblesSteed]

I'm getting several errors.. and 1 warning...
Inside the code of the function..
it doesnt accept that line
Dim ExcelReport As Excel.ApplicationClass
and propose that
Dim ExcelReport As Microsoft.Office.Interop.Excel.Application
also this
ExcelReport = New Excel.ApplicationClass
to
ExcelReport = New Microsoft.Office.Interop.Excel.Application
and the warning it says..
Function 'ExportListViewToExcel' doesn't return a value on all code paths. A null reference exception could occur at run time when the result is used.
After i hit the button.. Your msgbox appear perfectly and then MS Excel get opens and nothing has been transfered.. and the application got an error message "No valid index. (HRESULT Exception: 0x8002000B (DISP_E_BADINDEX))"

I'll appreciate your help

 

[KnobblesSteed]

Found another way

ok.. i replaced the listview to datagridview and found some codes to export to Excel and works perfectly..

Thx for your help

KSteed