I have a program that's a bit of a hack, but it's an internal tool, and it works 99% of the time.
It runs via Windows Scheduler, and it:
1) Downloads a spreadsheet from our intranet site
2) Executes a VBA method in the spreadsheet to update the data using our internal SharePoint site
3) It scrapes some data from the resulting spreadsheet
My troubles occur when the SharePoint isn't reachable. I'd like to just "clean up" and exit the application in that case - the problem will eventually resolve itself, and a few failures aren't a worry for us.
The applicable code is:
Microsoft.Office.Interop.Excel.Application appExcel
= new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = appExcel.Workbooks.Open(EXCEL_FILE);
Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets["MyWorksheet"];
appExcel.Application.Visible = false;
appExcel.Application.DisplayAlerts = false;
appExcel.Run("Refresh");
The problem occurs on the last line. VBA execution halts with the dialog:
Run-time error '1004':
A connection to the SharePoint site cannot be established. To synchronize or refresh your table, you must be able to connect to the SharePoint site.
When this happens, my application remains "hung" until I'm able to close it. I would like to just close the spreadsheet, close Excel, and log the error.
Any suggestions would be greatly appreciated.