Quit the Excel Application and then destroy this object

ExcelObj.Application.Quit
Set ExcelObj = Nothing

Replacing the Set ExcelObj = Excel.Application with Set ExcelObj = New Excel.Application causes the application to bomb. Setting the 2nd occurrence only to New is OK but Excel is stll active until the VB executable is terminated.

I also tried setting 1st occurrence to Set ExcelObj = New Excel.Application & deleting the 1st occurence of ExcelObj.quit & Set ExcelObj = Nothing, along with deleting the 2nd Set ExcelObj = New Excel.Application, ExcelObj.Visible. Excel is stll active until the VB executable is terminated. (Note that Setting ExcelObj = Excel.Application had the same effects.)

Charles is correct, up to a point. That is better syntax, but there may be more to it than that. If that doesn’t resolve the issue, see MS KB article 178510 (http://support.microsoft.com/kb/178510/en-us), entitled “Excel automation fails second time code runs”. You wouldn’t be getting that error, because you’re not successfully closing Excel, but the cause is the same; an unreleased reference to one of the Excel objects. (Run your code, terminate the process in Task Manager, and run the code, again. I bet you get one of the errors listed in the article.) In your case, it looks like it might be the Activesheet or Activecell reference, but I may be overlooking something.

Copy this into a new project and call the method from a command button:

Option Explicit
Option Base 1 ‘ to set matrix origin to (1,1)

Sub Run_test()
Dim ExcelObj As Excel.Application
Dim DataFile As Excel.Workbook
Dim DataSheet As Excel.Worksheet
Dim DataInFileName As String
Dim DataOutFileName As String
Dim DataOutTemplateName As String
Dim DataIn(9, 9) As Single
Dim DataOut(9, 9) As Single
Dim ncol As Integer
Dim nrow As Integer
Dim Current_Date As Variant
Dim Current_Time As Variant
Dim Current_Date_Full As Variant

Current_Date = Date
Current_Date = Replace(Current_Date, “/”, “_”)
Current_Time = Time
Current_Time = Replace(Current_Time, “:”, “-”)
Current_Date_Full = Current_Date & “_” & Current_Time

DataInFileName = “C:DataIn.xls”
DataOutTemplateName = “C:DataOutTemplate.xls”
DataOutFileName = “C:DataOut_” & Current_Date_Full & “.xls”

‘Opening/reading/closing Excel input data file

‘Set ExcelObj = Excel.Application ‘Yours
Set ExcelObj = New Excel.Application ‘Mine

ExcelObj.Visible = False ‘not visible even without this line

Set DataFile = ExcelObj.Workbooks.Open(DataInFileName)
DataFile.Activate
Set DataSheet = DataFile.Worksheets(“Input Data”)
DataSheet.Activate

MsgBox “Excel is open”
‘ With ActiveSheet
‘ Range(“A2″).Select
‘ With ActiveCell
‘ For nrow = 1 To 9
‘ For ncol = 1 To 9
‘ DataIn(nrow, ncol) = .Offset(nrow – 1, ncol – 1).Value
‘ Next ncol
‘ Next nrow
‘ End With ‘for activecell
‘ End With ‘for activesheet

‘Closing Excel Input Data File without saving
Set DataSheet = Nothing
DataFile.Close (False)
Set DataFile = Nothing
ExcelObj.Quit
Set ExcelObj = Nothing

MsgBox “Excel is now closed.”

‘ ‘generating output data
‘ For nrow = 1 To 9
‘ For ncol = 1 To 9
‘ DataOut(nrow, ncol) = 0.001 * DataIn(nrow, ncol)
‘ Next ncol
‘ Next nrow

‘Opening/writing/saving Excel output data file
‘Set ExcelObj = Excel.Application ‘Yours
Set ExcelObj = New Excel.Application ‘Mine

ExcelObj.Visible = False ‘not visible even without this line
Set DataFile = ExcelObj.Workbooks.Open(DataOutTemplateName)
DataFile.Activate
Set DataSheet = DataFile.Worksheets(“Output Data”)
DataSheet.Activate

MsgBox “Excel is open again”

‘ With ActiveSheet
‘ Range(“A2″).Select
‘ With ActiveCell
‘ For nrow = 1 To 9
‘ For ncol = 1 To 9
‘ .Offset(nrow – 1, ncol – 1).Value = DataOut(nrow, ncol)
‘ Next ncol
‘ Next nrow
‘ End With ‘for activecell
‘ End With ‘for activesheet
‘
‘ ‘Saving & Closing Excel Output Data File
‘ DataFile.SaveAs (DataOutFileName)
Set DataSheet = Nothing
DataFile.Close (False)
Set DataFile = Nothing

ExcelObj.Quit
Set ExcelObj = Nothing

MsgBox (“Check Task Manager to see that the Excel.exe process is still active”)

End Sub

Private Sub Command1_Click()
Call Run_test
End Sub

Hi,

I think that adding one more line will solve this problem. After creating object of Excel Appliction, you either create new workbook or open an existing one. After this step add this line…

xlapp.UserControl = True ‘ xlall is the object of ms excel

Thanks & Regards

I can now successfully close Excel form within VB but I have to remove all uses of With..End With, Activesheet, Activecell, & Range().Select. Apparently, using any one of these invokes Excel & cannot be closed from inside VB.
These can be replaced with direct references; this done then all instances of “..Activate” are unnecessary.

Excel is closed within VB after removing the above items & replacing DataIn(nrow, ncol) = .Offset(nrow – 1, ncol – 1).Value with DataIn(nrow, ncol) = DataSheet.Range(“A2″).Offset(nrow – 1, ncol – 1).Value.

Note that if a range name is needed as a reference, then the following lines will work:
Dim DataRange as Range
Dim DataCell As String
DataCell = DataSheet.Range(“DataRange”).Item(1, 1).Address
DataIn(nrow, ncol) = DataSheet.Range(DataCell).Offset(nrow – 1, ncol – 1).Value