Excel File Locked After Export

Question

I am having some issues with the below piece of code. The data exports fine from access to excel, however, when I go to view the excel file it says its locked for editing. Does anyone know why?

Public Sub ExportFiles()

Dim X As Object
Dim Y As Object
Dim XL As Object
Dim rs As Recordset

Set X = CreateObject("Excel.Application")
Set Y = X.Workbooks.Open("\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Production Pack Template\OFFICIAL SENSITIVE Tasking Team Production Pack.xlsx")
Set XL = Y.Sheets("Tasking Records")
Set rs = CurrentDb.OpenRecordset("ALL")

XL.Range("A2").CopyFromRecordset rs

Y.SaveAs "\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Production Pack Output\" & "Tasking " & "Week " & Format(FiscalWeek, "w") & Format(Date, "yyyymmdd") & ".xlsx"

X.Visible = False

Set X = Nothing
Set Y = Nothing
Set XL = Nothing
rs.Close
Set rs = Nothing

End Sub

score 0 · Accepted Answer · edited May 23 '17 at 11:50

You're not closing the excel application properly. You need to close the workbook and quit excel before setting the objects to nothing.

For more information see my answer to Remove Excel Task from Task Manager after running vba.

Public Sub ExportFiles()

Dim X As Object
Dim Y As Object
Dim XL As Object
Dim rs As Recordset

    Set X = CreateObject("Excel.Application")
    Set Y = X.Workbooks.Open("\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Production Pack Template\OFFICIAL SENSITIVE Tasking Team Production Pack.xlsx")
    Set XL = Y.Sheets("Tasking Records")
    Set rs = CurrentDb.OpenRecordset("ALL")

    XL.Range("A2").CopyFromRecordset rs

    Y.SaveAs "\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Production Pack Output\" & "Tasking " & "Week " & Format(FiscalWeek, "w") & Format(Date, "yyyymmdd") & ".xlsx"

    X.Visible = False

    ' remove objects in reverse order to creation
    Set XL = Nothing
    ' close workbook before quitting excel
    Y.Close
    Set Y = Nothing
    'close excel before setting it to nothing
    X.Quit
    Set X = Nothing


    rs.Close
Set rs = Nothing

Thanks for this. If I wanted to add in a 2nd workbook in another file path, would it be best to repeat the code or is there another way? — A Hughes, May 08 '14 at 07:02
@AHughes You would want to create a subroutine that takes the filepath of the workbook you want to copy and the destination range as a parameter. Take a look at this tutorial over @ [CPearson.com](http://www.cpearson.com/Excel/byrefbyval.aspx). If you have trouble refactoring your code, post another question and we'll help you out. — RubberDuck, May 08 '14 at 12:35

Excel File Locked After Export

1 Answers1