Replace the code that Visual Studio generated with the following code. If you are using the Module1.vb file, replace the Module1 module; if you are using the Program.cs file, replace the Program class definition.
Module Module1
' Declare variables to hold the Web service URL,
' target workbook, worksheet, and named range.
Dim serviceURL As String = String.Empty
Dim targetWorkbookPath As String = String.Empty
Dim sheetName As String = String.Empty
Dim rangeName As String = String.Empty
Sub Main()
' Check command-line arguments.
If Not CheckArgs() Then
Return
End If
' Create an instance of the Web service proxy class
' and set the URL to use.
Dim service As ExcelService = New ExcelService()
service.Url = serviceURL
' Declare a status array.
Dim outStatus() As Status
' Declare a variable to hold the session ID.
Dim sessionId As String = String.Empty
' Set the credentials for the request by specifying
' the network credentials of the current security context.
service.Credentials = _
System.Net.CredentialCache.DefaultCredentials
' An alternative approach is to create a NetworkCredential
' object to connect as a specific user.
'
' service.Credentials = _
' New System.Net.NetworkCredential("User", _
' "Password", "Domain")
Try
' Open the workbook, specifying string.Empty for the
' uiCultureName and dataCultureName parameters. Doing this
' tells the Web service to use the server's default
' settings for UI- and Data-Culture for the site and user.
sessionId = service.OpenWorkbook(targetWorkbookPath, _
String.Empty, String.Empty, outStatus)
' Request the range by name, specifying true for the
' formatted parameter to tell the Web service to return
' empty cells as the Empty string. Specfying false for this
' parameter would tell the Web service to return empty
' cells as Null.
Dim rangeResult() As Object = _
service.GetRangeA1(sessionId, sheetName, rangeName, _
True, outStatus)
' Display the values in the range.
Console.WriteLine("The values in the " + rangeName + _
" range are:" + vbCrLf)
For Each row As Object In rangeResult
For Each cell As Object In CType(row, Object())
Console.Write(cell + " ")
Next cell
Console.WriteLine()
Next row
Catch se As SoapException
Console.WriteLine("SOAP Exception Message: {0}", _
se.Message)
Catch ex As Exception
Console.WriteLine("Exception Message: {0}", ex.Message)
Finally
' Close the workbook. This also closes the session,
' proactively releasing resources on the server.
If Not sessionId = String.Empty Then
service.CloseWorkbook(sessionId)
End If
End Try
Console.Write(vbCrLf + "Press any key to continue...")
Console.ReadLine()
End Sub
#Region "Commandline Argument Processing"
Private Function CheckArgs() As Boolean
Dim bArgsOK As Boolean = True
' Check the number of command-line arguments.
If (My.Application.CommandLineArgs.Count < 4) Then
bArgsOK = False
Else
' Parse the command-line arguments.
For Each arg As String In My.Application.CommandLineArgs
If (arg.ToLower().StartsWith("/service=")) Then
serviceURL = arg.Remove(0, 9)
End If
If (arg.ToLower().StartsWith("/book=")) Then
targetWorkbookPath = arg.Remove(0, 6)
End If
If (arg.ToLower().StartsWith("/sheet=")) Then
sheetName = arg.Remove(0, 7)
End If
If (arg.ToLower().StartsWith("/range=")) Then
rangeName = arg.Remove(0, 7)
End If
Next arg
' Ensure that the URL for the Web service was specified.
If (serviceURL = String.Empty) Then
Console.Error.WriteLine("Please specify the URL " + _
"to the Web service with the /service= switch." + _
vbCrLf)
bArgsOK = False
End If
' Ensure that a workbook was specified.
If (targetWorkbookPath = String.Empty) Then
Console.Error.WriteLine("Please specify a " + _
"workbook with the /book= switch." + vbCrLf)
bArgsOK = False
End If
' Ensure that a worksheet was specified.
If (sheetName = String.Empty) Then
Console.Error.WriteLine("Please specify a " + _
"worksheet with the /sheet= switch." + vbCrLf)
bArgsOK = False
End If
' Ensure that a named range was specified.
If (rangeName = String.Empty) Then
Console.Error.WriteLine("Please specify a named " + _
"range with the /range= switch." + vbCrLf)
bArgsOK = False
End If
End If
If Not bArgsOK Then
ShowUsage()
End If
Return bArgsOK
End Function
Sub ShowUsage()
Console.Error.WriteLine("Usage:" + vbCrLf)
Console.Error.WriteLine("ExcelSvcsApp /service=ServiceURL " + _
"/book=WorkbookPath /sheet=WorksheetName " + _
"/range=RangeName" + vbCrLf)
Console.Error.WriteLine("/service= " + _
"The URL of the Web service,")
Console.Error.WriteLine(" " + _
"e.g. /service=" + _
"http://SiteURL/Subsite/_vti_bin/excelservice.asmx" + _
vbCrLf)
Console.Error.WriteLine("/book= " + _
"The path or URL to the target workbook,")
Console.Error.WriteLine(" " + _
"e.g. /book=http://Server/Documents/Workbook.xlsx" + vbCrLf)
Console.Error.WriteLine("/sheet= " + _
"The name of the worksheet to use in the target " + _
"workbook,")
Console.Error.WriteLine(" " + _
"e.g. /sheet=Sheet1" + vbCrLf)
Console.Error.WriteLine("/range= " + _
"The name of the range in the worksheet to request " + _
"data for,")
Console.Error.WriteLine(" " + _
"e.g. /range=MyRange" + vbCrLf)
Console.Error.Write("Press any key to continue...")
Console.ReadLine()
End Sub
#End Region
End Moduleclass Program
{
// Declare variables to hold the Web service URL,
// target workbook, worksheet, and named range.
static string serviceURL = string.Empty;
static string targetWorkbookPath = string.Empty;
static string sheetName = string.Empty;
static string rangeName = string.Empty;
static void Main(string[] args)
{
// Check command-line arguments.
if (!CheckArgs(args))
return;
// Create an instance of the Web service proxy class
// and set the URL to use.
ExcelService service = new ExcelService();
service.Url = serviceURL;
// Declare a status array.
Status[] outStatus;
// Declare a variable to hold the session ID.
string sessionId = string.Empty;
// Set the credentials for the request by specifying
// the network credentials of the current security context.
service.Credentials =
System.Net.CredentialCache.DefaultCredentials;
// An alternative approach is to create a NetworkCredential
// object to connect as a specific user.
//
// service.Credentials =
// New System.Net.NetworkCredential("User",
// "Password", "Domain");
try
{
// Open the workbook, specifying string.Empty for the
// uiCultureName and dataCultureName parameters. Doing this
// tells the Web service to use the server's default
// settings for UI- and Data-Culture for the site and user.
sessionId = service.OpenWorkbook(targetWorkbookPath,
string.Empty, string.Empty, out outStatus);
// Request the range by name, specifying true for the
// formatted parameter to tell the Web service to return
// empty cells as the Empty string. Specfying false for
// this parameter would tell the Web service to return
// empty cells as Null.
object[] rangeResult = service.GetRangeA1(sessionId,
sheetName, rangeName, true, out outStatus);
// Display the values in the range.
Console.WriteLine("The values in the " + rangeName +
" range are:\r\n");
foreach (object row in rangeResult)
{
foreach (object cell in (object[])row)
Console.Write(cell + " ");
Console.WriteLine();
}
}
catch (SoapException se)
{
Console.WriteLine("SOAP Exception Message: {0}",
se.Message);
}
catch (Exception ex)
{
Console.WriteLine("Exception Message: {0}", ex.Message);
}
finally
{
// Close the workbook. This also closes the session,
// proactively releasing resources on the server.
if (sessionId != String.Empty)
service.CloseWorkbook(sessionId);
}
Console.Write("\r\nPress any key to continue...");
Console.ReadLine();
}
#region Commandline Argument Processing
private static bool CheckArgs(string[] args)
{
bool bArgsOK = true;
// Check the number of command-line arguments.
if (args.Length < 4)
{
bArgsOK = false;
}
else
{
// Parse the command-line arguments.
foreach (string arg in args)
{
if (arg.ToLower().StartsWith("/service="))
serviceURL = arg.Remove(0, 9);
if (arg.ToLower().StartsWith("/book="))
targetWorkbookPath = arg.Remove(0, 6);
if (arg.ToLower().StartsWith("/sheet="))
sheetName = arg.Remove(0, 7);
if (arg.ToLower().StartsWith("/range="))
rangeName = arg.Remove(0, 7);
}
// Ensure that the URL for the Web service was specified.
if (serviceURL == string.Empty)
{
Console.Error.WriteLine("Please specify the URL to " +
"the Web service with the /service= switch.\r\n");
bArgsOK = false;
}
// Ensure that a workbook was specified.
if (targetWorkbookPath == string.Empty)
{
Console.Error.WriteLine("Please specify a workbook " +
"with the /book= switch.\r\n");
bArgsOK = false;
}
// Ensure that a worksheet was specified.
if (sheetName == string.Empty)
{
Console.Error.WriteLine("Please specify a worksheet " +
"with the /sheet= switch.\r\n");
bArgsOK = false;
}
// Ensure that a named range was specified.
if (rangeName == string.Empty)
{
Console.Error.WriteLine("Please specify a named " +
"range with the /range= switch.\r\n");
bArgsOK = false;
}
}
if (!bArgsOK)
ShowUsage();
return bArgsOK;
}
private static void ShowUsage()
{
Console.Error.WriteLine("Usage:\r\n");
Console.Error.WriteLine("ExcelSvcsApp /service=ServiceURL " +
"/book=WorkbookPath /sheet=WorksheetName " +
"/range=RangeName\r\n");
Console.Error.WriteLine("/service= " +
"The URL of the Web service,");
Console.Error.WriteLine(" " +
"e.g. /service=" +
"http://SiteURL/Subsite/_vti_bin/excelservice.asmx\r\n");
Console.Error.WriteLine("/book= " +
"The path or URL to the target workbook,");
Console.Error.WriteLine(" " +
"e.g. /book=http://Server/Documents/Workbook.xlsx\r\n");
Console.Error.WriteLine("/sheet= " +
"The name of the worksheet to use in the target " +
"workbook,");
Console.Error.WriteLine(" " +
"e.g. /sheet=Sheet1\r\n");
Console.Error.WriteLine("/range= " +
"The name of the range in the worksheet to request " +
"data for,");
Console.Error.WriteLine(" " +
"e.g. /range=MyRange\r\n");
Console.Error.Write("Press any key to continue...");
Console.ReadLine();
}
#endregion
}