Access VBA Excel Workbooks.Open() method stopped working

July 17, 2019 - Reading time: 5 minutes

tldr; Access VBA script to edit Excel only works when Excel application object Visible property is set to True.

I have this VBA attached to an MS Access form.  The worksheet is previously created in Access VBA, and resides on a shared drive (although I have moved it to local drive to see if that affects it...it doesn't). Before sending the spreadsheet to a remote server, however, because the recipient expects there to be no header row, we first remove the first line of the sheet using the following script:

Public Sub DeleteFirstLine(strReportName As String)
'Deletes first line of an Excel file.  Relies on reference to Microsoft Excel 15 Object Library

    On Error GoTo DeleteFirstLineError
    Dim wb As Excel.Workbook
    Dim xlApp As Excel.Application

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False 'Hide it in the background
    If FileExists(strReportName) Then

        Set wb = xlApp.Workbooks.Open(strReportName, True, False) 
' above line currently causing error.
        wb.Sheets(1).Rows(1).Delete
        wb.Save
        wb.Close
        xlApp.Quit

    Else
        MsgBox ("File not found when looking for " & strReportName)
    End If

    GoTo DeleteFirstLineExit
    DeleteFirstLineError:
    xlApp.Quit
    MsgBox ("Error deleting first line of Excel file. Permissions issue?" & vbCrLf & "Error: " & Err.Number & " - " & Err.Description)

    DeleteFirstLineExit:
End Sub

Ideally this creates an Excel application object, sets its visibility to false, and opens the worksheet. The first row is then deleted, the worksheet is saved, and the worksheet is closed.  Then the app is quit.

This was working until this week, when suddenly, when it get to the .Open() line it fails either by saying

Error: -2147417851
"Automation error
The server threw an exception."

or

Error: -2137417851
Method 'Open' of object 'Workbooks' failed.

Neither one of these is particularly helpful. In the past, I've run into similar issues when the spreadsheet is corrupted, as in the export in the previous step isn't completed or there is something permissions-based, but neither appears to be happening at this time.  I am able to manually open the Excel spreadsheet and see it looks fine and no error is reported. 

Now, to top it off, as an experiment, I changed the line xlApp.Visible = False to xlApp.Visible = True, and suddenly it works. This makes no sense to me. 

As you know, always a dabbler...