excelvbavb6basic

VB6 does not terminate Excel process


I'm having an VB6 application that opens an excel macro enabled file using workbooks.open method. Inside this file, there is an userform that gonna be automatically opened when the file is opened. The problem is the workbooks.open method in vb6 application keeps running and does not jump to next line even when I close my userform (I do have codes to close workbook, quit excel app when the userform is closed). I can only close the interface of my userform but Excel still running in background. As long as I dont close the Excel app in task manager, my vb6 does not terminate excel process and of course it will not jump to next line either.

Here is my code in vb6 to open excel file that contains the userform above

As I said, workbooks.open method works but it does not terminate and keeps the excel app runnning in background

P/s: don't mind the Japanese characters

Private Sub Form_Load()
    Dim xlapp
    Dim xlwkb
    
    If Not App.PrevInstance Then
        Set xlapp = CreateObject("excel.application")
        Me.Hide
        On Error Resume Next
        Set xlwkb = xlapp.workbooks.open("C:\Users\david\Desktop\sale-system\fixedfile.xlsm", Readonly:= True)
        
        Set xlwkb = Nothing
        
        If Not xlapp Is Nothing Then
            xlapp.Visible = True
            xlapp.Application.quit
            Set xlapp = Nothing
        End If
        Unload Me
    Else
        MsgBox "プログラムが既に起動されています!", vbInformation, "売上管理システム"
        Unload Me
    End If
    
End Sub

Here is my code in excel file when close the userform

Application.DisplayAlerts = False
ThisWorkbook.Close False
 Application.Visible = True
 Application.Quit
 End

I really need to solve this problem in this week, any solution? Thank you guys in advance and please pardon for my english

Here is some images. I hope they will help you guys to understand my problem

This happens when I use Thisworkbook.close False statement: enter image description here

This happens when I dont use Thisworkbook.close False statement: enter image description here


Solution

  • Inside this file, there is an userform that gonna be automatically opened when the file is opened.

    when I close my userform (I do have codes to close workbook, quit excel app when the userform is closed).

    You are doing this incorrectly. Do not close/quit it from the UserForm. Do it from VB6 form. This way, vb6 will be able to handle and clear the objects.

    Here, try this (I already tried it and it works...). This will not leave an instance of Excel running. I have commented the code. But if you still have questions then simply ask.

    Note: Before you try this (Just for testing purpose), close all Excel application. Even from the task manager.

    Private Sub Form_Load()
        Dim oXLApp As Object
        Dim oXLWb As Object
        Dim ICreateatedExcel As Boolean
        
        '~~> Establish an EXCEL application object
        On Error Resume Next
        Set oXLApp = GetObject(, "Excel.Application")
        
        '~~> If not found then create new instance
        If Err.Number <> 0 Then
            Set oXLApp = CreateObject("Excel.Application")
            '~~> I created instance of Excel
            ICreateatedExcel = True
        End If
        Err.Clear
        On Error GoTo 0
        
        '~~> Check if you have an instance of Excel
        If oXLApp Is Nothing Then
            MsgBox "Unable to get an instance of Excel.", vbCritical, "Excel is Installed?"
            Exit Sub
        End If
        
        Me.Hide
        
        '~~> Show Excel
        oXLApp.Visible = True
        
        '~~> Open file
        Set oXLWb = oXLApp.Workbooks.Open("C:\Tester.xlsm")
    
        '~~> This and other lines below will not run till the
        '~~> time you close the userform in Excel
    
        '~~> Close the workbook
        oXLWb.Close (False) '<~~ Set this to True if you want to save changes
        
        Set oXLWb = Nothing
        
        '~~> If I created Excel then quit
        If ICreateatedExcel = True Then oXLApp.Quit
        
        Set oXLApp = Nothing
        
        Unload Me
    End Sub