Hi,
Sample on how I open and release excel
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
' Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = True
' Get a new workbook.
oWB = oXL.Workbooks.Add
oSheet = oWB.ActiveSheet
' Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name"
oSheet.Cells(1, 2).Value = "Last Name"
oSheet.Cells(1, 3).Value = "Full Name"
oSheet.Cells(1, 4).Value = "Salary"
' Format A1:D1 as bold, vertical alignment = center.
With oSheet.Range("A1", "D1")
..Font.Bold = True
..VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
' Create an array to set multiple values at once.
Dim saNames(5, 2) As String
saNames(0, 0) = "John"
saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
' Fill A2:B6 with an array of values (First and Last Names).
oSheet.Range("A2", "B6").Value = saNames
' Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.Range("C2", "C6")
oRng.Formula = "=A2 & "" "" & B2"
' Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.Range("D2", "D6")
oRng.Formula = "=RAND()*100000"
oRng.NumberFormat = "$0.00"
' AutoFit columns A:D.
oRng = oSheet.Range("A1", "D1")
oRng.EntireColumn.AutoFit()
' Manipulate a variable number of columns for Quarterly Sales Data.
' Make sure Excel is visible and give the user control
' of Excel's lifetime.
oXL.Visible = True
oXL.UserControl = True
' Make sure that you release object references.
oRng = Nothing
oXL.DisplayAlerts = False
oSheet.SaveAs("C:\Test.xls",
FileFormat:=Excel.XlFileFormat.xlXMLSpreadsheet)
oSheet = Nothing
oWB = Nothing
oXL.Quit()
Marshal.ReleaseComObject(oXL)
oXL = Nothing
GC.Collect()
Ken
----------------------
"Rich Wallace" <ri**********@minusthecannedmeat.jfsheadotcom> wrote in
message news:u8**************@TK2MSFTNGP11.phx.gbl...
Thanks Ken,
Same result.
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Hi,
Try passing o byref instead of byval.
Ken
---------------
"Rich Wallace" <ri**********@minusthecannedmeat.jfsheadotcom> wrote in
message news:uu**************@TK2MSFTNGP09.phx.gbl... Hi all,
I have a VB app that runs and manages individual XLS files within a single COM object. Upon processing the final fie, I attempt to close out the
EXCEL object and release it using
System.Runtime.InteropServices.Marshal.ReleaseComO bject.
I have a Try...Catch in my routine as seen below and when I receive
the error, my Catch is never called so my app sees the release as successful, but when I look in Task Manager, the EXCEL.EXE instance is still
running and a Windows Application error pops up...see below code snippet
Private Sub ObjectRelease(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch
MsgBox(Err.Description)
Finally
o = Nothing
End Try
End Sub
Error:
Microsoft Excel: EXCEL.EXE - Application Error
The instruction as "0x77ab193d" referenced memory at "0x0207da34".
The memory could not be "read".
Memory should not be an issue on the server I am running the app on.
BTW, I do clean up any/all references to any Sheets, Workbooks/Workbook
objects prior to calling the ablove code:
'Release the Excel objects
oBook.Save()
ObjectRelease(oSheet)
oBook.Close(False)
ObjectRelease(oBook)
ObjectRelease(oBooks)
oExcel.Quit()
Any ideas??
TIA
-Rich
