VB6 cannot shutdown Excel.exe except by ending VB program

0 pts.
Tags:
Visual Basic
I am running VB6 with Excel 2000. VB6 opens an Excel file & then closes the file, quits excel - but Excel.exe is still active in Task Manager until the VB6 program is terminated. I need to be able to shutdown Excel completely while still inside the VB6 executable. Here is the code I am using: 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 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 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 '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 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 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
ASKED: January 20, 2006  5:51 PM
UPDATED: January 25, 2006  9:34 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Replace both instances of “Set ExcelObj = Excel.Application” with “Set ExcelObj = New Excel.Application” and your app will work just fine.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bappib
    Quit the Excel Application and then destroy this object ExcelObj.Application.Quit Set ExcelObj = Nothing
    0 pointsBadges:
    report
  • Lopezw
    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.)
    0 pointsBadges:
    report
  • DaveInAZ
    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.
    0 pointsBadges:
    report
  • CharlesJC
    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
    0 pointsBadges:
    report
  • SKMEHAB
    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
    0 pointsBadges:
    report
  • Lopezw
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following