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 12345»»»

Automating Excel from SQL Server Expand / Collapse
Author
Message
Posted Thursday, December 4, 2008 10:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:04 PM
Points: 6,582, Visits: 8,861
Comments posted to this topic are about the item Automating Excel from SQL Server

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 #614268
Posted Thursday, December 4, 2008 10:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 31, 2013 8:18 PM
Points: 17, Visits: 18
A good script, I was looking for some time.
Post #614270
Posted Friday, December 5, 2008 12:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,308, Visits: 1,378
Nice article...:)


Post #614283
Posted Friday, December 5, 2008 1:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 10, 2014 9:57 AM
Points: 6, Visits: 56
Yeah, but in reality:

- You'd at very least set the Excel worksheet up with data ranges, then all you have to do programatically is ask the worksheet to refresh itself from the database. Easier to maintain.

- You'd think very seriously about using Excel Services (or a 3rd party product) rather than kill your SQL Server (and your sanity) with COM calls. Not to mention the concurrency impact.

Just because we can, doesn't mean we should...
Post #614299
Posted Friday, December 5, 2008 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:09 AM
Points: 4, Visits: 56
Hi,

Maybe it's a marvelous idea, but......
Why do we need this ?

An Excel-user can create a Microsoft Query in Excel (OLEDB) and update the spreadsheet he created with all the formatting he wants.
The DBA creates a view for this purpose and give permission to the user who needs it.
Users can than select a subset of the data (if needed) by using parameters in the query.

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

My experience is that when you build something specific for a user (formatting he wants, data he wants now), you will get a lot of requests for enhancements and little changements, which will keep you busy.

Regards,
Frank van de Ven


Post #614314
Posted Friday, December 5, 2008 2:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 3:18 AM
Points: 419, Visits: 559
Automating Excel is a very useful way to solve the kind of problem you describe, but I too would be wary of doing it from within SQL Server. For example if something goes wrong, how can you be sure never to leave an instance of Excel running in the background?

I have found it useful to automate Excel from scripts - in my case mainly VBS. You will find that you can use much the same Excel code as you are doing already, and you could access the database using ADODB connection, recordset and command objects and many more.

If you are into Powershell then you could use the .NET objects instead to access the database and benefit from much better error handling as well.
Post #614320
Posted Friday, December 5, 2008 2:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:06 AM
Points: 337, Visits: 1,981
I'd have to agree with Piers,

This looks like it could be useful under certain circumstances and for that reason the article is worthwhile.

In most cases though, I would not want to ask the database to perform those operations. SSIS, VBA or a .NET application would be more appropriate.
Post #614323
Posted Friday, December 5, 2008 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2008 2:34 AM
Points: 1, Visits: 6
Good article - very clear. It is good to know what is possible, even if there are drawbacks.

Please note that there is a comma missing, from before 'SaveAs'. Also, it would be worth mentioning that @value contains the value you want to save.
Post #614325
Posted Friday, December 5, 2008 4:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 25, 2013 2:09 AM
Points: 11, Visits: 62
Will this work on 64 bit Sql Server?

Paul



Post #614368
Posted Friday, December 5, 2008 4:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:15 PM
Points: 23, Visits: 148
Nice article, even if there are drawbacks to this approach it's info worth knowing.

We've been looking at a bit of SQL automation of excel. (not similar to the problem provided but some ideas could be adapted).

Would anybody know if this approach would work on a 64 bit SQL server? (no 64 bit implementation of jet). I've got a stand alone app running on our SQL server that talks to ACE and JET databases (using WOW64) but couldn't get my CLR proc to do the same as it's running under the control of 64 bit SQL.

Any thoughts would be much appreciated.

Thanks
Paul

-- EDIT: Just noticed that post above was written at the same time! :)
Post #614369
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse