Users are able to choose for automated refreshing of the data when opening their excel-spreadsheet or by command.

Frank, some of my users are clients, for whom auto-refresh won't work, as they are outside the corporate network. I need to refresh it for them, preferably on a scheduled basis. Other people don't want auto-refresh enabled, as they want the data to remain static and only be refreshed on, say, a weekly basis.

"I have been repeatedly advised not to have SQL Server run Office automation"We all hear this, probably because Office needs security patches from time to time. I would never suggest letting users have access to an office application running on the server.But, in most cases, you will loose this argument to the server group.

This is probably more than you ever wanted to know...

Here is what I have in one support environment.1. Access MDE with a secure MDA application. I use 3rd party tools to provide modern tree views and an Outlook style look and feel. This resides on each users PC.2. Access uses linked Views - the vast majority of links are to SQL Views built with T-SQL. Access VBA also calls on Stored Procedures. I also generate Pass Throgh Queries and store the T-SQL in Access code using case statements and appends to alter the request.2. With Excel VBA and Excel Object Model, dynamically create each Excel Workbook. Start with Excel, add a worksheet, and all the gory details. My Workbooks may have a daily download of Wall Street prices for a commodity per location, basis, transport and other current data. The associated worksheets have actual formulas in the cells. Brokers want to conduct what-if situations on futures and look at the associated spreads for example.This is not something that people who just copyfromrecordset need to do. The Excel workboods can be dynamically created from the options a user checks or enters on the user interface form.3. A launcher on the users desktop checks for the last date of the Access MDE application. When I update the application, each user's PC checks for latest version and downloads it. Users are in the local office and in many remote sites.

The interface on step 1 uses SQL Server to maintain each users security, views, and favorites.

Basically, I have a development version. Compile it. Post it on the server and all users automatically get the latest version.

Since 99% of the processing takes place on SQL Server, even a large complex 16 worksheet (full of formulas and current data) will take between 1 to 25 seconds to build from scratch. And, most of these offer the user a dozen custom choices such as: Customer name, from-to dates, overview / details, graphs, ...

Just to give you an idea, the application code and forms (no data) is about 130 MB with about 150 user form interfaces and 200 very active users. Once in a while, simple reports use Access Reports. Excel has the advantage of having the business rules and formulas included so everyone can agree on where the numbers came from.Depending on the complexity and size of the data, make decisions to just copy and filter a SQL Veiw, Stored Procedure, or Pass Through Query using T-SQL.

I add about two or more major features per week to update the application version that gives my traders the edge. This is a one development station and MS office on each users PC solution. The office manager clicks a button once in the morning, and it creates stand-alone Excel reports that are archived on a shared network. These can be retrieved via FTP or e-mailed. It would be easy to automat that on a timer.

This solution is not the solution for everyone. I also use .NET and Reporting Services when they are the right tool.

http://www.accessui.com/Home/tabid/36/Default.aspxTake a look at this web site for example (if just for the free tools).Access can work extremely efficiently with SQL Server, let SQL provide all the horsepower, and only send the results over the network.

All this said:I would run a job on Excel (Excel VBA Object Model Code) on a server next to SQL server and have it create the Excel workbooks in a shared folder.We post a lot of SQL on our Excel users forum along with the Excel Object Model VBA code. (and VB and .Net... its all good)

Thank you RobI will just add one aspect."I have been repeatedly advised not to have SQL Server run Office automation"You don't need to do that.With Remote OLE DB you can have SQL Server and Office automation on separetedmachines. In fact the client can run Excel with VBA etc to fetch data from SQL Server ortransmit data to SQL Server.

SQL Server does provide another option to do what OLE Automation using (almose) Pure T-SQL. On a previous thread, we discussed how it would be a wonderful world if it was supported. However, it turns out that for this option to work, a copy of Excel must be installed... on the SQL Server. And this is what "everyone" was warning against.

Thanks for bringing that up. So many options... so little posting time.

This is one web site that I was attempting to get this working myself. Sorry if I can remember it all.Basically, I heard (rumored so let's keep it at that level) that Microsoft Accounting used this as the preference.I can understand why myself why they would want to create rich Excel documents with current data AND formulas and other embedded features rather than just scraping an HTML or XML data onto a spreadsheet.

Searching further and listening to other reasonable people, I believe that Microsoft failed to support or document this feature to a point we can really use it.

My interest is now more concerned with the next version of SQL Serve and Windows 7.

Having said that, you reminded me about this, so I will send off an e-mail to a long time friend at (Micro****) and see if they can shed some light. I use to shed cattle and sheep, now I want to shed light?

Since then, I have fallen back to automation methods, one the most efficient being Pass Through T-SQL queries - also very sketchy in its documentation.

If anyone has any news on how to use Interop or something like it, please, please post here!

Rob, thanks for the reply.I started out on this journey with trying to use the Interop assemblies in an SSIS package, only to find out that there were also issues with running this on 64-bit SQL Server 2005, my production environment.

It looks like one of the programmer/analysts here is going to write a utility that will poll certain folders, open workbooks, refresh and save. Not something I wanted to tackle so I'm glad this need has gotten some attention here in IT R&D. Turns out I wasn't the only one asking for this capability and the other person was able to allocate the resources to do it. Much as Microsoft integrates products, there are still some holes that we have to plug with some effort.

My only consolation is that there seem to be so many posts about this issue both here and on other forums that they have to do something about it, right?

cmcc (8/21/2009)My only consolation is that there seem to be so many posts about this issue both here and on other forums that they have to do something about it, right?

Dream on... MS doesn't even have 64-bit drivers for Excel yet. The 32-bit driver can't insert into a named range if there is anything under it, even if all the data will fit into the named range. Don't see them to eager to "do something about it".