views:

1072

answers:

5

I would like to know the best way to transfer data from a web service to an excel worksheet (office 2007). I am not interested in outputing a report to excel from a web site. In our scenario a user will be using excel (with a number of macros and such) and will request about 11000 rows of data from the server to a worksheet.

Anyone have any experience with this?

thanks in advance.

A: 

You could use SQL SSIS to connect with the webservice task http://msdn.microsoft.com/en-us/library/ms140114.aspx

Then do the typical move to an excel destination.

Sam
Note that: "The HTTP connection manager supports only anonymous authentication and basic authentication. It does not support Windows authentication."
Mitch Wheat
Could that be worked around with a script task?
Sam
A: 

The Google search: "excel get data from sql" (no quotes) appears to retrive some solutions.

This tutorial looks especially promising: http://www.excelguru.ca/node/23

Phil
+1  A: 

A simple solution I've used in the past for getting data from a database to Excel via a web server is to simply expose the data as a table in a webpage. This can be done using any technology you like, e.g. ASP.NET, which can connect to the database, retreive the data and put the data in a GridView control.

You can then set up a 'Web Query' in Excel. This is done by using the menu item Data -> Import External Data -> New Web Query. You can then navigate to your webpage, select the table as your data source, and choose the range to which it should be written.

There are options for auto-refresh, query string parameters (from cells in the worksheet), etc.

You can automate the refreshment of your Web Query as part of a larger process by using VBA, e.g.

Sheet1.QueryTables(1).Refresh
AdamRalph
A: 

I've recently used a system which did something similar, essentially it was just an Excel Workbook with some VB script that would call the web service. It also prompted the user for credentials.

I can't post any code relating to the spreadsheet side of things, but there is plenty of examples and information out there such as this from Microsoft, and other tutorials / examples such as this.

RM
These are very good resources. One of the issues we have is how to move the xml data very quickly to a worksheet (without looping). Any ideas on doing this? thank you
David
+1  A: 

Check into SharePoint and Excel Services - that's exactly what it's designed to do. You can publish data sources in SharePoint for any users to use in Excel, and keep their Excel spreadsheets up to date with the data warehouse.

Architecture overview:

http://technet.microsoft.com/en-us/magazine/2009.02.bookbi.aspx

Implementation guide in SQL Server Magazine (requires subscription):

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=99355&pg=2

Brent Ozar
We need to get the data from Excel being the client interface/application. It is Excel that needs to pull the data from the web service.
David
OK, in that case, the answer is AdamRalph's suggestion about building a macro.
Brent Ozar