Using MS Access VBA to insert a row into a .xlsx file... how to avoid corrupting the file?

Question

In MS Access, I am writing a small piece of code to insert a row into a .xlsx file. Both files are local. The code runs with no errors, but, each time it runs, it renders the .xlsx file unreadable. Here's a step-through:

(1) I have the simple .xlsx file (a few hundred rows, a dozen columns, a bit of formatting, no special features or embedded VBA).

(2) I can open and view the file in Excel - but I make sure I close it before running the code.

(3) Run the code below, in MS Access.

(4) The code executes with no errors. The debug.print performs as expected and prints out the contents of cell(1,1).

(5) I re-open the .xlsx file in MS Excel, but Excel now can't open it. Excel itself opens, but, remains completely blank with no open file. No errors.

(6) If I use Office365 to open the file (via web front end), then, it says the following error:

"We can't open this workbook. It's set to show only certain named items, but they aren't in the workbook."

I can restore the file easily enough, but the same behaviour occurs each time (5 times, I've tested - each with slightly different .insert methods). Is there something in my code which is causing this?

Public Function WriteHistoryToExcelFile()
    Dim lExcelObj As Excel.Application
    Dim lExcelWB As Excel.Workbook, lSheet As Excel.Worksheet

    Set lExcelObj = CreateObject("Excel.Application")
    Set lExcelWB = GetObject("C:\Users\XXX\OneDrive\AA-Store\Ziggy\Meta History.xlsx")
    Set lSheet = lExcelWB.Sheets(1)

    Debug.Print lSheet.Cells(1, 1)    'This works correctly

    lSheet.Rows(1).Insert

    lExcelWB.Save
    lExcelWB.Close
    Set lExcelWB = Nothing
    Set lExcelObj = Nothing
End Function

Can someone reproduce this awkward behaviour?

Answer 1

Instead of using GetObject to open the workbook, try using the Application.Workbooks.Open method. And explicitly Quit Excel too:

Public Function WriteHistoryToExcelFile()

    Dim lExcelObj As Excel.Application
    Dim lExcelWB As Excel.Workbook, lSheet As Excel.Worksheet

    Set lExcelObj = CreateObject("Excel.Application")
    Set lExcelWB = lExcelObj.Workbooks.Open("C:\Users\XXX\OneDrive\AA-Store\Ziggy\Meta History.xlsx")
    Set lSheet = lExcelWB.Sheets(1)

    Debug.Print lSheet.Cells(1, 1)    'This works correctly

    lSheet.Rows(1).Insert

    lExcelWB.Save
    lExcelWB.Close
    lExcelObj.Quit
    Set lExcelWB = Nothing
    Set lExcelObj = Nothing
End Function

You might also want to make sure you don't have any lingering, hidden copies of Excel running, in Task Manager.