Excel Automation issue.

[Satish Itty]

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.xlShiftDown)
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.

 

[Herfried K. Wagner [MVP]]

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.

PRB: Office Application Does Not Quit After Automation from Visual Studio
..NET Client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>
-> "Troubleshooting"

 

[Rad [Visual C# MVP]]

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.xlShiftDown)
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.

I think you forgot to quit the application

oExcelApp.Quit()

 

[ge0193387]

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.xlShiftDown)
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.

http://www.dotnet247.com/247reference/msgs/28/141381.aspx
Gives some insight on how to use Marshalling.

In 1.1 you have Marshal.ReleaseByRefComObject which returns an
integer, you simply loop until the integer returned = -1. In 2.0 you
have System.Runtime.InteropServices.Marshal.FinalReleaseComObject
which should dump it. You would do this instead of just making it =
to Nothing. Also calling GC.Collect afterwards will help ensure that
it closes.