Comments by scottl2k7

scottl2k7 16-Jul-15 10:10am View

I wasn't able to find a true solution to my problem, but we did end up with a work-around.

We created a local copy of the spreadsheet, and I used "On Error Goto" to allow the application to continue gracefully. I also added a call to save the workbook when updates succeed. We've requested that we be informed when structural changes are made to the spreadsheet so that we can update our copy.

It's not perfect, but it should be a lot better than having the process hang for an entire weekend because the connection to SharePoint hiccupped on Friday night, and it's a lot easier than programmatically watching for and closing error dialogs when they appear.

My thanks to everyone for their suggestions. I'm accepting Sergey's reply as the solution since we did end up using VBA's remedial error handling.

scottl2k7 14-Jul-15 6:51am View

By "artifacts", I mean anything that's left from the prior run - like an open copy of the spreadsheet and the open instance of Excel.

Yes... Excel 2013 is installed. None of this would work at all without it.

Again, this solution works 99% of the time - it's run successfully every five minutes for a week at a time, but whenever the SharePoint site is unavailable for a moment and the Excel macro throws an error, everything grinds to a halt.

I've realized that I should be able to reduce the frequency of the errors by checking SharePoint from C# (before I load Excel), but it seems like there should be better solution that doesn't depend on even this bit of "timing" and luck.

scottl2k7 13-Jul-15 23:20pm View

I'm not sure what you mean about the "VBA method being executed"...

Yes, Task Scheduler can shut down a process (my C# application) if it runs too long, but that will leave Excel running, the workbook open, and the hung macro untouched.

I suppose that when the C# program launches I can look for artifacts from previous runs and try to clean them up... I was really hoping for a more graceful solution.

scottl2k7 13-Jul-15 16:24pm View

I'm hoping that I understand your questions correctly...

Yes. "Refresh" is a method in the Excel spreadsheet that re-scrapes data from our company's SharePoint site through a defined "Workbook Data Connection".

The spreadsheet also lives on our intranet, and I cannot make any changes to it.

scottl2k7 13-Jul-15 14:24pm View

I do understand exception handling in .NET.

The error happens in the VBA code (in Excel), but it doesn't throw an error and move on - it freezes there with a dialog. If you debug the C# code, you'll see that execution freezes at the line "appExcel.Run("Refresh");".

If this were an executable instead of VBA, I could adjust the wait time, and try to react to the fact that the process was taking too long, but I'm at a loss about what to do in this case.

scottl2k7 13-Jul-15 13:55pm View

I certainly understand what you're saying, but this isn't a matter of error handling in C#.

If C# was throwing an exception, I don't think I'd have any problem at all. Instead, C# makes the Interop call, and execution never returns.

scottl2k7 13-Jul-15 13:39pm View

Thanks Sergey,

Unfortunately, even "On Error" is not an option - I can't make changes to the VBA in the Excel file.

On the other hand, if C# would detect that there'd been an error, I'm pretty sure I could recover. API calls to watch for the dialog and respond with "SendKeys" seems like a possibility, but one that makes me shudder.

Comments by scottl2k7 (Top 7 by date)