How Do I Continue After A VBA Error?

Question

0.00/5 (No votes)

See more:

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:

C#

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.

Posted 13-Jul-15 3:51am

scottl2k7

Add a Solution

Comments

RedDk 13-Jul-15 14:53pm

So basically the spreadsheet it "opening" and some macro or automation code, like events stuck on an object like a form, is running? Presumably ISharePointFolder? Have you tried catching the hang by a simple "Exit Sub"?

scottl2k7 13-Jul-15 16:24pm

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.

RedDk 13-Jul-15 20:16pm

Whoever told you a VBA method was being executed is the man to whom you need to talk.

But let's back up a step. You're running Task Scheduler. Isn't there a "Run" option somewhere among the tabs that will shutdown the process after a given amount of time? Best bet is to fiddle with what you can control. Could you write a batch or a script and do some nestling there in Task Scheduler.

And then again there's always the other option of turning to some Code Project Article. See here perhaps:

http://www.codeproject.com/search.aspx?q=TAsk+Scheduler&sbo=kw&x=12&y=8

Run your hack item in one of these rehashes of Scheduler. Often time added functionailty is built into them.

scottl2k7 13-Jul-15 23:20pm

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.

RedDk 14-Jul-15 0:50am

... artifacts? Things like stubbs and .txt files? Sounds as if you need to rethink the whole spreadsheet idea. Let me ask you this, do you have Excel (2003-2007 or better preferably) on the box on which Scheduler is running?

scottl2k7 14-Jul-15 6:51am

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.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2015-07-13T06:55:00

Solution 1

Unfortunately, VBA does not have exception handling found in modern technologies for decades; but VBA is rather retardant thing than old. You can only use OnError statements:
http://www.cpearson.com/excel/errorhandling.htm[^],
https://support.microsoft.com/en-us/kb/141571[^].

—SA

Posted 13-Jul-15 6:55am

Sergey Alexandrovich Kryukov

Comments

scottl2k7 13-Jul-15 13:39pm

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.

Sergey Alexandrovich Kryukov 13-Jul-15 13:42pm

Then, I guess, nothing is an option. :-)
With .NET, of course, you can and should use structured exception handling. If you can use it, the rest is trivial. If you are not well familiar exception handling, you need to learn it in all detail. Without learning it to perfection, doing any programming makes no sense.
Any problem with that?
—SA

scottl2k7 13-Jul-15 13:55pm

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.

Sergey Alexandrovich Kryukov 13-Jul-15 14:12pm

But does C# code calls what cases exception? Catch all exceptions in C#, and you will see.
Do you understand that exceptions propagate up the stack?
—SA

scottl2k7 13-Jul-15 14:24pm

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.

Sergey Alexandrovich Kryukov 13-Jul-15 16:40pm

Of course, it could be "frozen" (blocked from propagation). Then there is nothing you can do without modifying the code. If you really know the mechanism of exceptions, you should understand, that the system where the exception is thrown does not have to have the mechanism for handling it. But it it is handled, the possibility to catch exception up the stack is lost. I would really avoid using VBA... doing Office programming... not sure if you can afford it... :-)
—SA

scottl2k7 16-Jul-15 10:10am

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.

Sergey Alexandrovich Kryukov 16-Jul-15 10:27am

You are very welcome.
Good luck, call again.
—SA