Anyone using a web service as a data source in Excel 2007?

Question

Can I use a web service as a data source for creating Excel pivot tables?

Currently, the soure data for the pivot table is being exported from our SQL db to a CSV file. Then, the CSV file is loaded into a worksheet. From there, a pivot table is created in the same workbook. Customers login to a website, click some links, and an excel file (with data and pivot table) is generated. This is a public app so the preference is to not connect directly to the DB.

We control the database and generate the output. We are looking to streamline this process. The SQL db and pivot tables can not / will not change.

Answer 1

See http://www.vertex42.com/News/excel-web-query.html

What format does the "public-facing website" use in making the data available? A data file, a table on a web page? This issue will determine how much of a scraping operation you'll need to do.

You'll still need to write the web service and have it run on a server. A possible alternative is to use Yahoo Pipes to do the conversions for you.