Talking To The Outside World In Excel 2003

05.29.03

By John O'Donnell

Being able to code C# with Excel 2003 is great fun but now its time for something a bit more useful.

This next code sample shows how to connect to the internet inside of Excel. The idea is when the workbook opens the code behind the workbook searches for quotes on NASDAQ and then populates the spreadsheet with the data. You can imagine you could have a spreadsheet full of data which is retrieved from data sources on company servers and internet sources etc. As you can see coding for excel in C# allows you to leverage your C# skills and re-use them in the world of Excel spreadsheets.

Here is a screenshot of the workbook after its loaded and the code has run. As you can see I populate column B from the Internet then the graph updates.

Before you can do any of this you need to get the Office 2003 beta kit from

http://www.microsoft.com/office/preview/default.asp

Then you need the office add-on for Visual Studio.Net 2003
http://www.microsoft.com/downloads/details.aspx?FamilyID=9e0b1b7c-4ab5-40d2-b4d9-5817ab0bc1e5&DisplayLang=en

Here is the code showing how Excel is updated and also how I retrieve quotes from Nasdaq

protected void ThisWorkbook_Open()

{

Excel.Worksheet ws=new Excel.WorksheetClass();

ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet;

string stock;

double stockval;

for(int i=2;i<8;i++)

{

Excel.Range rng=(Excel.Range)ws.Cells[i,1];

stock=rng.Value2.ToString();

stockval=GetQuote(stock);

ws.Cells[i,2]=stockval;

}

protected double GetQuote(string symbol)

{

string a;

string b;

string c="";

string d="";

string</span>query="http://quotes.nasdaq.com/Quote.dll?mode=stock&symbol=";

query=query+symbol+"&&quick.x=0&quick.y=0";

WebRequest wrq = WebRequest.Create(query);

// Return the response.

WebResponse wr= wrq.GetResponse();

StreamReader sr = new StreamReader(wr.GetResponseStream(),Encoding.ASCII);

a=sr.ReadToEnd().ToString();

if(a.IndexOf(symbol)>0)

{

int pos1=a.IndexOf("$&nbsp;");

//int pos2=a.IndexOf("]");

b=a.Substring(pos1,50);

c=b.Substring(7,b.Length-8);

int pos2=c.IndexOf("</td>");

d=c.Substring(0,pos2);

}

wr.Close();

sr.Close();

return(Convert.ToDouble(d));

}

About GetQuote

This piece of code simply returns the quote for a stock directly from NASDAQ. Because NASDAQ do not publish any web services or XML interfaces that I know of, I had to

get the data the long way. That’s right I just open up a URL and then search through the raw html for the data I need. Now of course your thinking what happens when the web page changes, surely this code will not work? Normally you would be right but for many major websites the overall layout does not change and therefore the underlying html source code is pretty constant so you can write code to search through it and be pretty confident it will work. Actually I wrote that NASDAQ code 9 months ago and it worked up until a few weeks ago and then just needed a minor update.

So in this code I use a WebRequest class with a WebResponse class to request a page from a URL. I then create a new stream by using a GetResponseStream method of the WebResponse Class.

Once I have my stream encoded as ASCII I can then load the stream into a string variable using the ReadToEnd method of the StreamReader class. I can now search through my string variable using the IndexOf method. The rest of the code is simply chopping strings around to close in on the data I want to see.

That’s it! Now you know you can read data easily from the outside world directly into your spreadsheets opening up many possibilities. It would have been possible to write similar code in Excel VBA but it would have been significantly more difficult as it would have meant using WIN32 API calls.

Get the code here

About the Author:
John O'Donnell is a former Microsoft UK employee and MCSE who now lives and works in the Chicago region. John published his first article on C# programming on www.c-sharpcorner.com in May of 2001 and has enjoyed learning this technology ever since. Today John works for MAS Consulting (www.mcas.com), a Microsoft partner company where he is employed as a Senior Technology Consultant specializing in Microsoft CRM. John is available for consulting work across the USA.

WebmasterFree Downloads

HTML Guard
If you want to prevent unauthorized copying of parts of your Internet sites you should have a look at HTML Guard. Among other features, this software lets you disable the right mouse button click.

Hide Files & Folders
Hide Files & Folders is a an easy-to-use password-protected security utility you can use to password-protect certain files and folders, or to hide them from viewing and searching.

32bit Email Broadcaster Batch broadcast email messages such as newsletters to groups and organizations.