logging though mail earlier this year, I came across an interesting message about Microsoft and eBay working to make it easier to integrate eBay into Office applications. This piqued my interest, because anytime companies of this size start to work together, I take notice.

As developers, we all understand the importance of Microsoft, but eBay is a little bit different. It can be hard to understand why your company would need to interact with eBay until you look at the numbers. In case you haven’t read the business pages lately, eBay has blown out its numbers again this quarter (Q1 2004). Digging a little deeper, you find that eBay really is, as they slogan says, “the world’s marketplace.” Some interesting eBay factoids include:

• One-third of people that have ever gone on the Internet have visited at sometime eBay.
• 25 percent of the German postal services traffic in 2002 was eBay related shipments.
• $23.8 billion(USD) in transactions are conducted on eBay in 2003. For Q1 of 2004 the number has risen to $8.0 billion for Q1 alone. This is about $1,000/sec.
• At anytime there are 21 million items on eBay, with three million items added each day.

The eBay API and SDK
While I don’t sell many Beanie Babies, I have worked with people selling hundreds?if not thousands?of items on eBay. Now, these people could use the eBay Web interface to enter all their items, but it’s a timely process and lacks integration with other systems the seller may use (inventory, order fulfillment, etc.). In business, time is money, so to facilitate application integration eBay provides two primary interfaces, the eBay API and the SDK.

The API is an XML-based interface. You communicate with eBay by creating a series of XML strings and passing them to the eBay processing DLL. This is the original interface and is still a primary means of application integration.

An alternative means of interfacing with eBay is the SDK. The SDK is a .NET library that wraps around and extends the API. The SDK handles all of the XML issues and operates in a manner more familiar to many object-oriented programmers, by using objects with methods and properties.

Microsoft Word and Excel 2003 and Code Behind
Microsoft and eBay Integration is based on using Word and Excel 2003’s support for code in .NET assemblies to call a .NET library such as the eBay SDK. This capability is different than the VBA environment available directly from Tools menu in the various office products. Though the VBA environment is still available, you may want to consider the Visual Studio .NET environment.

There are several reasons to use Visual Studio.NET to create the code that operates your document or spreadsheet:

• Performance: You can use the more structured languages like VB.NET and C#. Having these strongly typed environments helps eliminate a whole class of syntax errors.
• Familiarity: If you code in Visual Studio.NET most of the time, then you’ll be in familiar territory and more productive. An interactive debugging environment, context based help, and other facilities are available.
• Portability: You can easily incorporate functionality from existing code into your document or spreadsheet. If you already have a routine that does something you need, you can just reference the assembly.
• Security: Your code is deployed in a managed DLL that does not have a source format, and you can control access to the DLL using .NET’s security mechanism.

Speaking of security, this is one area that can frustrate some developers new to .NET and Office Code Behind. .NET provides security mechanisms that go far beyond simple file permission. These mechanisms allow the developer or security administrator to specify which .NET assemblies may be accessed by applications. Because of this, you may receive messages stating that your application was unable to acquire execute access to assemblies such as the eBay SDK. If so, you need to define access for the appropriate assemblies in Microsoft .NET configuration tool (found under Start|Control Panel|Administrative Tools). Microsoft has several tutorials on this tool on their Web site.

To facilitate developing in Visual Studio.NET, Microsoft has developed an add-on to the Visual Studio environment called “Visual Studio Tools for the Microsoft Office System.” This add-on modifies your Visual Studio environment to contain several new projects. These projects contain options for creating Word and Excel applications in either VB.NET or C#.

When you generate a Word project, Visual Studio will create three files:

• AssemblyInfo contains all of the basic settings for the assembly. You will probably not need to change these settings.
• A Word document. This document is given the name of your project. You can modify it by adding text or controls.
• ThisDocument.cs (or .vb if you used VB.NET) contains the event handlers associated with the Word document. The generated version of the file contains handlers for the document’s open and close events. You can add additional events as necessary. ThisDocument.cs is where your code will likely reside.

The eBay Search Document
Many of the examples for using the API and SDK focus on the sale of items on eBay (see “Integrate Your Inventory System with the eBay SDK“). While many people sell things on eBay, buyers are stronger in number and they can also benefit from the using the SDK. To this end, this article’s sample app is a search application uses Office and the eBay SDK.

This sample app is fairly simple. The user enters some search criteria and gets back a listing of the items that matched the criteria. In order to be able to include this listing in multiple documents, the app is written in Word 2003.

Application Architecture
To the user, the Word application appears as a simple document with a table for search criteria, a search button, and a search results table, as shown in Table 1 below. The user enters the search criteria, presses the Search button, and the matching items appear in the search results table. The items in the search results table are hyperlinked. Pressing the hyperlink displays the eBay listing for the item.

Table 1. A User’s View: The application appears as a simple document with a table for search criteria, a search button, and a search results table.

Connecting to eBay
Every call made via the SDK is independent. As such, the each call is validated to ensure it is coming from an “authorized” source. To become authorized, you must sign up with the eBay Developer Program and receive developer, application, and certification Ids. This can be done at no cost on the eBay developers Web site. Once you are enrolled in the program, you will be sent the appropriate ids and you can then create and validate test userIDs and password.

You must also pass along user information that is used to ensure that the caller has a valid eBay account. Currently, this can be done by also passing a user ID and password. As you can imagine, some users have balked at entering their eBay user ID and password. As a result, eBay has developed a new mechanism that allows the user to log into eBay via the standard eBay login mechanism and your application is handed a token representing this correct login. Your calls to the API use this token instead of the user ID and password. This mechanism is known as “Authorization and Authentication.”

Filling in this authorization information for each call is at best tedious, and worse, error-prone. To make it easier, the SDK has an APISession object that can be filled out once and reused to each call. The sample application uses the following code to initialize the APISession object:

//	Perform the one-time initialization needed to use the eBay SDK	public static void InitializeeBay()	{		DateTime  dtNow = DateTime.Now,		dtExpire = dtNow.AddDays(1);			//	Create and initialize the API Session		apiSession = new ApiSession();					//		Set up application key data		apiSession.Url = "https://api.sandbox.ebay.com/ws/api.dll";		apiSession.Developer = "your developer ID";		apiSession.Application = "you Application ID";		apiSession.Certificate = "your certificate";		apiSession.RequestUserId = "your user ID";		apiSession.RequestPassword = "your password";                 }

The populated APISession object must be passed with each call.

Specifying Search Criteria
The sample application lets users specify the search criteria through a simple table. The table in the sample utilizes only a few of the possible search criteria. The code to create the table is shown below:

//	Create the parameter table	Word.Range rng = ThisDocument.Range(ref location, ref location); 	CriteriaTable = ThisDocument.Tables.Add(rng, 5, 2, ref                      missingValue, ref missingValue);		CriteriaTable.Columns[1].SetWidth(ThisApplication.InchesToPoints((float)1.25),Word.WdRulerStyle.wdAdjustNone );		CriteriaTable.Columns[2].SetWidth(ThisApplication.InchesToPoints((float)1.25), Word.WdRulerStyle.wdAdjustNone );				CriteriaTable.Cell(1,2).Merge(CriteriaTable.Cell(1,1));	CriteriaTable.Cell(1,1).Range.Text = "Search Criteria";	CriteriaTable.Cell(1,1).Range.Font.Bold = 1;	CriteriaTable.Cell(1,1).Range.Font.Size= 14.0F;	CriteriaTable.Cell(2,1).Range.Text = "Name";	CriteriaTable.Cell(2,2).Range.Text = "Value";	CriteriaTable.Cell(2,1).Range.Font = HeaderFnt;	CriteriaTable.Cell(2,2).Range.Font = HeaderFnt;	//	Criterium	CriteriaTable.Cell(3,1).Range.Text = "Description";	CriteriaTable.Cell(3,2).Range.Text = "Car";	CriteriaTable.Cell(4,1).Range.Text = "Min Price";	CriteriaTable.Cell(4,2).Range.Text = "";	CriteriaTable.Cell(5,1).Range.Text = "Max Price";	CriteriaTable.Cell(5,2).Range.Text = "";	//	Border	CriteriaTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle;	CriteriaTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble;

Search for Matching Listings
The search for matching listings is invoked when the user presses the search button. The code connecting pressing this button to a routine is shown below:

//	associate events with the Search Button	object control;	control = this.FindControl("cmdSearch");	this.cmdSearch = control as MSForms.CommandButton;	// Attaches the events 	if (this.cmdSearch != null)	{	//	When the button, btn_search, is pressed the handler cmdSearch_Click() is invoked		this.cmdSearch.Click += new 			MSForms.CommandButtonEvents_ClickEventHandler			(cmdSearch_Click);	}

The actual search of the listings is accomplished by the API object GetSearchResults(). GetSearchResults() has a number of parameters that allow you to specify your search criteria and how results are returned.

The search parameters available include category, lowest price, highest price, region, and query string. The query string is used to search item titles and, optionally, descriptions. The query string contains keywords and relational operators such as OR and AND.

You can tell the object how many results to return by using the MaxResults parameter. If the total number of results matching the criteria, returned in the GrandTotal parameter, is greater than the max results, then you need to call the function again specifying the number of items to bypass with the Skip parameter. This is makes it very easy to implement paging of the results.

The code below shows how to call GetSearchResults():

	// Execute Search GetSearchResultsCall SearchRes = new GetSearchResultsCall( apiSession);	SearchRes.Timeout = 120000;	//		Get Search Criteria	SearchRes.Query = CriteriaTable.Cell(3,2).Range.Text.TrimEnd(tChars);	string sLow = CriteriaTable.Cell(4,2).Range.Text.TrimEnd(tChars);	if ( sLow.Length > 0)		SearchRes.LowestPrice = Convert.ToDecimal( sLow );	string sHigh = CriteriaTable.Cell(5,2).Range.Text.TrimEnd(tChars);	if ( sHigh.Length > 0)		SearchRes.HighestPrice = Convert.ToDecimal( sHigh );	//		Find matching items	IItemFoundCollection Items = SearchRes.GetSearchResults();

The results of the search are returned to the user as a collection of IItemFound objects. These are lightweight objects that have basic information about the item such as whether the object has a photo, has a “Buy It Now” option, or is a new listing. Two more important pieces of information are included, the URL to the item, and the ItemID. The URL to the item can be used to provide a hyperlink to the item. The code below shows how the sample application iterates through the collection of Items returned:

	//	If any matches are found, display results	if ( Items.Count > 0) 	{		sResult = Items.Count.ToString() + " matching items found";		Word.Font ResultFont = new Word.Font();		ResultFont.Underline = Word.WdUnderline.wdUnderlineNone;		ResultFont.Bold = 0;		ResultFont.Size = 10.0F;		int i = 3;		// add results from row 3 down		Object beforeRow = Type.Missing;		foreach ( IItemFound Item in Items)		{						ResultsTable.Rows.Add(ref beforeRow);			ResultsTable.Cell(i,1).Range.Font = ResultFont;			ResultsTable.Cell(i,2).Range.Font = ResultFont;			AddHyperLink( ResultsTable.Cell(i,1).Range, Item.Title.ToString(),Item.ViewItemLink.ToString() );			ResultsTable.Cell(i,2).Range.Text = Item.CurrentPrice.ToString();			i++;		}	}	else		sResult = "no matching items found";	MessageBox.Show( sResult );

The ItemID is used to retrieve detailed information about the item by calling GetItem(). Whether you need to call GetItem depends upon the level of detail about the item your application requires. The sample application does not need additional information about the item so GetItem() is not called.

Easy as eBay
As the sample application shows, it is simple to incorporate eBay functionality into a Word or Excel 2003 document. The Code Behind capabilities of these Office products make it easy to integrate eBay facilities it any .NET application. And, when combined with the Microsoft Visual Studio Tools for the Microsoft Office System, Visual Studio becomes a great facility for creating, developing, and debugging Office applications.