Pull Web Service Data into Excel
Visual Studio 2008 has great Office 2007 integration with the ability to essentially open an Excel worksheet from within VS and add C# code as if it was a Windows Forms application. Just today an acquaintance asked how to pull data off the web and bring it into Excel. There are many different techniques, but here is one simple method in which you create a Web Service, then call the web service from within Excel using C# and put the data in a sheet (in under 5 minutes).
Sample Code: PullWebServiceDataIntoExcel.zip
Note: This is my first attempt at using screencast.com, which is quick easy way to post Camtasia videos online. The direct link to the video is at https://www.screencast.com/t/dLt6QXTFA
Comments
Anonymous
April 16, 2008
PingBack from http://microsoftnews.askpcdoc.com/?p=2846Anonymous
May 01, 2008
I recently visited an MIS department at Baylor University. Since my background is in engineering andAnonymous
May 09, 2008
Hi Noah - Nice demonstration. I downloaded the sample and was not able to run it. I get the following error. I do have Office 2007, Visual Studio 2008 with VSTO installed. File or assembly name Microsoft.VisualStudio.Tools.Office.Runtime.v9.0, or one of its dependencies, was not found. ************** Exception Text ************** System.IO.FileNotFoundException: File or assembly name Microsoft.VisualStudio.Tools.Office.Runtime.v9.0, or one of its dependencies, was not found. File name: "Microsoft.VisualStudio.Tools.Office.Runtime.v9.0" at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Boolean isStringized, Evidence assemblySecurity, Boolean throwOnFileNotFound, Assembly locationHint, StackCrawlMark& stackMark) at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Boolean stringized, Evidence assemblySecurity, StackCrawlMark& stackMark) at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark) at System.AppDomain.Load(String assemblyString)Anonymous
May 11, 2008
Maulik, check the project's references, it may be that they're named differently on your machine. If any appear to be broken, remove them, then add them back from a location that is on your machine. Let me know how that goes.Anonymous
May 12, 2008
Thanks, but I have checked the references and made sure they are correct. In fact I also tried to copy the assemblies to the local directory without any luck. I also tried giving full trust rights to the project directory and sub folders etc. I've also read about this issue on a lot of forums with no specific answers. Any help would be appreciated.Anonymous
July 10, 2008
Dear Noah , Thx for your demo,(an newbie question coming thorugh : ) ) I just want to ask a question besides CSV format. But if we have an array which includes floating point variable like: "3,44" and lets say using this nx1 dimentional array we just want to plot a graph. In your case variables separated by commas but in excell don't we need to put ; to separate them? and how shoud I embed this in my windows forms application? thx for your help..Anonymous
August 06, 2008
Thanks for the information! I have an Excel spreadsheet based application with alot of VBA code. I haven't been able to open it in VS2008 to add a Windows Service. Can I add a Windows Service to an exisiting Excel Spreadsheet through the Office VBA editor?Anonymous
August 06, 2008
Hey EJ_User, I think you also need to be using Office 2007. But here's another method, instead of opening the Excel spreadsheet in VS2008, you can create a separate Web Service project in VS, add a reference to the Excel object model (Project menu, Add Reference..., COM tab, "Microsoft Excel x Object Library"), then open the Excel file through the object model and feed the web service. This will work in VS03, VS05, VS08, and with Office 2000, 2003, or 2008.Anonymous
August 07, 2008
Dear NoahC, I appreciate your suggestion; I am attempting to implement it. Pardon my lack of .NET experience. Below is an explanation of the difficulty I am having. My windows service is a GPS log file. I want Excel to acquire a coordinate from the windows service directly. I've found lots of info online about adding a web service (like your example) to a VS proj but nothing on adding a windows service. The service path I'm using ending with the .exe file isn't agreeable with the service VS08 is looking for. Any suggestions would be greatly appreciated. thx. for your time - ej_userAnonymous
August 08, 2008
For a Windows Service, create a "Windows Service" project in VS2008. "File / New / Project ... / Visual C# / Windows / Windows Service" then add a reference to the Excel object model as mentioned above and you can use the Windows Service to add data to an Excel worksheet.Anonymous
August 11, 2008
Sorry, I wasn't clear with my previous question. I've made it as far as your last response suggests. I just don't know what to use for the service address. In your example you used as the address for your web service: http://localhost:5736/Website2/Service.asmx The path to my windows service executable is: C:Program FilesEMERAGPSServiceSetupGPSPositioningService.exe When I use this as the address for my service reference, I get a error whereby the path was not recognized as a known doc. type. Am I supposed to reference the windows service .exe file as the address to add a (windows) service reference? Again, thanks for your help and patience! - ej_userAnonymous
August 28, 2008
EJ_User, what you've got there is a WINDOWS service, that's very different than a WEB service. I'd recommend learning some of the differences between.Anonymous
January 13, 2010
Great! Simple and straight to the point. When you say "There are many different techniques" what would be a simple one without using the Visual Studio? It used to be much simpler using Office 2003 and the Web Services Toolkit. And almost didn't require programming knowledge. Thanks.Anonymous
November 21, 2011
Dear NoahC, currently the download link not is working, could you please confirme me please? Thanks.