Pull Web Service Data into Excel

Microsoft news and tips » Pull Web Service Data into Excel

Microsoft news and tips » Pull Web Service Data into Excel — Wed, 16 Apr 2008 21:43:56 GMT

PingBack from http://microsoftnews.askpcdoc.com/?p=2846

MIS (Management Information Systems) Resources

Springboard from mortarboard to onboard — Fri, 02 May 2008 01:27:53 GMT

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

re: Pull Web Service Data into Excel

Maulik — Fri, 09 May 2008 19:01:34 GMT

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)

re: Pull Web Service Data into Excel

noahc — Mon, 12 May 2008 06:45:44 GMT

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.

re: Pull Web Service Data into Excel

Maulik — Tue, 13 May 2008 02:45:51 GMT

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.

re: Pull Web Service Data into Excel

furkan — Thu, 10 Jul 2008 16:08:31 GMT

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

re: Pull Web Service Data into Excel

EJ_User — Wed, 06 Aug 2008 17:21:35 GMT

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?

re: Pull Web Service Data into Excel

noahc — Wed, 06 Aug 2008 20:55:05 GMT

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.

re: Pull Web Service Data into Excel

EJ_User — Thu, 07 Aug 2008 23:27:46 GMT

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

re: Pull Web Service Data into Excel

noahc — Sat, 09 Aug 2008 00:47:31 GMT

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.

re: Pull Web Service Data into Excel

EJ_User — Mon, 11 Aug 2008 16:46:28 GMT

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 Files\EMERA\GPSServiceSetup\GPSPositioningService.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

re: Pull Web Service Data into Excel

noahc — Fri, 29 Aug 2008 03:54:39 GMT

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.