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 http://www.screencast.com/t/dLt6QXTFA

Comments (14)

  1. I recently visited an MIS department at Baylor University. Since my background is in engineering and

  2. Maulik says:

    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)

  3. noahc says:

    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.

  4. Maulik says:

    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.

  5. furkan says:

    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..

  6. EJ_User says:

    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?

  7. noahc says:

    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.

  8. EJ_User says:

    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_user

  9. noahc says:

    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.

  10. EJ_User says:

    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:


    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_user

  11. noahc says:

    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.

  12. Marcos says:

    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.


  13. Frank says:

    Dear NoahC, currently the download link not is working, could you please confirme me please?