Excel Automation issue.

Hi all,

I using excel automation to generate some reports in excel. I guess I'm
not doing it correctly because Every time the report is run it leaves a
Excel.exe process open in the system.process even after the user closes
the excel file generated.

Here is the code block I'm using

Dim oExcelApp As Excel.Application
Dim oExcel As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim rownum As Integer = 7
oExcelApp = CreateObject("Excel.Application")
oExcel = oExcelApp.Workbooks.Add(Application.StartupPath + \xxxx.xlt")
oSheet = oExcel.ActiveSheet
oExcelApp.Visible = False
oSheet.Range("A4").Value = String.Format("As of {0:G}", DateTime.Now())

For Each row As DataRow In ds.Tables(0).Rows
oSheet.Rows(rownum + 1).EntireRow.Select()
oExcelApp.Selection.Insert(Excel.XlInsertShiftDirection.xlSh iftDown)
oSheet.Range(String.Format("A{0}:V{0}", rownum)).Value = row.ItemArray
rownum += 1
Next
oExcelApp.Visible = True
oSheet = Nothing
oExcel = Nothing
oExcelApp = Nothing

Any ideas as to whats worng here?
Thanks,
Satish.

Re: Excel Automation issue.

On Tue, 16 Oct 2007 16:50:29 -0400, Satish Itty
wrote:

>Hi all,
>
>I using excel automation to generate some reports in excel. I guess I'm
>not doing it correctly because Every time the report is run it leaves a
>Excel.exe process open in the system.process even after the user closes
>the excel file generated.
>
>Here is the code block I'm using
>
>Dim oExcelApp As Excel.Application
>Dim oExcel As Excel.Workbook
>Dim oSheet As Excel.Worksheet
>Dim rownum As Integer = 7
>oExcelApp = CreateObject("Excel.Application")
>oExcel = oExcelApp.Workbooks.Add(Application.StartupPath + \xxxx.xlt")
>oSheet = oExcel.ActiveSheet
>oExcelApp.Visible = False
>oSheet.Range("A4").Value = String.Format("As of {0:G}", DateTime.Now())
>
>For Each row As DataRow In ds.Tables(0).Rows
> oSheet.Rows(rownum + 1).EntireRow.Select()
> oExcelApp.Selection.Insert(Excel.XlInsertShiftDirection.xlSh iftDown)
> oSheet.Range(String.Format("A{0}:V{0}", rownum)).Value = row.ItemArray
> rownum += 1
>Next
>oExcelApp.Visible = True
>oSheet = Nothing
>oExcel = Nothing
>oExcelApp = Nothing
>
>Any ideas as to whats worng here?
>Thanks,
>Satish.

Add the line oExcelApp.Quit() just before you set oSheet to nothing

--
http://bytes.thinkersroom.com

Re: Excel Automation issue.

in addition to the quit, i also do this for every excel object i had created

GCCom(XLworksheet, XLWorkbook, XLworkbooks, XLApplication)

where GCCom looks like this

Public Sub GCCom(ByVal ParamArray objects As Object())

Dim _references As Integer = 0

For Each _object As Object In objects
If Not _object Is Nothing Then
_references = Marshal.ReleaseComObject(_object)
While _references > 0
_references = Marshal.ReleaseComObject(_object)
End While
_object = Nothing
End If
Next

GC.Collect()

GC.WaitForPendingFinalizers()

End Sub



Rad [Visual C# MVP] wrote:
> On Tue, 16 Oct 2007 16:50:29 -0400, Satish Itty
> wrote:
>
>> Hi all,
>>
>> I using excel automation to generate some reports in excel. I guess I'm
>> not doing it correctly because Every time the report is run it leaves a
>> Excel.exe process open in the system.process even after the user closes
>> the excel file generated.
>>
>> Here is the code block I'm using
>>
>> Dim oExcelApp As Excel.Application
>> Dim oExcel As Excel.Workbook
>> Dim oSheet As Excel.Worksheet
>> Dim rownum As Integer = 7
>> oExcelApp = CreateObject("Excel.Application")
>> oExcel = oExcelApp.Workbooks.Add(Application.StartupPath + \xxxx.xlt")
>> oSheet = oExcel.ActiveSheet
>> oExcelApp.Visible = False
>> oSheet.Range("A4").Value = String.Format("As of {0:G}", DateTime.Now())
>>
>> For Each row As DataRow In ds.Tables(0).Rows
>> oSheet.Rows(rownum + 1).EntireRow.Select()
>> oExcelApp.Selection.Insert(Excel.XlInsertShiftDirection.xlSh iftDown)
>> oSheet.Range(String.Format("A{0}:V{0}", rownum)).Value = row.ItemArray
>> rownum += 1
>> Next
>> oExcelApp.Visible = True
>> oSheet = Nothing
>> oExcel = Nothing
>> oExcelApp = Nothing
>>
>> Any ideas as to whats worng here?
>> Thanks,
>> Satish.
>
> Add the line oExcelApp.Quit() just before you set oSheet to nothing
>
> --
> http://bytes.thinkersroom.com

Re: Excel Automation issue.

I cannot do oExcelApp.Quit() because that causes excel to close
immediately. I did a GC.Collect at the end and that seem to do the trick.
Thank you all.

Rad [Visual C# MVP] wrote:
> On Tue, 16 Oct 2007 16:50:29 -0400, Satish Itty
> wrote:
>
>> Hi all,
>>
>> I using excel automation to generate some reports in excel. I guess I'm
>> not doing it correctly because Every time the report is run it leaves a
>> Excel.exe process open in the system.process even after the user closes
>> the excel file generated.
>>
>> Here is the code block I'm using
>>
>> Dim oExcelApp As Excel.Application
>> Dim oExcel As Excel.Workbook
>> Dim oSheet As Excel.Worksheet
>> Dim rownum As Integer = 7
>> oExcelApp = CreateObject("Excel.Application")
>> oExcel = oExcelApp.Workbooks.Add(Application.StartupPath + \xxxx.xlt")
>> oSheet = oExcel.ActiveSheet
>> oExcelApp.Visible = False
>> oSheet.Range("A4").Value = String.Format("As of {0:G}", DateTime.Now())
>>
>> For Each row As DataRow In ds.Tables(0).Rows
>> oSheet.Rows(rownum + 1).EntireRow.Select()
>> oExcelApp.Selection.Insert(Excel.XlInsertShiftDirection.xlSh iftDown)
>> oSheet.Range(String.Format("A{0}:V{0}", rownum)).Value = row.ItemArray
>> rownum += 1
>> Next
>> oExcelApp.Visible = True
>> oSheet = Nothing
>> oExcel = Nothing
>> oExcelApp = Nothing
>>
>> Any ideas as to whats worng here?
>> Thanks,
>> Satish.
>
> Add the line oExcelApp.Quit() just before you set oSheet to nothing
>
> --
> http://bytes.thinkersroom.com

Re: Excel Automation issue.

On Thu, 18 Oct 2007 12:39:14 -0400, Satish Itty
wrote:

>I cannot do oExcelApp.Quit() because that causes excel to close
>immediately. I did a GC.Collect at the end and that seem to do the trick.
>Thank you all.

I'm not so sure that's a bad thing. I would imagine calling the Quit()
method instructs Excel to clean up after itself as it exits.

--
http://bytes.thinkersroom.com

Re: Excel Automation issue.

Yes but I need the report that was generated to stay open for the user.
When I do oExcepApp.Quit(). It closes the excel with the report that was
generated.

Rad [Visual C# MVP] wrote:
> On Thu, 18 Oct 2007 12:39:14 -0400, Satish Itty
> wrote:
>
>> I cannot do oExcelApp.Quit() because that causes excel to close
>> immediately. I did a GC.Collect at the end and that seem to do the trick.
>> Thank you all.
>
> I'm not so sure that's a bad thing. I would imagine calling the Quit()
> method instructs Excel to clean up after itself as it exits.
>
> --
> http://bytes.thinkersroom.com