Hey All,

I quite literally spent days trying to work out why when I exported data to excel I could not no matter what, remove the instance of excel form the task managerI tried every know method in every possible combination I could find on Google and ideas on these forums but it would not die

I didnt want to use the known kill all excel instances loop as although an easy fix isnt very user friendly as if the user has an instance of excel open, that will be close too and they probably wont like that too muchso I managed to come up with this solution which I hope or believe will finally solve the problem but has one perk to make it different

The code boasts that you can export data to exceldispose of the excel instance in the task manager BUT THEN .opens the file for the user to view with the difference being, when the user now closes the file, the instance of excel is removed from the tack manager unlike if when closing an instance of excel created though automation which will stay in the task manager

Code Out line:

Apply border all the way around a range of cells
Hide grid lines
Export data to excle / open or create excel
Name Sheets
Close excel and remove from task manager/system memory 100% garanteed
Open excel for user to view the export but ensure that when user close this instance of excel it is removed from the system task manager/system memory

Made for vb.net 2005 :

'You may need to import the following name spaces

'Imports microsoft.Office.Interop.Excel
'Imports Microsoft.VisualBasic
'Imports System
'Imports System.Collections
'Imports System.Collections.Generic
'Imports System.Configuration
'Imports System.Data
'Imports System.Data.SqlClient
'Imports System.IO
'Imports System.Runtime.InteropServices.Marshal

Public Sub CreatExcel()
Try
Dim priorSum As Integer = 0
Dim newSum As Integer = 0
Dim xlProcID As Integer = 0
For Each proc As Process In Process.GetProcessesByName("excel")
priorSum += proc.Id
Next proc
Dim excelApp As Object = Nothing
Dim excelBook As Object = Nothing
Dim excelWorksheet As Object = Nothing
excelApp = New Application
excelBook = excelApp.Workbooks.Add
excelWorksheet = CType(excelBook.Worksheets(1), Worksheet)
excelApp.Visible = False
For Each proc As Process In Process.GetProcessesByName("excel")
newSum += proc.Id
Next proc
xlProcID = newSum - priorSum
With excelWorksheet
' Add/export some data to excel
' Handy hint
' This code will apply border ALL the way around a group of cells 'x,x' i.e. 'A1:A5'
.Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
.Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThin
.Range(x, x).Borders(XlBordersIndex.xlEdgeLeft).ColorIndex = XlColorIndex.xlColorIndexAutomatic
.Range(x, x).Borders(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
.Range(x, x).Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin
.Range(x, x).Borders(XlBordersIndex.xlEdgeRight).ColorIndex = XlColorIndex.xlColorIndexAutomatic
.Range(x, x).Borders(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
.Range(x, x).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin
.Range(x, x).Borders(XlBordersIndex.xlEdgeTop).ColorIndex = XlColorIndex.xlColorIndexAutomatic
.Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
.Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin
.Range(x, x).Borders(XlBordersIndex.xlEdgeBottom).ColorIndex = XlColorIndex.xlColorIndexAutomatic
.Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
.Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).Weight = XlBorderWeight.xlThin
.Range(x, x).Borders(XlBordersIndex.xlInsideHorizontal).ColorIndex = XlColorIndex.xlColorIndexAutomatic

End With

' Handy hint you can remove grid lines and name your sheets like so
With excelApp
.ActiveWindow.DisplayGridlines = False
.ActiveSheet.name = "My Export"
End With

' Edit as required:
excelApp.ActiveWorkbook.SaveAs("My File Location Path\My File Name.xls")

' You must save the file here are some ideas for saving the file so as not to
' have any issues with existing files that you previously exported that may have
' the same file name as your new exported file which of cause would throw errors

'Method 1 Unqueek File name when saving :=
'You dont have to use this but this is just my way of ensuring file names is never
'the same as an old version export
'###############
Dim PublishDate As Date
PublishDate = Now()
Dim FormattedDateAsString As String
FormattedDateAsString = Format(PublishDate, "F") & " " & Format(PublishDate, "(h:mtt)")
Dim FixString As String = FormattedDateAsString
Dim GetDate As Date = Format(PublishDate, "d")
Dim TestString As String = GetDate.Day.ToString
If TestString.EndsWith("1") And TestString <> "11" Then
FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "st ")
ElseIf TestString.EndsWith("3") And TestString <> "13" Then
FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "rd ") 'FixString.Replace(TestString, (TestString & "rd"))
Else
FormattedDateAsString = FixString.Insert(FixString.IndexOf(TestString) + 2, "th ")
End If
TestString = GetDate.Year.ToString
FormattedDateAsString = FormattedDateAsString.Replace(TestString, (TestString & " at"))
FormattedDateAsString = FormattedDateAsString.Replace(":", ".")
' Save the file
excelApp.ActiveWorkbook.SaveAs("C:\My Report Created on - " & FormattedDateAsString & ".xls")
'###############

'Method 2 remove old file :=
'You could also delete and replace old file if you rather
'#############################
If File.Exists("C:\My Report.xls") Then
File.Delete("C:\My Report.xls")
End If
excelApp.ActiveWorkbook.SaveAs("C:\My Report.xls")
'#############################

' You dont have to have this but I do to be sure everything is saved
For Each w In excelApp.Application.Workbooks
'save all the work sheets
w.Save()
Next w
' close the work sheets with out prompting...may not need this either
excelApp.ActiveWorkbook.Close(SaveChanges:=False)

'Now kill excel but only the excel instance we created and NOT any other excel applications running at the same time
If Not excelApp Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet)
excelWorksheet = Nothing
If Not excelBook Is Nothing Then
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelBook)
End If
excelApp.Quit()
excelBook = Nothing
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp)
excelApp = Nothing
Dim proc As Process = Process.GetProcessById(xlProcID)
proc.Kill()
End If

' Now if you want you can open the excel file view. The advantage of this code is that
' when the user closes the excel it wont get stuck in the system task manager
Dim p As New System.Diagnostics.Process

' Set this to the file location and file name of ythe excel you just exported
' Example: "My File Location Path\My File Name.xls" or "C:\My Report.xls" or "C:\My Report Created on - " & FormattedDateAsString & ".xls"
' Edit as required:
p.StartInfo.FileName = "C:\My Report Created on - " & FormattedDateAsString & ".xls"
p.StartInfo.WindowStyle = ProcessWindowStyle.Minimized
p.Start()

'Wait until the process passes back an exit code add if you want but your application
'wont run untill the user closes the excel down
'p.WaitForExit()

' Free the recources ...may not need this line as will work with out but added to be save
' Remove if you add the line above 'WaitForExit()'
p = Nothing

'Free resources associated with this process
' add this if you add the line above 'WaitForExit()'
' p.Close()

Catch ex As Exception
MsgBox(ex.Message & vbTab & ex.Source & vbTab & ex.HelpLink)
End Try
End Sub

Enjoy :thumb: