the excel program will not closing from vba code in access

Question

I need to empty an excel file that is linked to access database.

Because you can't delete data from within access a tried to write some VBA code that opens the excel file, delete the rows and then save and close excel.

Everything until closing excel was working, the workbook also closed but the excel program stayed open.

Now i changed so much that i have now even problems with saving the excel.

I completely lost it now :-(

Any help would be kind

Dim xlApp As Object
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim LR As Long

Set xlApp = CreateObject("Excel.Application")

xlApp.Application.ScreenUpdating = False
xlApp.Visible = True


Set wb = xlApp.Workbooks.Open("\\networklocation\bestellijst.xlsx", True, False)
Set ws = wb.Worksheets("Bestelling 1")

LR = Range("A:A").SpecialCells(xlCellTypeLastCell).Row

' MsgBox LR

wb.Sheets(1).Range("A2:A" & LR).EntireRow.Delete

xlApp.Application.ScreenUpdating = True

wb.Save
wb.Close

x1App.Quit
Set x1App = Nothing

I search on different website for different solutions but nothing worked

I just want to open the excel file, delete some rows, save the file and close excel completely

Last thing i did today is put the code for closing like this:

Excel.Application.Quit

But then there is a background excel instance still active and the next time a run the vba i get an fault on the "range" code.

If i manually end process of the excel everything is fine again, i go crazy

Answer 1

Try:

Sub test()

    Dim xlApp As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim LastRow As Long
    
    Set xlApp = CreateObject("Excel.Application")
    
    With xlApp
        .Application.ScreenUpdating = True
        .Visible = True
    End With
    
    Set wb = xlApp.Workbooks.Open("D:\XXXX\XXXX\XXXX\XXXX.xlsx")
    Set ws = wb.Worksheets("Sheet1")
    
    With ws
        'Get last row of column A
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'Delete lines
        .Rows("2:" & LastRow).EntireRow.Delete
    End With
    
    'Save & close workbook
    wb.Close SaveChanges:=True
    
    'Close application
    xlApp.Quit

End Sub