Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»»

Automating Excel from SQL Server Expand / Collapse
Author
Message
Posted Wednesday, August 19, 2009 2:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:58 AM
Points: 24, Visits: 74
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.
Post #773893
Posted Wednesday, August 19, 2009 4:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 123, Visits: 347
"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)
Post #773955
Posted Thursday, August 20, 2009 1:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:18 AM
Points: 204, Visits: 1,333
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

Post #774048
Posted Thursday, August 20, 2009 8:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 123, Visits: 347
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.
Post #774242
Posted Thursday, August 20, 2009 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 123, Visits: 347
http://www.excelforum.com/
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.
Post #774254
Posted Friday, August 21, 2009 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 12:53 PM
Points: 1, Visits: 3
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?
Post #775280
Posted Friday, August 21, 2009 1:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 123, Visits: 347
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!
Post #775355
Posted Friday, August 21, 2009 2:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 PM
Points: 6,594, Visits: 8,877
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #775416
Posted Friday, August 21, 2009 2:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:58 AM
Points: 24, Visits: 74
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?
Post #775423
Posted Friday, August 21, 2009 3:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 PM
Points: 6,594, Visits: 8,877
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #775444
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse