Could try to add
oBook.Close
before
NAR(oSheet)
NAR(oBook)
I suppect that before the workbook is closed,
System.Runtime.InteropServices.Marshal.ReleaseComObject() will not release
it.
Something in your Excel automation code is not correct. You must release
all
objects in Excel object model and quit the Excel application in your code.
This is very common topic related to Excel automation. Without seeing your
code, that all I can say.
- Show quoted text -
Thank you for your response.
Let me first say the only reason I found the objects out there was
because I had to warm boot my computer. Since I seldom reboot my
computer I detected them when I did and suspect they may have been
left over from my testing. That being said, I have included the
function I use. I am also looking for a more eligant way to assign my
column headers then the vebose list at the top of the function.
Private Sub CreateExcelReport()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim ssamplefolder = Me.pOutputPath
Dim strRange As String
Dim rowWork As DataRow
Dim i As Integer
Dim intRow As Integer
Dim intcol As Integer
Dim colWork As DataColumn
Dim htAlphabet As New Hashtable
Dim dtmSaveNow As DateTime = DateTime.Now
Dim dtpDatePatt As String = moConst.BIDateFormatting
Try
htAlphabet.Add(1, "A")
htAlphabet.Add(2, "B")
htAlphabet.Add(3, "C")
htAlphabet.Add(4, "D")
htAlphabet.Add(5, "E")
htAlphabet.Add(6, "F")
htAlphabet.Add(7, "G")
htAlphabet.Add(8, "H")
htAlphabet.Add(9, "I")
htAlphabet.Add(10, "J")
htAlphabet.Add(11, "K")
htAlphabet.Add(12, "L")
htAlphabet.Add(13, "M")
htAlphabet.Add(14, "N")
htAlphabet.Add(15, "O")
htAlphabet.Add(16, "P")
htAlphabet.Add(17, "Q")
htAlphabet.Add(18, "R")
htAlphabet.Add(19, "S")
htAlphabet.Add(20, "T")
htAlphabet.Add(21, "U")
htAlphabet.Add(22, "V")
htAlphabet.Add(23, "W")
htAlphabet.Add(24, "X")
htAlphabet.Add(25, "Y")
htAlphabet.Add(26, "Z")
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
Using reader As New DataTableReader(Me.pReport)
For i = 0 To reader.FieldCount - 1
strRange = htAlphabet(i + 1) & 1.ToString
oSheet.Range(strRange).Value = reader.GetName(i)
oSheet.Range(strRange).Font.Bold = True
'MsgBox(reader.GetName(i) & " " &
reader.GetDataTypeName(i))
Next
End Using
i = 0
intRow = 2
For Each rowWork In Me.pReport.Rows
intRow += 1
intcol = 0
For Each colWork In Me.pReport.Columns
intcol += 1
strRange = htAlphabet(intcol) + intRow.ToString
oSheet.Range(strRange).Value = rowWork(colWork)
Next
Next
'Save the Workbook and quit Excel.
oBook.SaveAs(Me.pOutputPath & Me.pOutputName & "_" &
dtmSaveNow.ToString(dtpDatePatt) & ".xls")
NAR(oSheet)
NAR(oBook)
oExcel.Quit()
NAR(oExcel)
GC.Collect()
Catch ex As Exception
End Try
End Sub
Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub
