Excel.Application not closed excel file

Question

I want to read some data from excel file and close it. but my code not closed it:

Function getColumnOfFirstRow(PATH, size) As Long

Dim oApp_Excel As Excel.Application
Dim oBook As Excel.Workbook
Dim column As Long
column = 0

Set oApp_Excel = CreateObject("EXCEL.APPLICATION")
oApp_Excel.DisplayAlerts = False
oApp_Excel.Visible = True
Set oBook = oApp_Excel.Workbooks.Open(PATH, ReadOnly:=True)

On Error GoTo errhand
column = oBook.Sheets("Sheet1").Cells.Find(What:=CStr(size)).column


oBook.Close True
oApp_Excel.Quit


Set oBook = Nothing

errhand:
    Select Case Err.Number
    Case 91
        column = 0
    End Select
getColumnOfFirstRow = column


End Function

I think this part of my code must close it:

oBook.Close True
oApp_Excel.Quit

No No. You are doing this incorrectly. When there is an error, it will directly go to errhand and not quit excel. I would recommend going through 2 links **1.** https://stackoverflow.com/questions/74806190/vb6-does-not-terminate-excel-process See here how I'm creating, and disposing the object. **2.** https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640 Here I have talked about error handling. You can ignore the rest. So you should be quitting and disposing the objects in `Letscontinue:` part of Error handling. — Siddharth Rout, Dec 16 '22 at 10:19
And a 3rd link.. You should always handle the result of `.Find`. See this https://stackoverflow.com/questions/18864988/what-are-the-other-options-we-can-get-from-the-result-of-range-find — Siddharth Rout, Dec 16 '22 at 10:20
Also where is the function `getColumnOfFirstRow`. Is this in Excel? Word?, VB6? Where? — Siddharth Rout, Dec 16 '22 at 10:24

VBasic2008 · Answer 1 · 2022-12-16T12:19:22.717

Using a New Instance of Excel

It looks like overkill to open and close Excel and a workbook to just retrieve a number but let's say we're practicing handling objects and error handling.

Function GetSizeColumn(ByVal Path As String, ByVal Size As Double) As Long

    On Error GoTo ClearError
    
    Dim xlApp As Excel.Application: Set xlApp = New Excel.Application
    xlApp.Visible = True ' out-comment when done testing
    
    Dim wb As Excel.Workbook
    Set wb = xlApp.Workbooks.Open(Path, True, True)
    
    Dim SizeColumn As Long
    SizeColumn = wb.Sheets("Sheet1").Rows(1).Find(CStr(Size)).Column
    ' You can avoid the expected error as you have learned in your newer post. 
    ' In this case, if the error occurs, the function will end up with 
    ' its initial value 0 since its result is declared 'As Long'
    ' i.e. the following line will never be executed.
    
    GetSizeColumn = SizeColumn

ProcExit:
    On Error Resume Next
        If Not wb Is Nothing Then wb.Close False
        If Not xlApp Is Nothing Then xlApp.Quit
    On Error GoTo 0
    Exit Function    
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "':" & vbLf & Err.Description
    Resume ProcExit
End Function

score 0 · Answer 2 · answered Dec 16 '22 at 10:37

Try it. 100% working code about creating the excel. In this code, excel converts recordset in excel successfully. After that close the excel successfully. No error.

Also, check with the Task manager and close any excel file open in the process.

Public Sub ConvertRecordSetToExcelFull(Rs As Recordset, _
                                   FileNameWithPath As String, _
                                   SheetName As String, _
                                   Rangename As String)

On Error GoTo Error1

Dim ExlFile As Object, Book As Object, Sheet As Object, K As Long, J As Long

Set ExlFile = CreateObject("Excel.Application")
Set Book = ExlFile.Workbooks.Add
Set Sheet = Book.Worksheets(1)
ExlFile.DisplayAlerts = False
K = 1

For J = 0 To Rs.Fields.Count - 1
    Sheet.Cells(K, J + 1) = UCase(Rs.Fields(J).Name)
Next
K = K + 1

If Rs.RecordCount >= 1 Then

    'Call RecCount(rs)
    Do While Rs.EOF <> True

        For J = 0 To Rs.Fields.Count - 1
            Sheet.Cells(K, J + 1) = Rs.Fields(J)
        Next
        K = K + 1
        Rs.MoveNext
    Loop

End If

Book.Worksheets(1).Name = SheetName
Book.SaveAs FileNameWithPath
ExlFile.ActiveWorkbook.Close False
ExlFile.Quit
Set Sheet = Nothing
Set ExlFile = Nothing
Screen.MousePointer = vbNormal
Exit Sub
Error1:
MsgBox Err.Description
Err.Clear
End Sub

Excel.Application not closed excel file

2 Answers2

Using a New Instance of Excel