Using MS Access VBA to insert string into .xlsx file ... how to avoid file corruption?
In MS Access, I am writing a small piece of code to insert a line into a .xlsx file. Both files are local. The code runs without error, but every time it runs it makes the .xlsx file unreadable. Here's a step by step:
(1) I have a simple .xlsx file (a few hundred rows, a dozen columns, a bit of formatting, no special functions or inline VBA).
(2) I can open and view the file in Excel, but I will make sure to close it before running the code.
(3) Run the code below in MS Access.
(4) The code runs without error. Debug.print does as expected and prints out the contents of cell (1,1).
(5) I reopen the .xlsx file in MS Excel, but Excel now cannot open it. Excel itself opens but remains completely blank with no file open. No mistakes.
(6) If I am using Office365 to open a file (via the web interface) then it says the following error:
"We cannot open this workbook, it should only show certain named items, but they are not in the workbook."
I can restore the file quite easily, but the same behavior happens every time (5 times, I tested - each with slightly different .insert methods). Is there something in my code that 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 anyone reproduce this awkward behavior?
source to share
Instead of using GetObject
to open a workbook, try using the method Application.Workbooks.Open
. And obviously 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 can also make sure that there are no hidden hidden copies of Excel in Task Manager.
source to share