Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel.Application Object .Quit leaves EXCEL.EXE running

I'm working in MS Access 2013 on Windows 10.

I'm trying to open an Excel file on disk, change the column formatting and some columns, save and quit.
The Sub runs, but after the .Quit command, "EXCEL.EXE" keeps running and subsequent calls to that Sub will result in run-time errors.
If I close Access, "EXCEL.EXE" disappears on the Task Manager, but not if I do "Compact and Repair" the database.

I've another Sub that opens an Excel file on disk and alters the width of all columns to "auto-width", then closes, which doesn't leave "EXCEL.EXE" running.

I tried different line orders on Dims and Sets to Nothing, Workbook Closes, etc.
I searched here and on other sites. The only suggestions for VBA is to use something like ThisWorkbook.Saved = True. I tried that before and after .Quit to no effect.
Other than that, I only find solutions for VB.NET or other environments.

Sub changeXLcolumnFormatting()

Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Visible = False
XL.DisplayAlerts = False
XL.Workbooks.Open "C:\Users\640344\Desktop\rawDataTest.XLSX"
Dim sht As Worksheet

With XL
    Set sht = ActiveWorkbook.Worksheets(1)

    Dim rng As Range
    Dim i As Integer, j As Integer

    field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
    sht.Select
    end_of_table = sht.UsedRange.Columns.Count

    For j = 0 To UBound(field_names)
        For i = 1 To end_of_table
            Set rng = sht.Cells(1, i)
            If InStr(rng.Text, field_names(j)) > 0 Then
                sht.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
            End If
        Next i
    Next j
End With

Set rng = Nothing
Set sht = Nothing
XL.ActiveWorkbook.Close (True)

XL.Quit
Set XL = Nothing

End Sub
like image 492
Cinetyk Avatar asked Feb 08 '17 12:02

Cinetyk


People also ask

How do I stop Excel exe?

Restart the computer. 2. End Process to Winword.exe and Excel.exe in the task manager 3. Kill the process using taskkill.exe Although 1 is the easiest, it would also be time consuming to wait for the computer to start up again, launch all of the programs etc.

How do I close an instance of Excel VBA?

To close an Excel file, you need to use the “Close” method. With this method you can, specify if you want to save the changes or not. And, if you want to save and close a file that is not saved yet you can specify the path where you want to save it before closing.

Can an Excel file be converted to EXE?

To compile a workbook to an EXE you need to have the Microsoft Windows 7 or higher and Microsoft Office 2007 SP3 or higher with the latest service pack and updates installed on your computer. Our product supports all 32-bit and 64-bit versions of Excel from 2007 and higher, including Excel from Office 365.

How do you exit a macro in Excel VBA?

In VBA, you can stop your macro execution manually with the Esc key or by pressing Ctrl+Break.


1 Answers

Declare and use a specific Workbook object - as you do for Worksheet and Range, like this:

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    Dim rng     As Range
    
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)
    Set rng = wks.Range(<something>)
    
    ' Do stuff.
    wks.Name = "My New Name"
    With rng
         ' Do more.
    End With

    wkb.Close True
    
    Set rng = Nothing
    Set wks = Nothing
    Set wkb = Nothing
    
    xls.Quit
    
    Set xls = Nothing

Also, don't use Select, that's for visible use only. Define ranges instead.


Cinetyk's EDIT:

Using @Gustav 's indications, the code that does what I wanted and solves the problem is:

Sub changeXLcolumnFormatting()

Dim XL As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim rng As Range

Set XL = New Excel.Application
XL.Visible = False
XL.DisplayAlerts = False

Set wkb = XL.Workbooks.Open("C:\Users\640344\Desktop\rawDataTest.XLSX")
Set sht = wkb.Worksheets(1)

Dim i As Integer, j As Integer

field_names = Split("datasistema|Data de Registo|Data Registo CMVM", "|")
end_of_table = sht.UsedRange.Columns.Count

For j = 0 To UBound(field_names)
    For i = 1 To end_of_table
        Set rng = sht.Cells(1, i)
        If InStr(rng.Text, field_names(j)) > 0 Then
            sht.Columns(i).NumberFormat = "yyyy-mm-dd  HH:MM:ss"
        End If
    Next i
Next j

wkb.Close (True)
Set rng = Nothing
Set sht = Nothing

XL.Quit
Set XL = Nothing

End Sub
like image 108
Gustav Avatar answered Oct 05 '22 11:10

Gustav