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...