I have an Access 2007 database I use to create and format Excel reports for a large team. It has been working just fine up to a couple days ago. Then it started giving me a "Access has encountered an error and needs to close" popup. Using some MsgBoxes I was able to determine that the error occurs in a subroutine that opens an existing Excel file, deletes some extraneous columns of code (no headers and sometimes garbage characters live there), saves and closes the file.
If I comment out the lines
Set xlApp = New Excel.Application and Set xlApp = Nothing
then it works OK. Note that I have to comment out the xlApp lines in a couple other, but not all, places it exists in the code. But of course, if I do that, I do not get the tweeks I need.
Any ideas what could be causing this to decide to fail now? I have added the actual code below for one of the subs that is causing the problem. Another opens an existing Excel file and formats specific cells, fills, bold, that kind of thing.
Dim xlApp As Excel.Application
Dim filePath As String
filePath = CurrentProject.path & "\Linked\metrics_report.xls"
On Error Resume Next
Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.Workbooks.Open FileName:=filePath, ReadOnly:=False
Set xlApp = Nothing