Search code examples
excelvbams-access

Excel remains in the background


using access VBA I open an excel file, I perform a series of operations (as per the following code) I close everything but excel remains running. In fact, if I go and repeat the same procedure I get an error: the file is read only. I have to open "Process Explorer" and manually kill the process. Where am I wrong? Thank you

`Public Function agg_file_codifiche(Cognome, Nome, CF, password As String) As Long
Dim oExcel As Excel.Application
Dim oExcelWrkBk As Excel.Workbook
Dim oExcelWrSht As Excel.Worksheet
Dim xlsfile, xlssheet As String
Dim ur, code As Long

    Set oExcel = CreateObject("Excel.Application")
    
    xlsfile = "C:\Users\*****\Documenti\_LAVORO_\CODIFICHE_UNIPAM\Codifiche unipam.xlsx"
    xlssheet = "Codifiche"
        
    Set oExcelWrkBk = oExcel.Workbooks.Open(xlsfile, , False)
    Set oExcelWrSht = oExcelWrkBk.Sheets(xlssheet)
    oExcel.Visible = False

    With oExcelWrSht
        ur = .Cells(Rows.Count, 3).End(xlUp).Row
    
        badge_code = .Cells(ur + 1, 1).Value
        code = .Cells(ur + 1, 2).Value
    
        .Cells(ur + 1, 3).Value = Cognome & " " & Nome
        .Cells(ur + 1, 4).Value = password
        .Cells(ur + 1, 5).Value = CF
        .Cells(ur + 1, 8).Value = Format(Date, "Short Date")
    End With
    
    oExcelWrkBk.Save
    oExcelWrkBk.Close
    oExcel.Quit

Exit_Point:
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
    agg_file_codifiche = code
    Exit Function
    
Error_Handler:
    MsgBox Err & " - " & Err.Description
    GoTo Exit_Point
End Function`

Solution

  • You missed an object reference. It should read:

    ur = .Cells(.Rows.Count, 3).End(xlUp).Row
    

    and not:

    ur = .Cells(Rows.Count, 3).End(xlUp).Row