Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL.EXE process does not end after adding an excel chart
Hi,
I have VB .NET function below that creates an excel document. It works fine and the Excel.exe process ends except when I try to add a chart. When I include the code here I cannot cleanly end the Excel.exe process: xlChart.ChartType = Excel.XlChartType.xlXYScatterLines Once I include that code the EXCEL.EXE process is still in the task manager. What can I do to close the EXCEL.EXE process? Thanks, Marc Code: Imports Excel = Microsoft.Office.Interop.Excel Function CreateExcelPlot() As String Dim plotFilePath As String = Nothing Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlDataSheet As Excel.Worksheet Dim xlChartSheet As Excel.Worksheet Dim xlChart As Excel.Chart 'Initiate Excel, hide the application, and add a workbook. In case you were wondering, by referring to the Excel 'application, the program is initiated xlApp = New Excel.Application xlApp.Visible = False xlBook = xlApp.Workbooks.Add() xlDataSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChartSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChart = xlApp.Charts.Add xlChart.ChartType = Excel.XlChartType.xlXYScatterLines 'Once I include this, I can't cleanly close EXCEL.EXE xlDataSheet.Cells(1, 1) = DateValue("January 1, 2003") xlDataSheet.Cells(1, 2) = 30 xlDataSheet.Cells(2, 1) = DateValue("February 1, 2003") xlDataSheet.Cells(2, 2) = 35 xlDataSheet.Cells(3, 1) = DateValue("March 1, 2003") xlDataSheet.Cells(3, 2) = 31 xlDataSheet.Cells(4, 1) = DateValue("April 1, 2003") xlDataSheet.Cells(4, 2) = 36 'xlChart.SetSourceData(xlDataSheet.Range("A1:B4")) Ignore for now plotFilePath = "C:\Test13.xls" Try My.Computer.FileSystem.DeleteFile(plotFilePath) Catch End Try xlBook.SaveAs(plotFilePath) xlBook.Close() xlApp.Workbooks.Close() xlApp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlApp) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlDataSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChartSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChart) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlBook) xlApp = Nothing xlBook = Nothing xlDataSheet = Nothing xlChartSheet = Nothing xlChart = Nothing GC.Collect() return plotFilePath End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL.EXE process does not end after adding an excel chart
Marc,
I don't speak VB.NET but I have some comments/suggestions. (you get what you pay for) <g Your complaint is most ofter heard (speaking VBA) when orphan references are created and they are not Set to nothing before exiting.... 1. Use xlApp instead of Excel... xlChart.ChartType = xlApp.XlChartType.xlXYScatterLines 2. Clean up your child objects first and proceed up the line... xlChart = Nothing xlChartSheet = Nothing xlDataSheet = Nothing xlBook.Close() xlBook = Nothing xlApp.Workbooks.Close() xlApp.Quit() xlApp = Nothing 3. Don't quit the app until you have cleaned things up -- Jim Cone Portland, Oregon USA "marcnikko" wrote in message Hi, I have VB .NET function below that creates an excel document. It works fine and the Excel.exe process ends except when I try to add a chart. When I include the code here I cannot cleanly end the Excel.exe process: xlChart.ChartType = Excel.XlChartType.xlXYScatterLines Once I include that code the EXCEL.EXE process is still in the task manager. What can I do to close the EXCEL.EXE process? Thanks, Marc Code: Imports Excel = Microsoft.Office.Interop.Excel Function CreateExcelPlot() As String Dim plotFilePath As String = Nothing Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlDataSheet As Excel.Worksheet Dim xlChartSheet As Excel.Worksheet Dim xlChart As Excel.Chart 'Initiate Excel, hide the application, and add a workbook. In case you were wondering, by referring to the Excel 'application, the program is initiated xlApp = New Excel.Application xlApp.Visible = False xlBook = xlApp.Workbooks.Add() xlDataSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChartSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChart = xlApp.Charts.Add 'Once I include this, I can't cleanly close EXCEL.EXE xlChart.ChartType = Excel.XlChartType.xlXYScatterLines xlDataSheet.Cells(1, 1) = DateValue("January 1, 2003") xlDataSheet.Cells(1, 2) = 30 xlDataSheet.Cells(2, 1) = DateValue("February 1, 2003") xlDataSheet.Cells(2, 2) = 35 xlDataSheet.Cells(3, 1) = DateValue("March 1, 2003") xlDataSheet.Cells(3, 2) = 31 xlDataSheet.Cells(4, 1) = DateValue("April 1, 2003") xlDataSheet.Cells(4, 2) = 36 'xlChart.SetSourceData(xlDataSheet.Range("A1:B4")) Ignore for now plotFilePath = "C:\Test13.xls" Try My.Computer.FileSystem.DeleteFile(plotFilePath) Catch End Try xlBook.SaveAs(plotFilePath) xlBook.Close() xlApp.Workbooks.Close() xlApp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlApp) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlDataSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChartSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChart) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlBook) xlApp = Nothing xlBook = Nothing xlDataSheet = Nothing xlChartSheet = Nothing xlChart = Nothing GC.Collect() return plotFilePath End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL.EXE process does not end after adding an excel chart
You forgot to include "Set" to set your objects to Nothing. ;)
"Jim Cone" wrote: Marc, I don't speak VB.NET but I have some comments/suggestions. (you get what you pay for) <g Your complaint is most ofter heard (speaking VBA) when orphan references are created and they are not Set to nothing before exiting.... 1. Use xlApp instead of Excel... xlChart.ChartType = xlApp.XlChartType.xlXYScatterLines 2. Clean up your child objects first and proceed up the line... xlChart = Nothing xlChartSheet = Nothing xlDataSheet = Nothing xlBook.Close() xlBook = Nothing xlApp.Workbooks.Close() xlApp.Quit() xlApp = Nothing 3. Don't quit the app until you have cleaned things up -- Jim Cone Portland, Oregon USA "marcnikko" wrote in message Hi, I have VB .NET function below that creates an excel document. It works fine and the Excel.exe process ends except when I try to add a chart. When I include the code here I cannot cleanly end the Excel.exe process: xlChart.ChartType = Excel.XlChartType.xlXYScatterLines Once I include that code the EXCEL.EXE process is still in the task manager. What can I do to close the EXCEL.EXE process? Thanks, Marc Code: Imports Excel = Microsoft.Office.Interop.Excel Function CreateExcelPlot() As String Dim plotFilePath As String = Nothing Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlDataSheet As Excel.Worksheet Dim xlChartSheet As Excel.Worksheet Dim xlChart As Excel.Chart 'Initiate Excel, hide the application, and add a workbook. In case you were wondering, by referring to the Excel 'application, the program is initiated xlApp = New Excel.Application xlApp.Visible = False xlBook = xlApp.Workbooks.Add() xlDataSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChartSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChart = xlApp.Charts.Add 'Once I include this, I can't cleanly close EXCEL.EXE xlChart.ChartType = Excel.XlChartType.xlXYScatterLines xlDataSheet.Cells(1, 1) = DateValue("January 1, 2003") xlDataSheet.Cells(1, 2) = 30 xlDataSheet.Cells(2, 1) = DateValue("February 1, 2003") xlDataSheet.Cells(2, 2) = 35 xlDataSheet.Cells(3, 1) = DateValue("March 1, 2003") xlDataSheet.Cells(3, 2) = 31 xlDataSheet.Cells(4, 1) = DateValue("April 1, 2003") xlDataSheet.Cells(4, 2) = 36 'xlChart.SetSourceData(xlDataSheet.Range("A1:B4")) Ignore for now plotFilePath = "C:\Test13.xls" Try My.Computer.FileSystem.DeleteFile(plotFilePath) Catch End Try xlBook.SaveAs(plotFilePath) xlBook.Close() xlApp.Workbooks.Close() xlApp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlApp) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlDataSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChartSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChart) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlBook) xlApp = Nothing xlBook = Nothing xlDataSheet = Nothing xlChartSheet = Nothing xlChart = Nothing GC.Collect() return plotFilePath End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL.EXE process does not end after adding an excel chart
You forgot to include "Set" to set your objects to Nothing. ;) In VB.NET, there is no "Set" keyword since everything is basically an object already. VS2005 and earlier would automatically remove the Set keyword if you used it in an assignment, but VS2008+ doesn't do this. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 15 May 2009 17:23:01 -0700, Barb Reinhardt wrote: You forgot to include "Set" to set your objects to Nothing. ;) "Jim Cone" wrote: Marc, I don't speak VB.NET but I have some comments/suggestions. (you get what you pay for) <g Your complaint is most ofter heard (speaking VBA) when orphan references are created and they are not Set to nothing before exiting.... 1. Use xlApp instead of Excel... xlChart.ChartType = xlApp.XlChartType.xlXYScatterLines 2. Clean up your child objects first and proceed up the line... xlChart = Nothing xlChartSheet = Nothing xlDataSheet = Nothing xlBook.Close() xlBook = Nothing xlApp.Workbooks.Close() xlApp.Quit() xlApp = Nothing 3. Don't quit the app until you have cleaned things up -- Jim Cone Portland, Oregon USA "marcnikko" wrote in message Hi, I have VB .NET function below that creates an excel document. It works fine and the Excel.exe process ends except when I try to add a chart. When I include the code here I cannot cleanly end the Excel.exe process: xlChart.ChartType = Excel.XlChartType.xlXYScatterLines Once I include that code the EXCEL.EXE process is still in the task manager. What can I do to close the EXCEL.EXE process? Thanks, Marc Code: Imports Excel = Microsoft.Office.Interop.Excel Function CreateExcelPlot() As String Dim plotFilePath As String = Nothing Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlDataSheet As Excel.Worksheet Dim xlChartSheet As Excel.Worksheet Dim xlChart As Excel.Chart 'Initiate Excel, hide the application, and add a workbook. In case you were wondering, by referring to the Excel 'application, the program is initiated xlApp = New Excel.Application xlApp.Visible = False xlBook = xlApp.Workbooks.Add() xlDataSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChartSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChart = xlApp.Charts.Add 'Once I include this, I can't cleanly close EXCEL.EXE xlChart.ChartType = Excel.XlChartType.xlXYScatterLines xlDataSheet.Cells(1, 1) = DateValue("January 1, 2003") xlDataSheet.Cells(1, 2) = 30 xlDataSheet.Cells(2, 1) = DateValue("February 1, 2003") xlDataSheet.Cells(2, 2) = 35 xlDataSheet.Cells(3, 1) = DateValue("March 1, 2003") xlDataSheet.Cells(3, 2) = 31 xlDataSheet.Cells(4, 1) = DateValue("April 1, 2003") xlDataSheet.Cells(4, 2) = 36 'xlChart.SetSourceData(xlDataSheet.Range("A1:B4")) Ignore for now plotFilePath = "C:\Test13.xls" Try My.Computer.FileSystem.DeleteFile(plotFilePath) Catch End Try xlBook.SaveAs(plotFilePath) xlBook.Close() xlApp.Workbooks.Close() xlApp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlApp) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlDataSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChartSheet) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChart) System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlBook) xlApp = Nothing xlBook = Nothing xlDataSheet = Nothing xlChartSheet = Nothing xlChart = Nothing GC.Collect() return plotFilePath End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL.EXE process does not end after adding an excel chart
I solved this issue using your guys suggestions. For others
encountering similar issues here are some guidelines I learned the hard way. 1) Declare every object 2) Cleanup your objects in the correct order Here's the code that works: Function CreateExcelPlot() As String Dim plotFilePath As String = Nothing Dim xlApp As Excel.Application Dim xlWorkBooks As Excel.Workbooks Dim xlBook As Excel.Workbook Dim xlDataSheet As Excel.Worksheet Dim xlChart As Excel.Chart Dim xlChartRange As Excel.Range 'Initiate Excel, hide the application, and add a workbook. In case you were wondering, by referring to the Excel 'application, the program is initiated xlApp = New Excel.Application xlApp.Visible = False xlWorkBooks = xlApp.Workbooks xlBook = xlWorkBooks.Add() xlDataSheet = CType(xlBook.Worksheets.Add(), Excel.Worksheet) xlChart = xlApp.Charts.Add() xlChart.ChartType = Excel.XlChartType.xlXYScatterLines xlDataSheet.Cells(1, 1) = DateValue("January 1, 2003") xlDataSheet.Cells(1, 2) = 30 xlDataSheet.Cells(2, 1) = DateValue("February 1, 2003") xlDataSheet.Cells(2, 2) = 35 xlDataSheet.Cells(3, 1) = DateValue("March 1, 2003") xlDataSheet.Cells(3, 2) = 31 xlDataSheet.Cells(4, 1) = DateValue("April 1, 2003") xlDataSheet.Cells(4, 2) = 36 xlChartRange = xlDataSheet.Range("A1:B4") xlChart.SetSourceData(xlChartRange) plotFilePath = "C:\Test13.xls" Try My.Computer.FileSystem.DeleteFile(plotFilePath) Catch End Try xlBook.SaveAs(plotFilePath) 'Cleanup so Excel closes System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChartRange) xlChartRange = Nothing System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlChart) xlChart = Nothing System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlDataSheet) xlDataSheet = Nothing xlBook.Close() System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlBook) xlBook = Nothing xlWorkBooks.Close() System.Runtime.InteropServices.Marshal.ReleaseComO bject (xlWorkBooks) xlWorkBooks = Nothing xlApp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlApp) xlApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() Return plotFilePath End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding a single label on excel chart | Charts and Charting in Excel | |||
Adding a Benchmark line in an Excel chart | Charts and Charting in Excel | |||
Adding a control line to excel chart without showing up in the leg | Charts and Charting in Excel | |||
Adding custom graphics to excel chart | Excel Programming | |||
Adding a certain text label in a excel chart | Charts and Charting in Excel |