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?

ThunderFrame · Accepted Answer · 2017-05-25 20:22:50Z

2

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.

answered May 25, 2017 at 20:22

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vexen Crabtree Over a year ago

YES! Thank you, the code modification worked. ... and I was already checking Task Manager (it stood to reason maybe a lingering running process had a handle to the file, but it didn't seem to be that).

ThunderFrame Over a year ago

FWIW - the bug is because GetObject opens the workbook's window as hidden, as per the archived article KB111247. As per the article, the window can be unhidden, but IMO, the Workbooks.Open method is a better approach, given you already have a reference to an Excel.Application

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related