How to: Use the SubCode Property to Capture Error Codes
Excel Services generates errors in the SOAP exception based on errors that occur in Excel Services. To make it easier for the developer to catch specific error conditions, an Excel Calculation Services alert has an associated error code. The Excel Web Services then returns the error using properties from the SoapException class.
The following examples show how to capture the error codes using the SubCode property (https://msdn.microsoft.com/en-us/library/system.web.services.protocols.soapexception.subcode.aspx) of the SoapException class.
Note
To be able to use the SubCode property, you must use Microsoft Visual Studio 2005. The SubCode property does not exist in earlier versions of Visual Studio.
For a list of error codes, see Excel Services Error Codes.
To capture error codes when using SOAP
After adding a Web reference to the Excel Web Services, add the following using directive so that you can use the SoapException class without having to qualify it with a full namespace:
using System.Web.Services.Protocols;To capture the Excel Services error codes using the SubCode property, you must use the SOAP12 protocol version. After instantiating the Excel Web Services proxy class, set the SOAP protocol version as follows:
// Instantiate the Web service. ExcelService xlservice = new ExcelService(); // Set the SOAP protocol version. xlservice.SoapVersion = SoapProtocolVersion.Soap12;To catch the error codes using the SubCode property, add a SOAP exception catch block to your code, for example:
catch (SoapException e) { Console.WriteLine("SOAP Exception Message: {0}", e.Message); Console.WriteLine("SOAP Exception Error Code: {0}", e.SubCode.Code.Name); }
To capture error codes when using direct linking
In the direct linking scenario, you won't need to add a Web reference to the Excel Web Services. However, you will need to add a reference to System.Web.Services namespace.
After you add a reference, add the following using directive to your code so that you can use the SoapException class without having to qualify it with a full namespace:
using System.Web.Services.Protocols;Unlike using SOAP over HTTP, in the direct linking scenario, you won't need to set the SOAP protocol version.
To catch the error codes using the SubCode property, add a SOAP exception catch block to your code, for example:
catch (SoapException e) { Console.WriteLine("SOAP Exception Message: {0}", e.Message); Console.WriteLine("SOAP Exception Error Code: {0}", e.SubCode.Code.Name); }
Example
The following program (a console application) uses the SubCode property to capture the error codes. The program takes different actions based on the error code that is caught. You can, for example, intentionally pass in a nonexistent sheet name to trigger a SOAP exception. In this case, the following SOAP exception message is returned:
The sheet that was requested could not be found. Please try a different one.
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Services.Protocols;
using System.Threading;
using SubCodeExample;
namespace SubCodeExample
{
class Program
{
static void Main(string[] args)
{
if (args.Length != 3)
{
Console.WriteLine("This program requires 3 parameters -
workbook, sheet and cell");
}
string workbookUrl = args[0];
string sheetName = args[1];
string cellRange = args[2];
const string DataRefreshError =
"ExternalDataRefreshFailed";
const string InvalidSheetNameError = "InvalidSheetName";
const string FileOpenNotFound = "FileOpenNotFound";
string sessionId = null;
MyXlServices.ExcelService service = null;
try
{
MyXlServices.Status[] status;
service = new
SubCodeExample.MyXlServices.ExcelService();
service.SoapVersion = SoapProtocolVersion.Soap12;
service.Credentials =
System.Net.CredentialCache.DefaultCredentials;
sessionId = service.OpenWorkbook(workbookUrl, "", "",
out status);
object result = service.GetCellA1(sessionId, sheetName,
cellRange, true, out status);
Console.WriteLine("GetCell result was:{0}", result);
int retries = 3;
while (retries > 0)
{
try
{
service.Refresh(sessionId, "");
}
catch (SoapException soapException)
{
bool rethrow = true;
if (soapException.SubCode.Code.Name ==
DataRefreshError)
{
if (retries > 1)
{
Console.WriteLine("Error when
refreshing. Retrying...");
Thread.Sleep(5000);
rethrow = false;
}
}
if (rethrow) throw;
}
retries--;
}
}
catch (SoapException exception)
{
string subCode = exception.SubCode.Code.Name;
if (subCode == FileOpenNotFound)
{
Console.WriteLine("The workbook could not be found.
Change the first argument to be
a valid file name.");
}
else if (subCode == DataRefreshError)
{
Console.WriteLine("Could not refresh
the workbook.");
}
else if (subCode == InvalidSheetNameError)
{
Console.WriteLine("The sheet that was requested
could not be found. Please try
a different one.");
}
else
{
Console.WriteLine("Unknown error code returned from
Excel Services:{0}", subCode);
}
}
catch (Exception)
{
Console.WriteLine("Unknown exception was raised.");
}
finally
{
if (service != null &&
!String.IsNullOrEmpty(sessionId))
{
try
{
service.CloseWorkbook(sessionId);
}
catch
{
}
}
}
}
}
}
Robust Programming
Make sure that you add a Web reference to an Excel Web Services site that you have access to. Change the using SubCodeExample; statement to point to the Web service site that you are referencing.
In addition, make changes to the workbook path, sheet name, and so on, as appropriate.
See Also
Tasks
Walkthrough: Developing a Custom Application Using Excel Web Services
How to: Catch Exceptions
How to: Trust a Location
How to: Trust Workbook Locations Using Script
How to: Save from Excel Client to the Server
Concepts
Accessing the SOAP API
Excel Services Alerts
Excel Services Known Issues and Tips
Loop-Back SOAP Calls and Direct Linking
Other Resources
How to: Capture Status Return Values