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:
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
The server threw an exception."
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...