March 13, 2009 at 11:10 am
Microsoft Press:
Programming Microsoft Office Business Applications
ISBN-13: 978-0-7356-2536-5 2008
I am reading and really enjoying this book I won at the Denver (Rocky Mountain) Microsoft SQL Server Users Group. B.T.W. Thanks Microsoft for providing this for our free drawing.
It provides some needed examples and observations for coders.
For example: on page 96 it has code examples of -
Performing Calculations Server-Side in Word Using the Excel Calculation Engine
While many of the small clients I support (50 employees or under) don't have some of the applications (Share Point, Visual Studio...) there is a movement to SQL Server and moving to MS Office applications.
For $35.00 retail - code available - I will give it a thumbs up.
It offers multiple options depending on your clients resources, or maybe even a way to justify obtaining the resources.
March 13, 2009 at 11:16 am
Rob,
Thanks for sharing with us. I use not only Excel but Word and MS ACCESS automation too. I will get this book.
Regards,Yelena Varsha
May 8, 2009 at 9:44 am
Hi I am new but I use lot's of Excel sheets to diplay data from our sql database.
I create a view then use an ODBC call to link the sheet.
I set the properties of the query in excel to keep the properties the user has set.
The user then just has to open and right click and update
Or have I missed the point of the orignal question?
May 8, 2009 at 8:19 pm
Linking Excel to SQL (and you can also store a query as part of the process) is one way to return a recordset. It is a popular way to provide raw data to users.
However, it is sometimes necessary to allow the user to select several options that could include: date range, sort order, group-by, multiple group by (i.e. states, then cities, then product), exclusions, and so on.
The interface often polls the database so that a list box will only include for example States available in the recordset for a selected date.
By using T-SQL, ADO, or ODBC, the client can build a dynamic statement against a SQL view or even a stored procedure. In some cases, a pass-through query can be sent to SQL, then executed so that all the processing takes place on SQL Server. For example: Using the union of four transformation views against SQL data should be done at the server and only return the resulting records across the network.
Likewise, SQL Server using T-SQL can create the Excel automation from the server side. You could have SQL create a Excel product for each client based on their role and service area.
Often, these are decision support products used by a limited number of executives or decision makers.
August 19, 2009 at 2:10 pm
Piers, the theory is easier than the implementation.
I create many workbooks for users throughout my company. The common request is that they be refreshed on a regular basis, usually daily. The obvious option is to have the sheets refresh from the database when they are opened. However, some users are in remote offices, are on a different network, or are clients picking up files via FTP. In all of these cases the data connection fails.
So, I am looking for some way to open the workbooks and refresh the data connections.
HOW? You make it sound so easy!
I have been repeatedly advised not to have SQL Server run Office automation. Besides, none of our SQL Servers have Office installed on them, nor will it ever be installed.
I am considering setting something up on my workstation to run jobs overnight. Given that I'm a DBA, T-SQL is my programming language of choice, so Steve's SPs and examples speak to me.
Do you know another way to do this?
August 19, 2009 at 2:15 pm
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.
August 19, 2009 at 4:55 pm
"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.aspx
Take 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)
August 20, 2009 at 1:48 am
Thank you Rob
I 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 separeted
machines. In fact the client can run Excel with VBA etc to fetch data from SQL Server or
transmit data to SQL Server.
/Gosta
August 20, 2009 at 8:21 am
OLE Automation - Absolutely correct.
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.
August 20, 2009 at 8:28 am
Since the last question was about Excel (getting data from SQL Server)
My favorite place to post and find answers (with code and other details) for Excel -
- Getting data from SQL Seerver
- Adding data back into SQL Server
The code is great and the organization is one of the better forums out there.
August 21, 2009 at 11:15 am
Wayne,
This is a very fascinating article. I have always wondered it there was a way to write data from SQL directly into an Excel Spreadsheet.
However, I followed your article's instructions and was not successful in getting the process to work. Can you suggest what I might be doing wrong?
August 21, 2009 at 1:02 pm
RE: Office 2003 Redistributable Primary Interop Assemblies
Starting back on page 7 and before.... Recap from Memory
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!
August 21, 2009 at 2:04 pm
david.c.reynolds (8/21/2009)
Wayne,This is a very fascinating article. I have always wondered it there was a way to write data from SQL directly into an Excel Spreadsheet.
However, I followed your article's instructions and was not successful in getting the process to work. Can you suggest what I might be doing wrong?
Not without more information about what kind of problems / errors you're having.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 21, 2009 at 2:11 pm
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?
August 21, 2009 at 3:00 pm
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". 🙁
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 76 through 90 (of 102 total)
You must be logged in to reply to this topic. Login to reply